Re: Subqueries and numeric to character conversion problem

Mark Oberfield wrote: > > 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 Have you tried: 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_tabl) );? Hope that helps, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+