RE: Subqueries and numeric to character conversion problem

I don't see anything wrong with your select statement but I can say this. A while ago I had te same problem with a subquery being referenced by not in. select ... from tab_a where tab_a.field_1 not in (select tab_b.field_1 from tab_b .....) The only way I got around my problem was to modify my query to validate it the subquery returns no rows. select ... from tab_a where 0 = (select count(*) from tab_b where tab_b.field_1 = tab_a.field_1.....) I have found that I have less trouble with this style of query then the other. Russ..... }-----Original Message----- }From: Mark Oberfield [SMTP:oberfiel@APWK01G1.nws.noaa.gov] }Sent: Friday, July 25, 1997 9:39 AM }To: informix-list@rmy.emory.edu }Subject: Subqueries and numeric to character conversion problem } }Greetings, } }I have a problem and I am hoping someone can tell me what I am doing }wrong and perhaps enlighten me on why the following doesn't work. }I spent about 2 hours yesterday trying to understand why it doesn't, }gave up and I'm turning to the gurus. } }I have many tables in a database that have the following format }%YYYYMMDDCC where % = a single character; YYYY = year; MM = Month; }DD = day; CC = cycle. So for today, I have the following tables: } }a1997072100 }b1997072100 }c1997072100 } . . . }etc. } }I want to drop tables that are "old" based on the date in the table }name. However, I don't want to drop tables that have a "lock" on }them. That information is stored in another table as an integer of }the format YYYYMMDDCC. So I have the following SQL to return a list }of YYYYMMDDCC tables in the database: } }SELECT DISTINCT tabname[2,11] FROM systables } WHERE tabname[2,11] MATCHES "[1-2]??[0-9]*"; (1) } }This returns something like: } }1997072100 }1997072012 }1997072000 }1997071912 }1997071900 }1997071812 } }The query for the lock table is simple. } }SELECT DISTINCT date_cycle FROM lock_table; (2) } }This returns: } }1997071912 }1997072012 } }Simply combining (1) and (2) with (2) as a subquery to the first }one doesn't work. I get a -1213. Okay, so I put quotes }around date_cycle: } }SELECT DISTINCT "'"||date_cycle||"'" FROM lock_table; (3) } }This returns: } }'1997071912' }'1997072012' } }The combined SELECT statement now looks like this: } }SELECT DISTINCT tabname[2,11] FROM systables } WHERE tabname[2,11] MATCHES "[1-2]??[0-9]*" AND } tabname[2,11] NOT IN ( SELECT DISTINCT "'"||date_cycle||"'" } FROM lock_table ); (4) } }But it still returns all the rows of query (1)! If }I remove the subquery and put the date_cycles (with quotes) }in by hand it works fine, i.e. the locked tables aren't returned: } }SELECT DISTINCT tabname[2,11] FROM systables } WHERE tabname[2,11] MATCHES "[1-2]??[0-9]*" AND } tabname[2,11] NOT IN ( '1997071912', '1997072012' ); } }Hence my "why" question. I suspect I am very close to getting }it right with (4) or that it cannot be done this way at all. } }Using DB-Access Version 5.03.UC1 } }TIA, } }mgo } }p.s. I apologize if this was posted twice. I didn't }recieve any response to my original post. I suspect }that my news server didn't post it, hence this time }I'm using Deja News. } }-------------------------------------------------------------- }Mark Oberfield }E-mail: oberfiel@storm.nws.noaa.gov }-------------------------------------------------------------- } }-------------------==== Posted via Deja News ====----------------------- } http://www.dejanews.com/ Search, Read, Post to Usenet