By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,145 Members | 819 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,145 IT Pros & Developers. It's quick & easy.

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

P: 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
Share this Question
Share on Google+
1 Reply

debasisdas
Expert 5K+
P: 8,127
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.