Home » SQL & PL/SQL » SQL & PL/SQL » Sub Query / Lookup
Sub Query / Lookup [message #278380] Sat, 03 November 2007 10:31 Go to next message
tracerrx
Messages: 5
Registered: November 2007
Location: Fort Lauderdale, FL
Junior Member
Please forgive me in advance, I'm totally new to oracle (fresh from MySQL). I have the following Query for a report which works well:
SELECT 
CASE WHEN (RX.STORENBR = '101114') THEN 'CC-FTL'  
WHEN (RX.STORENBR = '103073') THEN 'CC-MIA'  
WHEN (RX.STORENBR = '102520') THEN 'CC-WPB'  
ELSE '0' 
END AS PHARMACY_NAME, 
CASE WHEN (RX.STORENBR = '101114') THEN '1598762015'  
WHEN (RX.STORENBR = '103073') THEN '1063423606'  
WHEN (RX.STORENBR = '102520') THEN '1770580292'  
ELSE '0' 
END AS PHARMACY_NPI,
CASE WHEN (RX.STORENBR = '101114') THEN '1079638'  
WHEN (RX.STORENBR = '103073') THEN '1019303'  
WHEN (RX.STORENBR = '102520') THEN '1099286'  
ELSE '0' 
END AS PHARMACY_NCPDP,
CASE WHEN (RX.STORENBR = '101114') THEN 'BN9568861'  
WHEN (RX.STORENBR = '103073') THEN 'BN9754450'  
WHEN (RX.STORENBR = '102520') THEN 'BA8399962'  
ELSE '0' 
END AS PHARMACY_DEA,
PATIENT.PATSTOREID AS PT_STORE, 
PATIENT.PATKEY AS PT_KEY, 
(PATIENT.PATSTOREID || LPAD(PATIENT.PATKEY, 6, 0)) AS PTID, 
DOCTOR.NAMELAST AS PRESCRIBER_LAST, 
DOCTOR.NAMEFIRST AS PRESCRIBER_FIRST, 
DOCTOR.ADRSCITY AS PRESCRIBER_CITY, 
DOCTOR.ADRSSTATE AS PRESCRIBER_STATE, 
DOCTOR.ADRSZIP AS PRESCRIBER_ZIP, 
DOCTOR.STATENUMBER,
DOCTOR.DEAALPHAALPHA || DOCTOR.DEAALPHANBR || DOCTOR.DEAALPHASFX AS PRESCRIBER_DEA,
CASE WHEN (DOCTOR.PLANCODE01 = 'NPI') THEN DOCTOR.PLANID01 
WHEN (DOCTOR.PLANCODE02 = 'NPI') THEN DOCTOR.PLANID02 
WHEN (DOCTOR.PLANCODE03 = 'NPI') THEN DOCTOR.PLANID03 
ELSE '0' 
END AS NPI, 
DOCTOR.SPECIALTY,    
DRUG.NAME, DRUG.DRUGNDCNBR,
PLANS.PLANID, 
(NVL(RX.CASHPAID, 0)/100) AS COPAY, 
to_char(to_date(RX.FILLDATE, 'yyyyddd'), 'MM/DD/YYYY') AS DISPENSED_DATE,
RX.DISPENSEDQTY01, RX.DAYSUPP,  
FROM RX 
LEFT OUTER JOIN PLANS ON (RX.PLANSTOREID01=PLANS.PLANCODESTOREID AND RX.PLANKEY01=PLANS.PLANCODEKEY)  
LEFT OUTER JOIN DRUG ON (RX.DISPENSEDDRUGSTOREID=DRUG.DRUGNBRSTOREID AND RX.DISPENSEDDRUGKEY=DRUG.DRUGNBRKEY) 
LEFT OUTER JOIN DOCTOR ON (RX.DOCNBRSTOREID=DOCTOR.DOCSTOREID AND RX.DOCNBRKEY = DOCTOR.DOCKEY) 
LEFT OUTER JOIN PATIENT ON (RX.PATNBRSTOREID=PATIENT.PATSTOREID AND RX.PATNBRKEY=PATIENT.PATKEY) 
WHERE RX.FILLDATE >= 2007274 
AND RX.FILLDATE <= 2007304 
AND DRUG.DRUGNDCNBR = 0004038039 
AND NVL(RX.RXSTATUS, 0) <> 16 
AND NVL(RX.RXSTATUS, 0) <> 17 
AND NVL(RX.RXSTATUS, 0) <> 18 
AND NVL(RX.RXSTATUS, 0) <> 20 
AND NVL(RX.RXSTATUS, 0) <> 24 
AND NVL(RX.RXSTATUS, 0) <> 32 
AND NVL(RX.RXSTATUS, 0) <> 33 
AND NVL(RX.RXSTATUS, 0) <> 48 



However, I wan to add a lookup/subquery for each row returned by the main query to return a new field the LEAST(RX.FILLDATE) on each record returned by the main query. I attempted inserting subquery such as:
(
  SELECT * FROM (
                  SELECT RX.FILLDATE AS FIRST_DATE 
                  FROM RX
                  LEFT OUTER JOIN DRUG ON (RX.DISPENSEDDRUGSTOREID=DRUG.DRUGNBRSTOREID AND RX.DISPENSEDDRUGKEY=DRUG.DRUGNBRKEY)
                  WHERE RX.PATNBRSTOREID=PT_STORE  
                  AND RX.PATNBRKEY=PT_KEY  
                  AND DRUG.DRUGNDCNBR = 0004038039  
                  ORDER BY LEAST(RX.FILLDATE) 
                )
  WHERE ROWNUM = 1
)


However, this acts during the query, and not on each row returned from the main query. Any ideas on HOW to return this information for each row of the main query?

Thank You
Re: Sub Query / Lookup [message #278382 is a reply to message #278380] Sat, 03 November 2007 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please first read about LEAST and MIN function in SQL Reference.
Then simplify your exemple, this not a question of this very particular query, it is a question that you don't know how to do something.
Build an exemple around what you don't know. This will help you to understand the problem and help us to explain you.

Regards
Michel
Re: Sub Query / Lookup [message #278383 is a reply to message #278382] Sat, 03 November 2007 10:44 Go to previous messageGo to next message
tracerrx
Messages: 5
Registered: November 2007
Location: Fort Lauderdale, FL
Junior Member
I understand LEAST and MIN. What I don't understand is how to perform and additional query, on the original result set, to return the first date that.

The original query will return something like:
MISC | Record Date
Result 1 | 10/01/2007
Result 2 | 10/02/2007

What I need to also know is the date these patient FIRST had records added:
MISC | This Record Date | Subquery Record Date
Result 1 | 10/01/2007 | 01/01/2006
Result 2 | 10/02/2007 | 02/12/2004

Re: Sub Query / Lookup [message #278384 is a reply to message #278383] Sat, 03 November 2007 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Given what you wrote, I can say that you don't know what LEAST and MIN are for.
Please read the documentation.

If you don't agree, explain what you (think you) know about these functions and we tell you what's wrong.

Regards
Michel
Re: Sub Query / Lookup [message #278387 is a reply to message #278384] Sat, 03 November 2007 11:44 Go to previous messageGo to next message
tracerrx
Messages: 5
Registered: November 2007
Location: Fort Lauderdale, FL
Junior Member
I understand that I need to use MIN... and probably something along the lines of:

MIN(RX.FILLDATE) OVER (partition by RX.PATNBRSTOREID, RX.PATNBRKEY) earliest_fill_date


But what I do not understand, is how to execute this on * rather than on the result set of the original "WHERE" clause.
Re: Sub Query / Lookup [message #278388 is a reply to message #278387] Sat, 03 November 2007 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select ename, hiredate, 
  2         min(hiredate) over(partition by deptno) "First hire in dept"
  3  from emp
  4  order by ename
  5  /
ENAME      HIREDATE    First hire
---------- ----------- -----------
ADAMS      23-MAY-1987 17-DEC-1980
ALLEN      20-FEB-1981 20-FEB-1981
BLAKE      01-MAY-1981 20-FEB-1981
CLARK      09-JUN-1981 09-JUN-1981
FORD       03-DEC-1981 17-DEC-1980
JAMES      03-DEC-1981 20-FEB-1981
JONES      02-APR-1981 17-DEC-1980
KING       17-NOV-1981 09-JUN-1981
MARTIN     28-SEP-1981 20-FEB-1981
MILLER     23-JAN-1982 09-JUN-1981
SCOTT      19-APR-1987 17-DEC-1980
SMITH      17-DEC-1980 17-DEC-1980
TURNER     08-SEP-1981 20-FEB-1981
WARD       22-FEB-1981 20-FEB-1981

14 rows selected.

Regards
Michel
Re: Sub Query / Lookup [message #278389 is a reply to message #278388] Sat, 03 November 2007 12:10 Go to previous messageGo to next message
tracerrx
Messages: 5
Registered: November 2007
Location: Fort Lauderdale, FL
Junior Member
Understood, but in the example above how would you only select records with a hire date greater than 1982, and still pull their original hire date which is prior to this, and therefore out of the result set?

I do appreciate the help.
Re: Sub Query / Lookup [message #278390 is a reply to message #278389] Sat, 03 November 2007 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (
  2    select ename, hiredate, 
  3           min(hiredate) over(partition by deptno) "First hire in dept"
  4    from emp
  5  )
  6  select * from data where hiredate >= to_date('1983','YYYY')
  7  order by ename
  8  /
ENAME      HIREDATE    First hire
---------- ----------- -----------
ADAMS      23-MAY-1987 17-DEC-1980
SCOTT      19-APR-1987 17-DEC-1980

2 rows selected.

Regards
Michel
Re: Sub Query / Lookup [message #278391 is a reply to message #278380] Sat, 03 November 2007 12:25 Go to previous messageGo to next message
tracerrx
Messages: 5
Registered: November 2007
Location: Fort Lauderdale, FL
Junior Member
Michael,

Thank you for bearing with me. I guess I was not clear in my original question. That is EXACTLY what I needed.

Regards,
Re: Sub Query / Lookup [message #278392 is a reply to message #278391] Sat, 03 November 2007 13:02 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is important when you post a question to extract the smallest unit that reflects your problem.
Then you 1) better understand your problem and even can solve it this way, 2) let us better understand what the problem is and help you find a solution.
Most of the time, classic scott schema (or another Oracle demo schema) is sufficient to explain the case.

Regards
Michel
Previous Topic: case expression
Next Topic: solve the query
Goto Forum:
  


Current Time: Sun Dec 04 10:59:51 CST 2016

Total time taken to generate the page: 0.07962 seconds