Can this be done? Outer join with OR where clause

IDS: 11.10.FC3 O/S: RHEL 5 I have a query in a foreach loop that originally was one table and only returned rows based on a datetime (year to fraction 4) field being greater than a value stored in another table. This query worked fine and returned the expected rows. Now I have a need to do an outer join to another related table that also have a datetime field, but only want to return the row if either of the datetime fields is greater than the table value. I can't do a union because it is the same row, but some of the returned fields will change based on the presence of a row in the related table. All of the rows returned because of the datetime criteria in the related table are related to primary table records that don't fall within the time period requested. I only want to pull ~2 weeks' worth of changed records, but the related table entries sometimes don't happen for several months. The issue I think I am having is that the related table will have NULL for the datetime field if a record doesn't exist since it is an outer join. I thought I could use an OR in the where clause, but it is returning every row in the table. I then added an NVL to the OUTER table, but it gives me an " SQL Error (-1264): Extra characters at the end of a datetime or interval." error. Am I doing something wrong or can't this type of query work? Basic query: Select 1,2,3,4,a.moddatetime,b.moddatetime From table1 a, OUTER table2 b Where (a.moddate >= (Select datetime value from anothertable) OR b.moddate >= (Select datetime value from anothertable)) I then modified where to be: Where (a.moddate >= (Select datetime value from anothertable) OR NVL(b.moddate,'1900-01-01 00:00:00.0000' >= (Select datetime value from anothertable)) But neither works as intended. TIA, Randy

Randy: You are doing several things wrong. The main ones that there is no join condition between tables a & b and no filter on "anothertable". Don't you have to find the table2 row(s) that align with the correct table1 row(s)? Don't you want to compare to a particular datetime record in "anothertable"? Hopefully that's a transcription error when you posted the simplified query. I cannot fill in the correct criteria, but hopefully you'll get the gist and correct my version: select res.one, res.two, res.three, res.four, res.a_modtime, res.b_modtime from ( select 1, 2, 3, 4, a.modtime, b.modtime from table1 as a, outer table2 as b where a.somekey = b.somekey ) as res ( one, two, three, four, a_modtime, b_modtime ) where a_modtime >= (select datetime_col from anothertable where criteria_col = <value>) and b.modtime IS NOT NULL and b.modtime >= (select datetime_col from anothertable where criteria_col = <value>) ; If you need to filter from anothertable using values in table1 or table2, then you will have to pass those out of the dynamic table "res" that is managing the outer join between table1 and table2. BTW, if the row in another table IS dependent on values from table1 and/or table2, then you can fold that subqueries containing anothertable into one or two joins (depending on whether the filter criteria needed to test the modtime from table1 is the same or different from that needed to test the modtime from table2. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Dec 26, 2012 at 5:20 PM, Kennedy, Randy <RKennedy@scottsdaleaz.gov>wrote: > IDS: 11.10.FC3 > O/S: RHEL 5 > > I have a query in a foreach loop that originally was one table and only > returned rows based on a datetime (year to fraction 4) field being greater > than a value stored in another table. > This query worked fine and returned the expected rows. > > Now I have a need to do an outer join to another related table that also > have > a datetime field, but only want to return the row if either of the datetime > fields is greater than the table value. I can't do a union because it is > the > same row, but some of the returned fields will change based on the > presence of > a row in the related table. All of the rows returned because of the > datetime > criteria in the related table are related to primary table records that > don't > fall within the time period requested. I only want to pull ~2 weeks' worth > of > changed records, but the related table entries sometimes don't happen for > several months. > > The issue I think I am having is that the related table will have NULL for > the > datetime field if a record doesn't exist since it is an outer join. > > I thought I could use an OR in the where clause, but it is returning every > row > in the table. > > I then added an NVL to the OUTER table, but it gives me an " SQL Error > (-1264): Extra characters at the end of a datetime or interval." error. > Am I doing something wrong or can't this type of query work? > > Basic query: > Select 1,2,3,4,a.moddatetime,b.moddatetime > >From table1 a, OUTER table2 b > Where (a.moddate >= (Select datetime value from anothertable) OR b.moddate> >= > (Select datetime value from anothertable)) > > I then modified where to be: > Where (a.moddate >= (Select datetime value from anothertable) OR > NVL(b.moddate,'1900-01-01 00:00:00.0000' >= (Select datetime value from > anothertable)) > > But neither works as intended. > > TIA, > Randy > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d0447882d4b519604d1c98bde

I had a join condition, just didn't put it in the email. I don't think I am quite following your example so I will post the actual updated query using your guidelines. SELECT ccaa332400053,ccaa332400054,ccaa332400011,ccaa332400012,ccaa33240004,ccaa3324000 2,ccaa33240006,ccaa33240007,DECODE(NVL(ccaa33240017,''),'V','t','f'),amoddatetim e,amoduser,bmoddatetime,bmoduser FROM (SELECT caa332400053,caa332400054,caa332400011,caa332400012,caa33240004,caa33240002,caa3 3240006,caa33240007,DECODE(NVL(caa33240017,''),'V','t','f'),a.moddatetime,a.modu ser,b.moddatetime,b.moduser FROM caa33240 a, OUTER caa33540 b WHERE caa332400011 = caa335400011 AND caa332400012 = caa335400012 AND caa33240002 = caa33540002) AS c (ccaa332400053, ccaa332400054, ccaa332400011, ccaa332400012, ccaa33240004, ccaa33240002, ccaa33240006, ccaa33240007, ccaa33240017, amoddatetime, amoduser, bmoddatetime, bmoduser) WHERE amoddatetime >= (SELECT MAX(lastsyncdate) FROM lastsync) AND b.moddatetime IS NOT NULL AND b.moddatetime >= (SELECT MAX(lastsyncdate) FROM lastsync) I get a syntax error that it can't find the b. fields for the where. Aren't they now outside the select that uses a and b assignments? If I am reading your example correctly, there is no filtering (where), just the join for the outer of the original 2 tables. Shouldn't some of the where go before the ") as c ( field list" part? Thanks, Randy -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Wednesday, December 26, 2012 4:11 PM To: ids@iiug.org Subject: Re: Can this be done? Outer join with OR where.... [29160] Randy: You are doing several things wrong. The main ones that there is no join condition between tables a & b and no filter on "anothertable". Don't you have to find the table2 row(s) that align with the correct table1 row(s)? Don't you want to compare to a particular datetime record in "anothertable"? Hopefully that's a transcription error when you posted the simplified query. I cannot fill in the correct criteria, but hopefully you'll get the gist and correct my version: select res.one, res.two, res.three, res.four, res.a_modtime, res.b_modtime from ( select 1, 2, 3, 4, a.modtime, b.modtime from table1 as a, outer table2 as b where a.somekey = b.somekey ) as res ( one, two, three, four, a_modtime, b_modtime ) where a_modtime >= (select datetime_col from anothertable where criteria_col = <value>) and b.modtime IS NOT NULL and b.modtime >= (select datetime_col from anothertable where criteria_col = <value>) ; If you need to filter from anothertable using values in table1 or table2, then you will have to pass those out of the dynamic table "res" that is managing the outer join between table1 and table2. BTW, if the row in another table IS dependent on values from table1 and/or table2, then you can fold that subqueries containing anothertable into one or two joins (depending on whether the filter criteria needed to test the modtime from table1 is the same or different from that needed to test the modtime from table2. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Dec 26, 2012 at 5:20 PM, Kennedy, Randy <RKennedy@scottsdaleaz.gov>wrote: > IDS: 11.10.FC3 > O/S: RHEL 5 > > I have a query in a foreach loop that originally was one table and > only returned rows based on a datetime (year to fraction 4) field > being greater than a value stored in another table. > This query worked fine and returned the expected rows. > > Now I have a need to do an outer join to another related table that > also have a datetime field, but only want to return the row if either > of the datetime fields is greater than the table value. I can't do a > union because it is the same row, but some of the returned fields will > change based on the presence of a row in the related table. All of the > rows returned because of the datetime criteria in the related table > are related to primary table records that don't fall within the time > period requested. I only want to pull ~2 weeks' worth of changed > records, but the related table entries sometimes don't happen for > several months. > > The issue I think I am having is that the related table will have NULL > for the datetime field if a record doesn't exist since it is an outer > join. > > I thought I could use an OR in the where clause, but it is returning > every row in the table. > > I then added an NVL to the OUTER table, but it gives me an " SQL Error > (-1264): Extra characters at the end of a datetime or interval." error. > Am I doing something wrong or can't this type of query work? > > Basic query: > Select 1,2,3,4,a.moddatetime,b.moddatetime > >From table1 a, OUTER table2 b > Where (a.moddate >= (Select datetime value from anothertable) OR> b.moddate > >= > (Select datetime value from anothertable)) > > I then modified where to be: > Where (a.moddate >= (Select datetime value from anothertable) OR > NVL(b.moddate,'1900-01-01 00:00:00.0000' >= (Select datetime value > from > anothertable)) > > But neither works as intended. > > TIA, > Randy > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d0447882d4b519604d1c98bde ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I tried a few different combinations, but still not able to get the results I am looking for or get syntax errors. What am I missing? Thanks, Randy -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Kennedy, Randy Sent: Wednesday, December 26, 2012 5:19 PM To: ids@iiug.org Subject: RE: Can this be done? Outer join with OR where.... [29161] I had a join condition, just didn't put it in the email. I don't think I am quite following your example so I will post the actual updated query using your guidelines. SELECT ccaa332400053,ccaa332400054,ccaa332400011,ccaa332400012,ccaa33240004,ccaa3324000 2,ccaa33240006,ccaa33240007,DECODE(NVL(ccaa33240017,''),'V','t','f'),amoddatetim e,amoduser,bmoddatetime,bmoduser FROM (SELECT caa332400053,caa332400054,caa332400011,caa332400012,caa33240004,caa33240002,caa3 3240006,caa33240007,DECODE(NVL(caa33240017,''),'V','t','f'),a.moddatetime,a.modu ser,b.moddatetime,b.moduser FROM caa33240 a, OUTER caa33540 b WHERE caa332400011 = caa335400011 AND caa332400012 = caa335400012 AND caa33240002 = caa33540002) AS c (ccaa332400053, ccaa332400054, ccaa332400011, ccaa332400012, ccaa33240004, ccaa33240002, ccaa33240006, ccaa33240007, ccaa33240017, amoddatetime, amoduser, bmoddatetime, bmoduser) WHERE amoddatetime >= (SELECT MAX(lastsyncdate) FROM lastsync) AND b.moddatetime IS NOT NULL AND b.moddatetime >= (SELECT MAX(lastsyncdate) FROM lastsync) I get a syntax error that it can't find the b. fields for the where. Aren't they now outside the select that uses a and b assignments? If I am reading your example correctly, there is no filtering (where), just the join for the outer of the original 2 tables. Shouldn't some of the where go before the ") as c ( field list" part? Thanks, Randy -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Wednesday, December 26, 2012 4:11 PM To: ids@iiug.org Subject: Re: Can this be done? Outer join with OR where.... [29160] Randy: You are doing several things wrong. The main ones that there is no join condition between tables a & b and no filter on "anothertable". Don't you have to find the table2 row(s) that align with the correct table1 row(s)? Don't you want to compare to a particular datetime record in "anothertable"? Hopefully that's a transcription error when you posted the simplified query. I cannot fill in the correct criteria, but hopefully you'll get the gist and correct my version: select res.one, res.two, res.three, res.four, res.a_modtime, res.b_modtime from ( select 1, 2, 3, 4, a.modtime, b.modtime from table1 as a, outer table2 as b where a.somekey = b.somekey ) as res ( one, two, three, four, a_modtime, b_modtime ) where a_modtime >= (select datetime_col from anothertable where criteria_col = <value>) and b.modtime IS NOT NULL and b.modtime >= (select datetime_col from anothertable where criteria_col = <value>) ; If you need to filter from anothertable using values in table1 or table2, then you will have to pass those out of the dynamic table "res" that is managing the outer join between table1 and table2. BTW, if the row in another table IS dependent on values from table1 and/or table2, then you can fold that subqueries containing anothertable into one or two joins (depending on whether the filter criteria needed to test the modtime from table1 is the same or different from that needed to test the modtime from table2. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Dec 26, 2012 at 5:20 PM, Kennedy, Randy <RKennedy@scottsdaleaz.gov>wrote: > IDS: 11.10.FC3 > O/S: RHEL 5 > > I have a query in a foreach loop that originally was one table and > only returned rows based on a datetime (year to fraction 4) field > being greater than a value stored in another table. > This query worked fine and returned the expected rows. > > Now I have a need to do an outer join to another related table that > also have a datetime field, but only want to return the row if either > of the datetime fields is greater than the table value. I can't do a > union because it is the same row, but some of the returned fields will > change based on the presence of a row in the related table. All of the > rows returned because of the datetime criteria in the related table > are related to primary table records that don't fall within the time > period requested. I only want to pull ~2 weeks' worth of changed > records, but the related table entries sometimes don't happen for > several months. > > The issue I think I am having is that the related table will have NULL > for the datetime field if a record doesn't exist since it is an outer > join. > > I thought I could use an OR in the where clause, but it is returning > every row in the table. > > I then added an NVL to the OUTER table, but it gives me an " SQL Error > (-1264): Extra characters at the end of a datetime or interval." error. > Am I doing something wrong or can't this type of query work? > > Basic query: > Select 1,2,3,4,a.moddatetime,b.moddatetime > >From table1 a, OUTER table2 b > Where (a.moddate >= (Select datetime value from anothertable) OR> b.moddate > >= > (Select datetime value from anothertable)) > > I then modified where to be: > Where (a.moddate >= (Select datetime value from anothertable) OR > NVL(b.moddate,'1900-01-01 00:00:00.0000' >= (Select datetime value > from > anothertable)) > > But neither works as intended. > > TIA, > Randy > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d0447882d4b519604d1c98bde ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.