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......

I try things like this, but no luck:

SELECT id
FROM table1
WHERE date1 = (SELECT MAX(date1)
FROM table1
WHERE code = 'E' AND id LIKE 'Z%');


Any ideas? I've been playing with sub-selects and MAX(date), but it
seems that I still need to put CODE in there twice......


Thanks a bunch.

amerar (AT) iwc (DOT) net says...
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

amerar (AT) iwc (DOT) net says...
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

amerar (AT) iwc (DOT) net says...
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

amerar (AT) iwc (DOT) net says...
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

On Jan 31, 8:54*pm, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com>
wrote:
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

On Jan 31, 8:54*pm, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com>
wrote:
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

On Jan 31, 8:54*pm, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com>
wrote:
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

On Jan 31, 8:54*pm, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com>
wrote:
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

fitzjarrell (AT) cox (DOT) net wrote:
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.