Home » RDBMS Server » Server Administration » Re: decode
| Re: decode [message #373497] |
Thu, 19 April 2001 11:07  |
Rajeev Mancheril
Messages: 4
Registered: April 2001
|
Junior Member |
|
|
Hi,
SELECT field1, field2,
NVL(SUM(field3),0)
FROM INVACC
WHERE TRANTYPE = 'ALL' AND CONTDTE
BETWEEN date1 and date2
GROUP BY field1, field2
Will this solve your problem?
|
|
|
|
| Re: decode [message #373498 is a reply to message #373497] |
Thu, 19 April 2001 11:21  |
chris savage
Messages: 1
Registered: April 2001
|
Junior Member |
|
|
Thanks for your response.
I don't think it will solve my problem as I have seven columns populated by summations of various fields based on slightly different criteria.
So it would be the equivalent of having seven different where clauses.
An example of the type of SQL Server statement I am trying to convert is:
INSERT INTO DCRESULT (MEMBNO,DATACDE,STIND,CONTTYPE,CONTSRC,INVCDE,DCUSEIND,DATE1,DATE2,DATE3,AMOUNT3,UNITAMT1,UNITAMT2,UNITAMT4,AMOUNT4,UNITAMT3,UNITAMT5,FACTORID) SELECT MEMBNO,'STAT',STIND,CONTTYPE,CONTSRC,INVCDE,'CF','01-Jul-1998','30-Sep-1998','01-Oct-1998',
SUM(CASE WHEN (TRANTYPE = 'ALL' AND CONTDTE >= '01-Jul-1998') THEN RELCASH ELSE 0 END),
SUM(CASE WHEN (TRANTYPE = 'ALL' AND CONTDTE >= '01-Jul-1998') THEN INVQTY ELSE 0 END),
SUM(CASE WHEN (TRANTYPE = 'SLD' AND CONTDTE >= '01-Jul-1998') THEN CASHACC ELSE 0 END),
SUM(CASE WHEN (TRANTYPE = 'SLD' AND CONTDTE >= '01-Jul-1998') THEN INVQTY ELSE 0 END),
SUM(RELCASH),
SUM(INVQTY),
SUM(CASE WHEN (TRANTYPE = 'REB' AND CONTDTE >= '01-Jul-1998') THEN INVQTY ELSE 0 END),
CONVERT(varchar(12),SUM(CASE WHEN (TRANTYPE = 'ALL' AND SWGRP IS NOT NULL) THEN 1 WHEN (TRANTYPE = 'SLD' AND SWGRP IS NULL) THEN 100 WHEN (TRANTYPE = 'SLD' AND SWGRP IS NOT NULL) THEN 10000 WHEN (TRANTYPE = 'ALL' AND STIND = 'TFR') THEN 1000000 ELSE 0 END))
FROM INVACC
WHERE CONTDTE <= '30-Sep-1998' GROUP BY MEMBNO,INVCDE,STIND,CONTTYPE,CONTSRC
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 09:39:17 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
