Re: Funky Query

From: <fitzjarrell_at_cox.net>
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

Original text of this message