Strange sql behavior

Hello, I'm running ids 11.50.fc4 on Aix 5.3 ... the following query is giving strange behavior ... Any advice is appreciated... SELECT count(*) FROM ps_S_PAYPERIOD_STS WHERE S_HW_STATUS NOT IN (SELECTS_HW_STATUS FROM ps_S_UL_EE_STS_MVW) ; This returns the count of 0... SELECT S_HW_STATUS FROM ps_S_UL_EE_STS_MVW ;This returns a -217, column not found, s_h_w_status ... The -217 is correct since this column doesn't exist in the view. The question is why isn't the first sql also returning the error. It's like it doesn't care ... Please advise... Peter Logan Senior Database Administrator Phone: 616/878-8309

Peter_Logan@spartanstores.com wrote: > Hello, > > I'm running ids 11.50.fc4 on Aix 5.3 ... the following query is giving > strange behavior ... Any advice is appreciated... > > SELECT count(*) FROM ps_S_PAYPERIOD_STS WHERE S_HW_STATUS NOT IN (SELECT> S_HW_STATUS FROM ps_S_UL_EE_STS_MVW) ; > > This returns the count of 0... > > SELECT S_HW_STATUS FROM ps_S_UL_EE_STS_MVW ;> This returns a -217, column not found, s_h_w_status ... > > The -217 is correct since this column doesn't exist in the view. > > The question is why isn't the first sql also returning the error. It's > like it doesn't care ... > > Please advise... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 because in the first query s_hw_status is a statement local variable, not a column name. see http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.s qls.doc/ids_sqs_0164.htm -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

;-) This is a confusing one until you think about it. Maybe this will give you a clue: > select count(*) from syscolumns; (count(*)) 715 1 row(s) retrieved. > select count(*) from syscolumns where colno not in (select colno fromsystables); (count(*)) 0 1 row(s) retrieved. > select count(*) from syscolumns where colno in (select colno fromsystables); (count(*)) 715 1 row(s) retrieved. > Get it yet? What you did was set up a correlated sub-query that selected the constant value of the S_HW_STATUS column from the current record of the outer query from every row in the inner query's table. Of course, since that value is the one that you are comparing to, the value was found in every execution of the sub-query so all rows failed the NOT IN filter and the count returned was zero! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Mon, Mar 15, 2010 at 11:39 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Hello, > > I'm running ids 11.50.fc4 on Aix 5.3 ... the following query is giving > strange behavior ... Any advice is appreciated... > > SELECT count(*) FROM ps_S_PAYPERIOD_STS WHERE S_HW_STATUS NOT IN (SELECT> S_HW_STATUS FROM ps_S_UL_EE_STS_MVW) ; > > This returns the count of 0... > > SELECT S_HW_STATUS FROM ps_S_UL_EE_STS_MVW ;> This returns a -217, column not found, s_h_w_status ... > > The -217 is correct since this column doesn't exist in the view. > > The question is why isn't the first sql also returning the error. It's > like it doesn't care ... > > Please advise... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015174c1956d9f0df0481d94c68

Oh, if you had qualified the column name in the sub-query with the table name, you would have gotten the error you expected: > select count(*) from syscolumns where colno not in (select systables.colno from systables); 217: Column (colno) not found in any table in the query (or SLV is undefined). Error in line 1Near character position 69 > Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Mon, Mar 15, 2010 at 12:23 PM, Art Kagel <art.kagel@gmail.com> wrote: > ;-) > > This is a confusing one until you think about it. Maybe this will give you > a clue: > > > select count(*) from syscolumns;> > > (count(*)) > > 715 > > 1 row(s) retrieved. > > > select count(*) from syscolumns where colno not in (select colno from> systables); > > > (count(*)) > > 0 > > 1 row(s) retrieved. > > > select count(*) from syscolumns where colno in (select colno from> systables); > > > (count(*)) > > 715 > > 1 row(s) retrieved. > > > > > Get it yet? What you did was set up a correlated sub-query that selected > the constant value of the S_HW_STATUS column from the current record of the > outer query from every row in the inner query's table. Of course, since > that value is the one that you are comparing to, the value was found in > every execution of the sub-query so all rows failed the NOT IN filter and > the count returned was zero! > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > 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 Mon, Mar 15, 2010 at 11:39 AM, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > >> Hello, >> >> I'm running ids 11.50.fc4 on Aix 5.3 ... the following query is giving >> strange behavior ... Any advice is appreciated... >> >> SELECT count(*) FROM ps_S_PAYPERIOD_STS WHERE S_HW_STATUS NOT IN (SELECT>> S_HW_STATUS FROM ps_S_UL_EE_STS_MVW) ; >> >> This returns the count of 0... >> >> SELECT S_HW_STATUS FROM ps_S_UL_EE_STS_MVW ;>> This returns a -217, column not found, s_h_w_status ... >> >> The -217 is correct since this column doesn't exist in the view. >> >> The question is why isn't the first sql also returning the error. It's >> like it doesn't care ... >> >> Please advise... >> >> Peter Logan >> Senior Database Administrator >> Phone: 616/878-8309 >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --0016364c7c6535e58c0481d95493

s_h_w_status or s_hw_status? Does the column exist on table ps_s_payperiod? If it does it is correct behavior (You have my sympathy if you find this odd...) regards On Mon, Mar 15, 2010 at 3:39 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Hello, > > I'm running ids 11.50.fc4 on Aix 5.3 ... the following query is giving > strange behavior ... Any advice is appreciated... > > SELECT count(*) FROM ps_S_PAYPERIOD_STS WHERE S_HW_STATUS NOT IN (SELECT> S_HW_STATUS FROM ps_S_UL_EE_STS_MVW) ; > > This returns the count of 0... > > SELECT S_HW_STATUS FROM ps_S_UL_EE_STS_MVW ;> This returns a -217, column not found, s_h_w_status ... > > The -217 is correct since this column doesn't exist in the view. > > The question is why isn't the first sql also returning the error. It's > like it doesn't care ... > > Please advise... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e6db2b159803c00481d9aded

ah yes ... I understand ... For the record, I didn't create this .... Ha .. Thanks Art ... Peter Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 03/15/2010 12:26 PM Subject: Re: Strange sql behavior [19331] Sent by: ids-bounces@iiug.org ;-) This is a confusing one until you think about it. Maybe this will give you a clue: > select count(*) from syscolumns; (count(*)) 715 1 row(s) retrieved. > select count(*) from syscolumns where colno not in (select colno fromsystables); (count(*)) 0 1 row(s) retrieved. > select count(*) from syscolumns where colno in (select colno fromsystables); (count(*)) 715 1 row(s) retrieved. > Get it yet? What you did was set up a correlated sub-query that selected the constant value of the S_HW_STATUS column from the current record of the outer query from every row in the inner query's table. Of course, since that value is the one that you are comparing to, the value was found in every execution of the sub-query so all rows failed the NOT IN filter and the count returned was zero! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Mon, Mar 15, 2010 at 11:39 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Hello, > > I'm running ids 11.50.fc4 on Aix 5.3 ... the following query is giving > strange behavior ... Any advice is appreciated... > > SELECT count(*) FROM ps_S_PAYPERIOD_STS WHERE S_HW_STATUS NOT IN (SELECT > S_HW_STATUS FROM ps_S_UL_EE_STS_MVW) ; > > This returns the count of 0... > > SELECT S_HW_STATUS FROM ps_S_UL_EE_STS_MVW ;> This returns a -217, column not found, s_h_w_status ... > > The -217 is correct since this column doesn't exist in the view. > > The question is why isn't the first sql also returning the error. It's > like it doesn't care ... > > Please advise... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015174c1956d9f0df0481d94c68 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Yes it does ... and Art's explaination straightened me out ... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Fernando Nunes" <domusonline@gmail.com> To: ids@iiug.org Date: 03/15/2010 12:51 PM Subject: Re: Strange sql behavior [19333] Sent by: ids-bounces@iiug.org s_h_w_status or s_hw_status? Does the column exist on table ps_s_payperiod? If it does it is correct behavior (You have my sympathy if you find this odd...) regards On Mon, Mar 15, 2010 at 3:39 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Hello, > > I'm running ids 11.50.fc4 on Aix 5.3 ... the following query is giving > strange behavior ... Any advice is appreciated... > > SELECT count(*) FROM ps_S_PAYPERIOD_STS WHERE S_HW_STATUS NOT IN (SELECT > S_HW_STATUS FROM ps_S_UL_EE_STS_MVW) ; > > This returns the count of 0... > > SELECT S_HW_STATUS FROM ps_S_UL_EE_STS_MVW ;> This returns a -217, column not found, s_h_w_status ... > > The -217 is correct since this column doesn't exist in the view. > > The question is why isn't the first sql also returning the error. It's > like it doesn't care ... > > Please advise... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e6db2b159803c00481d9aded ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.