Re: Funky Query

On Feb 2, 10:52�am, Michael Austin <maus..._at_firstdbasource.com> wrote:
> fitzjarr..._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),

Mikey,

It may be, it may not be, however I am presuming it isn't as I've also encountered applications which accept one passed parameter. It didn't appear to be 'homework' to me.

> and if someone is this green
> - should spend a lot more time understanding sql instead of getting us
> to do the work for him...

Such is your opinion. Mine differs. Although in the end one of us will be right, and it may well be you. Que sera.

>
> Hopefully the professor knows how to search the Oracle NG to find
> plagiarists.

Should that be necessary.

David Fitzjarrell Received on Mon Feb 04 2008 - 07:40:21 CST