Home » SQL & PL/SQL » SQL & PL/SQL » Adding additional columns to query
Adding additional columns to query [message #380623] Mon, 12 January 2009 14:53 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Hi,

I need suggestions on how I can rewrite the following portion of query column to include additional categories. For example, I need to add the additional expenditure_categories to the column requirements: Cost Share-Direct, Cost Share-Non, Cost Share-Indirect and Cost Share-Research. How can I rewrite this to include the above additional categories?


SUM(DECODE(SUBSTR(et.expenditure_category,1,10),'Cost Share',NVL(GMSAC.BURDENED_COST,0), 0)) CS_EXP,



Anne
Re: Adding additional columns to query [message #380632 is a reply to message #380623] Mon, 12 January 2009 15:42 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this, perhaps?
SUM(DECODE(SUBSTR(et.expenditure_category, 1, 10),
             'Cost Share'         , NVL(GMSAC.BURDENED_COST, 0), 
             'Cost Share - Direct', some_value_here,
             'Cost Share - Non'   , yet_another_value_here,
              0
          )) CS_EXP

Note that SUBSTR(string, 1, 10) will probably not be enough (for example, 'Cost Share - Direct' is longer than 10 characters).
Re: Adding additional columns to query [message #380864 is a reply to message #380632] Tue, 13 January 2009 11:13 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
So here is my ending result, would this be correct?


 SUM(DECODE(SUBSTR(et.expenditure_category, 1, 20),
             'Cost Share', NVL(GMSAC.BURDENED_COST, 0), 
             'Cost Share-Direct', NVL(GMSAC.BURDENED_COST, 0),
             'Cost Share-Indirect', NVL(GMSAC.BURDENED_COST, 0),
             'Research Enhancement', NVL(GMSAC.BURDENED_COST, 0),0)) CS_EXP,



Anne
Re: Adding additional columns to query [message #380875 is a reply to message #380864] Tue, 13 January 2009 12:18 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you are checking the first 20 positions of this column, I doubt if that will ever equal 'Cost Share'.
Use CASE WHEN column LIKE ....

and use a correct order of checking: use 'Cost Share-Direct%' before you check on 'Cost Share%'

[Updated on: Tue, 13 January 2009 12:21]

Report message to a moderator

Previous Topic: "instead of triggers"
Next Topic: How to get Procedure values into a Local Variable
Goto Forum:
  


Current Time: Sun Dec 04 06:48:37 CST 2016

Total time taken to generate the page: 0.12583 seconds