OraFAQ Forum: SQL & PL/SQL » Adding additional columns to query

Home » SQL & PL/SQL » SQL & PL/SQL » Adding additional columns to query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Adding additional columns to query [message #380623]

Mon, 12 January 2009 14:53

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

Report message to a moderator

Re: Adding additional columns to query [message #380632 is a reply to message #380623]

Mon, 12 January 2009 15:42

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: Adding additional columns to query [message #380864 is a reply to message #380632]

Tue, 13 January 2009 11:13

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

Report message to a moderator

Re: Adding additional columns to query [message #380875 is a reply to message #380864]

Tue, 13 January 2009 12:18

Frank
Messages: 7901
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 15:25:25 CST 2024