my problem is I have used invoice table, patient table.In Invoice table date,cash,credi t, cheque,advance, total is coming.but in patient table having registration type code having gen registraion means gen, external reg means ext,acccident & emergency reg means AE.hear patient,invoice table have date column.but I have to display invoice date. that date no of gen reg, ext reg,ae reg not coming when joining two tables.gen reg, ext reg, AE reg i have used decode fuction. is it correct or not check it.
SELECT TO_CHAR(INV.DOC DATE,'DD/MM/YYYY') DATES,
SUM(DECODE(INV. INVOICETYPECODE ,'CASH',INV.PAT IENTAMOUNT,'00. 00'))CASH,
SUM(DECODE(INV. INVOICETYPECODE ,'CRDT',INV.PAT IENTAMOUNT,'00. 00')) CREDIT,
SUM(DECODE(INV. INVOICETYPECODE ,'CHEQUE',INV.P ATIENTAMOUNT,'0 0.00')) CHEQUE,
SUM(NVL(INV.TOT ALDEPOSITAMOUNT ,0)) ADVANCE,
SUM(DECODE(INV. INVOICETYPECODE ,'CASH',INV.PAT IENTAMOUNT,'00. 00'))+
SUM(DECODE(INV. INVOICETYPECODE ,'CRDT',INV.PAT IENTAMOUNT,'00. 00'))+ SUM(DECODE(INV. INVOICETYPECODE ,'CC',INV.PATIE NTAMOUNT,'00.00 '))- SUM(NVL(INV.TOT ALDEPOSITAMOUNT ,0))TOTAL,
DECODE(PAT.REGI STRATIONTYPECOD E,'GEN',COUNT(P AT.REGISTRATION TYPECODE),'0')G ENREG,
DECODE(PAT.REGI STRATIONTYPECOD E,'EXT',COUNT(P AT.REGISTRATION TYPECODE),'0')E XTREG,
DECODE(PAT.REGI STRATIONTYPECOD E,'AE',COUNT(PA T.REGISTRATIONT YPECODE),'0')AE REG
/*COUNT(PAK.PACK AGETYPECODE)HEA LTHPKG,
DECODE(SPT.SPON SORTYPECODE,'CO RP',COUNT(SPT.S PONSORTYPENAME) ,'0')CORPORATE*/
FROM INVOICES INV,PATIENTS PAT/*, /*P*\ACKAGES PAK,SPONSORTYPE S SPT,INVOICEDETA ILS IND *\*\*/
WHERE INV.DOCIDPATIEN TS = PAT.DOCID
/*AND INV.DOCID = IND.DOCIDINVOIC ES(+)
AND IND.PACKAGECODE = PAK.PACKAGECODE
AND INV.SPONSORTYPE CODE = SPT.SPONSORTYPE CODE(+)
AND PAK.VISITTYPECO DE ='OUT'*\*/
GROUP BY TO_CHAR(INV.DOC DATE,'DD/MM/YYYY'),PAT.REGI STRATIONTYPECOD E/*,SPT.SPONSORTY PECODE*/