Re: Funky Query

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Sat, 02 Feb 2008 16:52:36 GMT
Message-ID: <od1pj.9110$Ej5.8332@newssvr29.news.prodigy.net>


fitzjarrell_at_cox.net wrote:
> On Jan 31, 8:54 pm, Geoff Muldoon <geoff.muld..._at_trap.gmail.com>
> wrote:

>> ame..._at_iwc.net says...
>>
>>
>>
>>
>>
>>> Hi,
>>> I need to write a query or sub-query, but only use a parameter once.
>>> Take a look at this:
>>> A     Z198051              27-AUG-07 27-AUG-07
>>> B     Z199111              31-AUG-07 31-AUG-07
>>> C     Z215180              17-OCT-07 17-OCT-07
>>> D     Z27867               21-FEB-06 22-FEB-06
>>> E     Z239557              28-DEC-07 28-DEC-07
>>> F     Z230612              01-DEC-07 03-DEC-07
>>> G     Z108701              22-NOV-06 23-NOV-06
>>> H     Z171480              04-JUN-07 04-JUN-07
>>> I     Z143538              02-MAR-07 02-MAR-07
>>> J     Z107108              16-NOV-06 17-NOV-06
>>> K     Z114553              18-DEC-06 19-DEC-06
>>> L     Z168095              24-MAY-07 24-MAY-07
>>> M     Z82875               22-AUG-06 23-AUG-06
>>> N     Z71657               14-JUL-06 14-JUL-06
>>> O     Z55598               19-MAY-06 19-MAY-06
>>> Basically the code (1st column) will be passed in, and I want to
>>> retrieve the ID (second column) for that code with the MAX date (3rd
>>> column).
>>> Problem being that the way the engine for this application is
>>> written,
>>> you can only pass 1 parameter to it.  So, I can only pass the code
>>> once, which means I can only use it in the query once......
>> a) Protocol for assistance requests here is to provide your Oracle version
>> and O/S platform in case it is relevant to a solution.
>>
>> b) Your description of the problem makes no sense.  Is your "third
>> column" a string containing multiple dates?  And passing a single
>> parameter to a query doesn't preclude that parameter from being referenced
>> multiple times in a query.
>>
>> GM- Hide quoted text -
>>
>> - Show quoted text -

>
> Your example data doesn't suit the situation, as you have only one
> record per each code; a simple
>
> select id from table1 where code = ...
>
> returns one, and only one, record. To provide for your problem
> description look at the following:
>
> SQL>
> SQL> --
> SQL> -- Create example table
> SQL> --
> SQL> create table testtbl(
> 2 code varchar2(2),
> 3 id varchar2(20),
> 4 date1 date,
> 5 date2 date
> 6 );
>
> Table created.
>
> SQL> --
> SQL> -- Load it with data
> SQL> --
> SQL> -- Ensure there are multiple entries
> SQL> -- for each code
> SQL> --
> SQL> insert all
> 2 into testtbl
> 3 values('A','Z198051',to_date('27-AUG-07','DD-MON-YY'),to_date('27-
> AUG-07','DD-MON-YY'))
> 4 into testtbl
> 5 values('B','Z199111',to_date('31-AUG-07','DD-MON-YY'),to_date('31-
> AUG-07','DD-MON-YY'))
> 6 into testtbl
> 7 values('C','Z215180',to_date('17-OCT-07','DD-MON-YY'),to_date('17-
> OCT-07','DD-MON-YY'))
> 8 into testtbl
> 9 values('D','Z27867',to_date('21-FEB-06','DD-MON-YY'),to_date('22-
> FEB-06','DD-MON-YY'))
> 10 into testtbl
> 11 values('E','Z239557',to_date('28-DEC-07','DD-MON-YY'),to_date('28-
> DEC-07','DD-MON-YY'))
> 12 into testtbl
> 13 values('F','Z230612',to_date('01-DEC-07','DD-MON-YY'),to_date('03-
> DEC-07','DD-MON-YY'))
> 14 into testtbl
> 15 values('G','Z108701',to_date('22-NOV-06','DD-MON-YY'),to_date('23-
> NOV-06','DD-MON-YY'))
> 16 into testtbl
> 17 values('H','Z171480',to_date('04-JUN-07','DD-MON-YY'),to_date('04-
> JUN-07','DD-MON-YY'))
> 18 into testtbl
> 19 values('I','Z143538',to_date('02-MAR-07','DD-MON-YY'),to_date('02-
> MAR-07','DD-MON-YY'))
> 20 into testtbl
> 21 values('J','Z107108',to_date('16-NOV-06','DD-MON-YY'),to_date('17-
> NOV-06','DD-MON-YY'))
> 22 into testtbl
> 23 values('K','Z114553',to_date('18-DEC-06','DD-MON-YY'),to_date('19-
> DEC-06','DD-MON-YY'))
> 24 into testtbl
> 25 values('L','Z168095',to_date('24-MAY-07','DD-MON-YY'),to_date('24-
> MAY-07','DD-MON-YY'))
> 26 into testtbl
> 27 values('M','Z82875',to_date('22-AUG-06','DD-MON-YY'),to_date('23-
> AUG-06','DD-MON-YY'))
> 28 into testtbl
> 29 values('N','Z71657',to_date('14-JUL-06','DD-MON-YY'),to_date('14-
> JUL-06','DD-MON-YY'))
> 30 into testtbl
> 31 values('O','Z55598',to_date('19-MAY-06','DD-MON-YY'),to_date('19-
> MAY-06','DD-MON-YY'))
> 32 into testtbl
> 33 values('O','Z198051',to_date('27-AUG-07','DD-MON-YY'),to_date('27-
> AUG-07','DD-MON-YY'))
> 34 into testtbl
> 35 values('N','Z199111',to_date('31-AUG-07','DD-MON-YY'),to_date('31-
> AUG-07','DD-MON-YY'))
> 36 into testtbl
> 37 values('M','Z215180',to_date('17-OCT-07','DD-MON-YY'),to_date('17-
> OCT-07','DD-MON-YY'))
> 38 into testtbl
> 39 values('L','Z27867',to_date('21-FEB-06','DD-MON-YY'),to_date('22-
> FEB-06','DD-MON-YY'))
> 40 into testtbl
> 41 values('K','Z239557',to_date('28-DEC-07','DD-MON-YY'),to_date('28-
> DEC-07','DD-MON-YY'))
> 42 into testtbl
> 43 values('J','Z230612',to_date('01-DEC-07','DD-MON-YY'),to_date('03-
> DEC-07','DD-MON-YY'))
> 44 into testtbl
> 45 values('I','Z108701',to_date('22-NOV-06','DD-MON-YY'),to_date('23-
> NOV-06','DD-MON-YY'))
> 46 into testtbl
> 47 values('H','Z171480',to_date('09-JUN-07','DD-MON-YY'),to_date('14-
> JUN-07','DD-MON-YY'))
> 48 into testtbl
> 49 values('G','Z143538',to_date('02-MAR-07','DD-MON-YY'),to_date('02-
> MAR-07','DD-MON-YY'))
> 50 into testtbl
> 51 values('F','Z107108',to_date('16-NOV-06','DD-MON-YY'),to_date('17-
> NOV-06','DD-MON-YY'))
> 52 into testtbl
> 53 values('E','Z114553',to_date('18-DEC-06','DD-MON-YY'),to_date('19-
> DEC-06','DD-MON-YY'))
> 54 into testtbl
> 55 values('D','Z168095',to_date('24-MAY-07','DD-MON-YY'),to_date('24-
> MAY-07','DD-MON-YY'))
> 56 into testtbl
> 57 values('C','Z82875',to_date('22-AUG-06','DD-MON-YY'),to_date('23-
> AUG-06','DD-MON-YY'))
> 58 into testtbl
> 59 values('B','Z71657',to_date('14-JUL-06','DD-MON-YY'),to_date('14-
> JUL-06','DD-MON-YY'))
> 60 into testtbl
> 61 values('A','Z55598',to_date('19-MAY-06','DD-MON-YY'),to_date('19-
> MAY-06','DD-MON-YY'))
> 62 select * from dual;
>
> 30 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> --
> SQL> -- Display all of the data
> SQL> --
> SQL> select * from testtbl order by code, date1;
>
> CO ID DATE1 DATE2
> -- -------------------- --------- ---------
> A Z55598 19-MAY-06 19-MAY-06
> A Z198051 27-AUG-07 27-AUG-07
> B Z71657 14-JUL-06 14-JUL-06
> B Z199111 31-AUG-07 31-AUG-07
> C Z82875 22-AUG-06 23-AUG-06
> C Z215180 17-OCT-07 17-OCT-07
> D Z27867 21-FEB-06 22-FEB-06
> D Z168095 24-MAY-07 24-MAY-07
> E Z114553 18-DEC-06 19-DEC-06
> E Z239557 28-DEC-07 28-DEC-07
> F Z107108 16-NOV-06 17-NOV-06
>
> CO ID DATE1 DATE2
> -- -------------------- --------- ---------
> F Z230612 01-DEC-07 03-DEC-07
> G Z108701 22-NOV-06 23-NOV-06
> G Z143538 02-MAR-07 02-MAR-07
> H Z171480 04-JUN-07 04-JUN-07
> H Z171480 09-JUN-07 14-JUN-07
> I Z108701 22-NOV-06 23-NOV-06
> I Z143538 02-MAR-07 02-MAR-07
> J Z107108 16-NOV-06 17-NOV-06
> J Z230612 01-DEC-07 03-DEC-07
> K Z114553 18-DEC-06 19-DEC-06
> K Z239557 28-DEC-07 28-DEC-07
>
> CO ID DATE1 DATE2
> -- -------------------- --------- ---------
> L Z27867 21-FEB-06 22-FEB-06
> L Z168095 24-MAY-07 24-MAY-07
> M Z82875 22-AUG-06 23-AUG-06
> M Z215180 17-OCT-07 17-OCT-07
> N Z71657 14-JUL-06 14-JUL-06
> N Z199111 31-AUG-07 31-AUG-07
> O Z55598 19-MAY-06 19-MAY-06
> O Z198051 27-AUG-07 27-AUG-07
>
> 30 rows selected.
>
> SQL>
> SQL> --
> SQL> -- Display all records for a given code
> SQL> --
> SQL> select * from testtbl where code = '&&1' order by code, date1;
> old 1: select * from testtbl where code = '&&1' order by code, date1
> new 1: select * from testtbl where code = 'A' order by code, date1
>
> CO ID DATE1 DATE2
> -- -------------------- --------- ---------
> A Z55598 19-MAY-06 19-MAY-06
> A Z198051 27-AUG-07 27-AUG-07 <---- max(date1) for
> code 'A'
>
> SQL>
> SQL> --
> SQL> -- Return desired id
> SQL> --
> SQL> select id
> 2 from testtbl
> 3 where (code, date1) in
> 4 (select code, max(date1) from testtbl where code = '&&1' group by
> code);
> old 4: (select code, max(date1) from testtbl where code = '&&1'
> group by code)
> new 4: (select code, max(date1) from testtbl where code = 'A' group
> by code)
>
> ID
> --------------------
> Z198051 <--- And we, indeed, return the
> correct record
>
> SQL>
>
> The &&1 is your variable; notice the value has only been used once in
> the query. Of course with the && syntax you can reuse this value over
> and over and over and over until you either terminate the session or
> undefine the variable.
>
>
> David Fitzjarrell

Fitz - this looks like homework ("but only use a parameter once"... why be this restrictive if it isn't homework), and if someone is this green - should spend a lot more time understanding sql instead of getting us to do the work for him...

Hopefully the professor knows how to search the Oracle NG to find plagiarists. Received on Sat Feb 02 2008 - 10:52:36 CST

Original text of this message