Re: Funky Query
Date: Fri, 1 Feb 2008 06:36:43 -0800 (PST)
Message-ID: <4a1c0ea3-0f27-4321-b30d-5311d0a8f94f@i7g2000prf.googlegroups.com>
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) forcode '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 thecorrect 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 Received on Fri Feb 01 2008 - 08:36:43 CST