469,919 Members | 2,123 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,919 developers. It's quick & easy.

check decode fuction for ae,ext,gen and check joining

1
my problem is I have used invoice table, patient table.In Invoice table date,cash,credit, 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.DOCDATE,'DD/MM/YYYY') DATES,
SUM(DECODE(INV.INVOICETYPECODE,'CASH',INV.PATIENTA MOUNT,'00.00'))CASH,
SUM(DECODE(INV.INVOICETYPECODE,'CRDT',INV.PATIENTA MOUNT,'00.00')) CREDIT,
SUM(DECODE(INV.INVOICETYPECODE,'CHEQUE',INV.PATIEN TAMOUNT,'00.00')) CHEQUE,
SUM(NVL(INV.TOTALDEPOSITAMOUNT,0)) ADVANCE,
SUM(DECODE(INV.INVOICETYPECODE,'CASH',INV.PATIENTA MOUNT,'00.00'))+
SUM(DECODE(INV.INVOICETYPECODE,'CRDT',INV.PATIENTA MOUNT,'00.00'))+ SUM(DECODE(INV.INVOICETYPECODE,'CC',INV.PATIENTAMO UNT,'00.00'))- SUM(NVL(INV.TOTALDEPOSITAMOUNT,0))TOTAL,
DECODE(PAT.REGISTRATIONTYPECODE,'GEN',COUNT(PAT.RE GISTRATIONTYPECODE),'0')GENREG,
DECODE(PAT.REGISTRATIONTYPECODE,'EXT',COUNT(PAT.RE GISTRATIONTYPECODE),'0')EXTREG,
DECODE(PAT.REGISTRATIONTYPECODE,'AE',COUNT(PAT.REG ISTRATIONTYPECODE),'0')AEREG
/*COUNT(PAK.PACKAGETYPECODE)HEALTHPKG,
DECODE(SPT.SPONSORTYPECODE,'CORP',COUNT(SPT.SPONSO RTYPENAME),'0')CORPORATE*/
FROM INVOICES INV,PATIENTS PAT/*, /*P*\ACKAGES PAK,SPONSORTYPES SPT,INVOICEDETAILS IND *\*\*/
WHERE INV.DOCIDPATIENTS = PAT.DOCID
/*AND INV.DOCID = IND.DOCIDINVOICES(+)
AND IND.PACKAGECODE = PAK.PACKAGECODE
AND INV.SPONSORTYPECODE = SPT.SPONSORTYPECODE(+)
AND PAK.VISITTYPECODE ='OUT'*\*/
GROUP BY TO_CHAR(INV.DOCDATE,'DD/MM/YYYY'),PAT.REGISTRATIONTYPECODE/*,SPT.SPONSORTYPECODE*/
Jun 29 '07 #1
1 2579
debasisdas
8,127 Expert 4TB
Since i dont have the table it is very difficult to check the code over here.

Can you please post what is the error message you are getting.
Jun 29 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

27 posts views Thread by mrbog | last post: by
10 posts views Thread by N | last post: by
3 posts views Thread by mike | last post: by
1 post views Thread by anonymous | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.