join and partition by

join and partition by.sql

CREATE TABLE  MYRXDATA
(
  DATAKEY  DATE,
  NDC_KEY VARCHAR2(20),
  COPAY_AMT NUMBER(10,2)
);
INSERT INTO myrxdata VALUES('01-JAN-05','11111111111',5.12) ;
INSERT INTO myrxdata VALUES('01-FEB-05','22222222222',10.24) ;

CREATE TABLE DATEDIMENSION(
  DATAKEY DATE
);
INSERT INTO DATEDIMENSION VALUES('01-JAN-05');
INSERT INTO DATEDIMENSION VALUES('01-FEB-05');
INSERT INTO DATEDIMENSION VALUES('01-MAR-05');


-- old method
SELECT C.DATAKEY,C.NDC_KEY,SUM(C.COPAY_AMT) AS TOT FROM
(SELECT A.DATAKEY,B.NDC_KEY,NULL AS COPAY_AMT
FROM (SELECT DISTINCT DATAKEY
            FROM DATEDIMENSION) A,(SELECT DISTINCT NDC_KEY FROM MYRXDATA) B
      UNION ALL
      SELECT DATAKEY,NDC_KEY,COPAY_AMT
      FROM MYRXDATA
) C
GROUP BY C.DATAKEY,C.NDC_KEY;

-- new way
SELECT B.DATAKEY,
  A.NDC_KEY,
  A.COPAY_AMT
FROM MYRXDATA A PARTITION BY (A.NDC_KEY)
RIGHT OUTER JOIN DATEDIMENSION B
ON A.DATAKEY=B.DATAKEY;