Problem in oracle query

Question

Hai guys,

I've a query in which i need to interchange the values of two fields. The query is as follows:

SELECT TO_DATE(A.G_LEDGER_DATE,'dd/mm/YYY')as G_LEDGER_DATE,C.ACC_MASTER_NAME,
  A.G_LEDGER_REF_NO ,
  NVL(CASE WHEN B.G_LEDGER_SECTION = 1 THEN 
  CASE WHEN 
   (SELECT COUNT(*)FROM SOSTRANS.ACC_GEN_LEDGER WHERE G_LEDGER_SECTION = B.G_LEDGER_SECTION  AND G_LEDGER_ID = B.G_LEDGER_ID)> 1 THEN 
  B.G_LEDGER_VALUE  ELSE A.G_LEDGER_VALUE END END,0) AS G_LEDGER_DR_VALUE,  
  NVL(CASE WHEN B.G_LEDGER_SECTION = -1 THEN 
  CASE WHEN 
  (SELECT COUNT(*) FROM SOSTRANS.ACC_GEN_LEDGER WHERE G_LEDGER_SECTION = B.G_LEDGER_SECTION  AND G_LEDGER_ID = B.G_LEDGER_ID)> 1 THEN 
  B.G_LEDGER_VALUE ELSE A.G_LEDGER_VALUE END END,0) AS G_LEDGER_CR_VALUE,  
  B.G_LEDGER_SECTION,C.ACC_MASTER_ID,SUBSTR(A.G_LEDGER_REF_NO,0,3) AS Types,'Z' as OrderChar ,
    CASE WHEN A.G_LEDGER_REMARK IS NULL THEN B.G_LEDGER_REMARK ELSE A.G_LEDGER_REMARK END AS Narration 
    FROM SOSTRANS.ACC_GEN_LEDGER A  
    LEFT OUTER JOIN SOSTRANS.ACC_GEN_LEDGER B ON A.G_LEDGER_ID = B.G_LEDGER_ID  
    LEFT OUTER JOIN SOSMASTER.ACC_ACCOUNT_MASTER C ON A.ACC_MASTER_ID = C.ACC_MASTER_ID  WHERE A.G_LEDGER_CANCEL='N' AND
  B.ACC_MASTER_ID = 'MSOS000001' AND
  A.ACC_MASTER_ID <> 'MSOS000001' AND
  A.G_LEDGER_SECTION <> B.G_LEDGER_SECTION  AND
  A.G_LEDGER_DATE >= '25/sep/2009' AND
  A.G_LEDGER_DATE<='26/sep/2009' 
  ORDER BY OrderChar,G_LEDGER_DATE

Now i get the output as

...  G_LEDGER_DR_VALUE  G_LEDGER_CR_VALUE .....
...       2000                0           .....
...       3000                0           .....
...      -1000                0           .....

I need to get the negetive value of the G_LEDGER_DR_VALUE side in G_LEDGER_CR_VALUE and if negetive value exists in G_LEDGER_CR_VALUE then it should be in the G_LEDGER_DR_VALUE field Can anyone help me to solve this?

Answer 1

If I understood your question well, you select a value (that I will call g_ledger_value) that you want to appear in a different column depending on its sign.

This is how I would do it :

SELECT
    CASE WHEN t.g_ledger_value>0 THEN t.g_ledger_value ELSE 0 END AS g_ledger_dr_value,
    CASE WHEN t.g_ledger_value<0 THEN t.g_ledger_value ELSE 0 END AS g_ledger_cr_value
FROM
    (SELECT g_ledger_value FROM mytable) t;

Answer 2

It sounds like a combination of SIGN() and CASE is what you need ...

CASE WHEN SIGN(G_LEDGER_DR_VALUE) = -1 then ...
ELSE ...
END

etc

Answer 3

SELECT G_LEDGER_DR_VALUE, 
       CASE WHEN G_LEDGER_DR_VALUE < 0 
        THEN G_LEDGER_CR_VALUE
        ELSE G_LEDGER_DR_VALUE
       END
FROM (...)

Is it that you mean? I suggest calculate values of CR___VALUE and DR_VALUE in subquery, and then in wrapping query make CASE which returns you correct value.