Re: Funky Query
Date: Mon, 4 Feb 2008 05:40:21 -0800 (PST)
Message-ID: <8077cdc4-cdf4-4c3d-89b7-e2fd72013195@p69g2000hsa.googlegroups.com>
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