OraFAQ Forum: SQL & PL/SQL » multiply two columns

Home » SQL & PL/SQL » SQL & PL/SQL » multiply two columns

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

multiply two columns [message #139699]

Thu, 29 September 2005 04:16

shatishr
Messages: 52
Registered: September 2005
Location: Shah Alam

Member

--------------------------------------------------------------------------------

INSERT INTO TRAP_RA_VCA_SC_W (VENDOR_ID, BATCH_NO, DATE_GENERATED, DATE_VALID_TO, STATUS, DATE_STATUS_CHANGE, DENOMINATION, AMOUNT, QUANTITY, NO_OF_TRANSACTIONS)

(SELECT DISTINCT PROD_ID, BATCHNR, DATE_GENERATED, DATE_VALID_TO, STATUS, DATE_LSTATE, VALUE,
(SELECT count(*) FROM TRAP_STAGE_VCA_SC_W a WHERE STATUS = '5' and a.prod_id=b.prod_id and a.batchnr=b.batchnr) , QUANTITY, (SELECT count(SERIAL_NUMBER) FROM TRAP_STAGE_VCA_SC_W a where a.prod_id=b.prod_id and a.batchnr=b.batchnr) FROM TRAP_STAGE_VCA_SC_W b)

------------------

i have the above script and i need to (multiply the bolded with the column value) but i cant do so..
(SELECT value * count(*) FROM TRAP_STAGE_VCA_SC_W a WHERE STATUS = '5' and a.prod_id=b.prod_id and a.batchnr=b.batchnr)

its should look something like this
anyone could help

thanks !

Report message to a moderator

Re: multiply two columns [message #139765 is a reply to message #139699]

Thu, 29 September 2005 09:02

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

I can't read your poorly formatted code, but this is what you want to do:

select a * b from
  (select value a, count(*) b
   from TRAP_STAGE_VCA_SC_W
   group by value)

Report message to a moderator

Re: multiply two columns [message #139866 is a reply to message #139765]

Thu, 29 September 2005 20:21

shatishr
Messages: 52
Registered: September 2005
Location: Shah Alam

Member

joy,
i know that the code is poorly formated since i am new to sql..
can u help me with the modification to make it more nice and efficient ??

ur assistance is appreciated..
(the actual table and also what

Report message to a moderator

Re: multiply two columns [message #139867 is a reply to message #139765]

Thu, 29 September 2005 20:22

shatishr
Messages: 52
Registered: September 2005
Location: Shah Alam

Member

Joy,
I know that the code is not up to the expectation but im new to sql..
maybe can u edit the code, so it will look better and more efficient..

i've even posted about this code in the previous thread somewhere 4-5 days back.. u can refer to the attachment on what I want the result to look like...

Ur assistance is appreciated..

Thanks

Report message to a moderator

Re: multiply two columns [message #139938 is a reply to message #139867]

Fri, 30 September 2005 02:24

bonker
Messages: 402
Registered: July 2005

Senior Member

you mean something like this

(SELECT DISTINCT PROD_ID, BATCHNR, DATE_GENERATED, DATE_VALID_TO, STATUS, DATE_LSTATE, VALUE,
(SELECT count(*) FROM TRAP_STAGE_VCA_SC_W a WHERE STATUS = '5' and a.prod_id=b.prod_id and a.batchnr=b.batchnr) ,
VALUE * (SELECT count(*) FROM TRAP_STAGE_VCA_SC_W a WHERE STATUS = '5' and a.prod_id=b.prod_id and a.batchnr=b.batchnr),
QUANTITY, (SELECT count(SERIAL_NUMBER) FROM TRAP_STAGE_VCA_SC_W a where a.prod_id=b.prod_id and a.batchnr=b.batchnr) FROM TRAP_STAGE_VCA_SC_W b)

Report message to a moderator

Previous Topic:	SQLPlus connect string
Next Topic:	to update a table having 40 lac rows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 27 17:18:04 CDT 2025