update one field in a view?

[LMCRYER]

I have the view (below) working great. It is not throwing All I need to do is get one more value in there, but I am having trouble understanding what I am doing wrong!

NOTE -
field "R" is supposed to have the criteria 'sum_unit' = 'R'
& the field "C" should be coming from 'sum_unit' = 'C'

I could not get them both to come in on one line, so I put the restriction on sum_unit in the where statement, used that for the field "R". I now just want to slip in the value for "C" - I think I need an update statement? A subquery?

I tried a subquery but ended up getting multiple rows..not good.

Any ideas?


**********************************


SELECT
PLGR.PROJ_ELEM_ID,
PLGR.BID_CTGY,
' ' AS RES,
'N' AS OT,
decode (PLGR.PRD,
'10212' ,
'2002-03',
'10301' ,
'2002-04',
) AS PERIOD,
SUM_UNIT,
(nvl(PLGR.CUR_ACT ,
PLGR.PRD_ACT) ) AS R,
'' AS C
FROM
CSIOWNER.PLGR_ELK PLGR,
CSIOWNER.PELM PELM
WHERE
PLGR.PROJ_ELEM_ID = PELM.PROJ_ELEM_ID AND
PLGR.ORG_ID = PELM.ORG_ID AND
PLGR.ORG_ID = '001' AND
PELM.COST_MODE = 'D' AND
PLGR.BID_CTGY <> '**' AND
substr(PLGR.PROJ_ELEM_ID,1,4) = '1973' AND
PLGR.PRD = '10405' AND
PLGR.SUM_UNIT = 'R'
ORDER BY PROJ_ELEM_ID, BID_CTGY

LMC

 

[SantaMufasa]

LMC,

Happy to help...I'm just not sure yet what you want. Could you please post a sample of the current output plus a description of how you want it to change in appearance. Also, what do you mean by, "...It is not throwing..."?

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:30 (03Sep04) UTC (aka "GMT" and "Zulu"), 13:30 (03Sep04) Mountain Time)

 

[LMCRYER]

Mufasa - my bad...That "its not throwing' was a fragment leftover from when I hit ctrl-V, shouldnt have been there!

This is what I am doing right now:

SELECT
PLGR.PROJ_ELEM_ID,
PLGR.BID_CTGY,
' ' AS RES,
'N' AS OT,
(SUBSTR(PLGR.PRD,2,4)) as ERP_PRD,
decode (PLGR.PRD,
'10212' ,
'200203',
'10301' ,
'200204',
'10302' ,
'200205',
'10312' ,
'200303',
'10401' ,
'200304',
'10402' ,
'200305',
'10403' ,
'200306',
'10404' ,
'200307',
'10405' ,
'200308',
'10406' ,
'200309',
'10407' ,
'200310',
'10408' ,
'200311',
'10409' ,
'200312',
'10410' ,
'200401',
'10411' ,
'200402',
'10412' ,
'200403',
'10501' ,
'200404',
'10502' ,
'200405',
'10503' ,
'200406',
'10504' ,
'200407',
'10505' ,
'200408',
'10910',
'200901') AS MPM_PRD,
SUM_UNIT,
(nvl(PLGR.CUR_ACT ,
PLGR.PRD_ACT) ) AS AMT
FROM
CSIOWNER.PLGR_ELK PLGR,
CSIOWNER.PELM PELM
WHERE
PLGR.PROJ_ELEM_ID = PELM.PROJ_ELEM_ID AND
PLGR.ORG_ID = PELM.ORG_ID AND
PLGR.ORG_ID = '001' AND
PELM.COST_MODE = 'D' AND
PLGR.BID_CTGY <> '**' AND
substr(PLGR.PROJ_ELEM_ID,1,4) = '2984' AND
PLGR.PRD = '10405' ORDER BY PROJ_ELEM_ID, BID_CTGY



This brings me data which looks like this:
2984.11 1P N 2003-08 C 0.00
2984.11 1P N 2003-08 R 0.00
2984.11 2E N 2003-08 C 128.71
2984.11 2E N 2003-08 R 7.00
2984.11 2P N 2003-08 C 42.91
2984.11 2P N 2003-08 R 2.00
2984.11 2Q N 2003-08 C 0.00
2984.11 2Q N 2003-08 R 0.00
2984.11 2R N 2003-08 C 112.23
2984.11 2R N 2003-08 R 5.50
2984.11 3R N 2003-08 C 0.00
2984.11 3R N 2003-08 R 0.00


What I would like to see is this:
2984.11 1P N 2003-08 0.00 0.00
2984.11 2E N 2003-08 128.71 7.00
2984.11 2P N 2003-08 42.91 2.00
2984.11 2Q N 2003-08 0.00 0.00
2984.11 2R N 2003-08 112.23 5.50
2984.11 3R N 2003-08 0.00 0.00

I know it has something to do with grouping, if I could get that to work right (maybe?!?) but I dont want to have to group by all of the things in my SELECT because it seems to skew the numbers (or maybe I'm doing it wrong!!)

So, what I was going to do was take the first one, set it so all the SUM_UNIT = C and get the first dollar amt that is on there, then later on in the query do a subquery to gain the ones where it = R (which is the second field)....
IS that not the way to go?

LMC




LMC