 | |
A Bit of Query Help....
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
A Bit of Query Help.... -
01-31-2008
, 08:48 PM
#2
| |||
| |||
|
|
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. |
any faster response.
It's also annoying. So, stop it.
David Fitzjarrell
#3
| |||
| |||
|
|
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. |
any faster response.
It's also annoying. So, stop it.
David Fitzjarrell
#4
| |||
| |||
|
|
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. |
any faster response.
It's also annoying. So, stop it.
David Fitzjarrell
#5
| |||
| |||
|
|
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. |
any faster response.
It's also annoying. So, stop it.
David Fitzjarrell
#6
| |||
| |||
|
|
On Jan 31, 8:48 pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote: 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. Posting this twice under two different subject headings won't get you any faster response. It's also annoying. So, stop it. David Fitzjarrell |
Did not realize it. The first one took over 20 minutes to show up, so
I thought there was an error and it would not get posted....
#7
| |||
| |||
|
|
On Jan 31, 8:48 pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote: 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. Posting this twice under two different subject headings won't get you any faster response. It's also annoying. So, stop it. David Fitzjarrell |
Did not realize it. The first one took over 20 minutes to show up, so
I thought there was an error and it would not get posted....
#8
| |||
| |||
|
|
On Jan 31, 8:48 pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote: 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. Posting this twice under two different subject headings won't get you any faster response. It's also annoying. So, stop it. David Fitzjarrell |
Did not realize it. The first one took over 20 minutes to show up, so
I thought there was an error and it would not get posted....
#9
| |||
| |||
|
|
On Jan 31, 8:48 pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote: 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. Posting this twice under two different subject headings won't get you any faster response. It's also annoying. So, stop it. David Fitzjarrell |
Did not realize it. The first one took over 20 minutes to show up, so
I thought there was an error and it would not get posted....
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.