CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
S Ram Prakash
S Ram Prakash

Reputation: 19

Case and Decode

I have written a query to display the customer id, login id and sum of credit debt amount of the customer. If the customer due amount is to be debited a virtual column must be created and display as Due from customer else Due to Customer. Please help me solve the query.

I am attaching the table Structure and values along with my query.

-- Customer Table

CREATE TABLE isbs_customer_mst
  (
     cust_id   VARCHAR2(30) NOT NULL,
     login_id  VARCHAR2(30) NOT NULL,
     cust_nm   VARCHAR2(30),
     cust_addr VARCHAR2(300),
     CONSTRAINT isbs_customer_mst_pk PRIMARY KEY (cust_id)
  );

--Values

INSERT INTO ISBS_CUSTOMER_MST (CUST_ID, LOGIN_ID, CUST_NM, CUST_ADDR) 
VALUES ('CUST0000000001', 'USER1', 'User Login ID 1', '143/1 Uthamar Gandhi Salai, Nungambakkam, Chennai - 34');

INSERT INTO ISBS_CUSTOMER_MST (CUST_ID, LOGIN_ID, CUST_NM, CUST_ADDR)
VALUES ('CUST0000000002', 'USER2', 'User Login ID 2', '143/2 Uthamar Gandhi Salai, Nungambakkam, Chennai - 34');

INSERT INTO ISBS_CUSTOMER_MST (CUST_ID, LOGIN_ID, CUST_NM, CUST_ADDR) 
VALUES ('CUST0000000003', 'USER3', 'User Login ID 3', '143/3 Uthamar Gandhi Salai, Nungambakkam, Chennai - 34');

INSERT INTO ISBS_CUSTOMER_MST (CUST_ID, LOGIN_ID, CUST_NM, CUST_ADDR) 
VALUES ('CUST0000000004', 'USER4', 'User Login ID 4', '143/4 Uthamar Gandhi Salai, Nungambakkam, Chennai - 34');

--Credit Debit Table

CREATE TABLE isbs_acct_ledger_det
  (
     acct_ledger_id    VARCHAR2(30),
     cust_id           VARCHAR2(30),
     credit_debit_amt  VARCHAR2(30) NOT NULL,
     credit_debit_dttm TIMESTAMP NOT NULL,
     CONSTRAINT isbs_acct_ledger_det_pk PRIMARY KEY (acct_ledger_id),
     CONSTRAINT isbs_acct_ledger_det_fk FOREIGN KEY (cust_id) REFERENCES
     isbs_customer_mst (cust_id)
  );

-- Values

INSERT INTO ISBS_ACCT_LEDGER_DET (ACCT_LEDGER_ID, CUST_ID, CREDIT_DEBIT_AMT, CREDIT_DEBIT_DTTM) 
VALUES ('ACC0000000001', 'CUST0000000001', -1000.25, TO_DATE('01-10-2008 11:00:00', 'DD-MM-YYYY HH24:MI:SS'));

INSERT INTO ISBS_ACCT_LEDGER_DET (ACCT_LEDGER_ID, CUST_ID, CREDIT_DEBIT_AMT, CREDIT_DEBIT_DTTM) 
VALUES ('ACC0000000002', 'CUST0000000002', -256.75, TO_DATE('01-10-2008 11:00:00', 'DD-MM-YYYY HH24:MI:SS'));

INSERT INTO ISBS_ACCT_LEDGER_DET (ACCT_LEDGER_ID, CUST_ID, CREDIT_DEBIT_AMT, CREDIT_DEBIT_DTTM) 
VALUES ('ACC0000000003', 'CUST0000000002', 100.25, TO_DATE('05-10-2008 11:00:00', 'DD-MM-YYYY HH24:MI:SS'));

-- Query

SELECT c.CUST_NM
       , c.LOGIN_ID
       , SUM(a.CREDIT_DEBIT_AMT) "Outstanding Amt"
       , CASE WHEN a.CREDIT_DEBIT_AMT <= -9999.99 
             THEN 'Due to Cust' 
          ELSE 'Due from Cust' END "Due"
FROM ISBS_CUSTOMER_MST c
JOIN ISBS_ACCT_LEDGER_DET a
     ON c.CUST_ID = a.CUST_ID
GROUP BY c.CUST_NM, c.LOGIN_ID, a.CREDIT_DEBIT_AMT;

Thanks in advance

Upvotes: 1

Views: 68

Answers (3)

codeLover
codeLover

Reputation: 2592

Why would you use sin function when you are dealing with sum. Sin gives the sin value of the parameter Whereas abs returns the positive number, likewise 

Select abs(-5) from dual ;

Will return 5 as output.

I dont think your requirement needs both these functions.

Upvotes: 0

codeLover
codeLover

Reputation: 2592

You need to add case statement in group by clause:

SELECT c.CUST_NM , c.LOGIN_ID , SUM(a.CREDIT_DEBIT_AMT) "Outstanding Amt" , CASE WHEN a.CREDIT_DEBIT_AMT <= -9999.99 THEN 'Due to Cust' ELSE 'Due from Cust' END "Due" FROM ISBS_CUSTOMER_MST c JOIN ISBS_ACCT_LEDGER_DET a ON c.CUST_ID = a.CUST_ID GROUP BY c.CUST_NM, c.LOGIN_ID, CASE WHEN a.CREDIT_DEBIT_AMT <= -9999.99 THEN 'Due to Cust' ELSE 'Due from Cust' END "Due";

As it is the rule of aggregate functions to have all other selected attributes in group by clause

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

You probably need to remove a.CREDIT_DEBIT_AMT from group by and use CASE expression on SUM().

SELECT c.cust_nm,
       c.login_id,
       SUM(a.credit_debit_amt) "Outstanding Amt",
       CASE
         WHEN SUM(a.credit_debit_amt) <= -9999.99 THEN 'Due to Cust'
         ELSE 'Due from Cust'
       END                     "Due"
FROM   isbs_customer_mst c
       JOIN isbs_acct_ledger_det a
         ON c.cust_id = a.cust_id
GROUP  BY c.cust_nm,
          c.login_id ;

Upvotes: 2

Related Questions