Home » SQL & PL/SQL » SQL & PL/SQL » Need some arithmatic help for this one
Need some arithmatic help for this one [message #256293] Fri, 03 August 2007 14:06 Go to next message
cbriggs
Messages: 4
Registered: July 2007
Junior Member
I know this is lengthly but I can't help it. I am trying to use to columns that are calculated in the below sql to make another column(enteredvalue-avgenteredvalue/stddev as sigma) the code below works but if I put the bolded line in it says that stddev is and invalid identifier what am I doing wrong here?

select enteredvalue,  (select avg(enteredvalue)
from sdidataitem sd, s_sample s1, s_sample s2, u_melt m1, u_melt m2
where s1.s_sampleid=sd.keyid1
and sd.sdcid='Sample'
and s1.sampletypeid='BEG'
and s1.samplestatus in ('Approved','Reported','Completed')
and sd.paramid='Ag'
and sd.paramtype='Standard'
and sd.enteredvalue is not null
and s1.s_sampleid <> s2.s_sampleid
and s2.s_sampleid = 'S-05-001228'
and s1.u_meltid = m1.u_meltid
and s2.u_meltid = m2.u_meltid
and m1.u_alloy_codeid = m2.u_alloy_codeid
and m1.u_calc_no < m2.u_calc_no)as avgenteredvalue,
(select stddev_samp(enteredvalue) 
		 from (select enteredvalue, 
					  row_number() over (order by m1.u_calc_no desc) AS rowcount
             	from sdidataitem sd, s_sample s1, s_sample s2, u_melt m1, u_melt m2
                where s1.s_sampleid=sd.keyid1
                and sd.sdcid='Sample'
                and s1.sampletypeid='BEG'
                and s1.samplestatus in ('Approved','Reported','Completed')
                and sd.paramid='Ag'
                and sd.paramtype='Standard'
                and sd.enteredvalue is not null
                and s1.s_sampleid <> s2.s_sampleid
                and s2.s_sampleid = 'S-05-001228'
                and s1.u_meltid = m1.u_meltid
                and s2.u_meltid = m2.u_meltid
                and m1.u_alloy_codeid = m2.u_alloy_codeid
                and m1.u_calc_no < m2.u_calc_no
		)where rowcount<=50) as stddev, 	 
from sdidataitem
where keyid1='S-05-001228'
and paramid='Ag'
and Paramtype='Standard'
Re: Need some arithmatic help for this one [message #256307 is a reply to message #256293] Fri, 03 August 2007 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put your expressions in the from clause then you can use it the select.
Or better, use the with clause to calculate the expressions.

Regards
Michel
Re: Need some arithmatic help for this one [message #256308 is a reply to message #256293] Fri, 03 August 2007 14:45 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Try the following. Column alias can NOT be used for formulas.


select enteredvale,avgenteredvalue,stddev,(enteredvalue-(avgenteredvalue/stddev)) as sigma
from
(select enteredvalue,  
       (select avg(enteredvalue)
        from sdidataitem sd, s_sample s1, s_sample s2, u_melt m1, u_melt m2
        where s1.s_sampleid=sd.keyid1
        and sd.sdcid='Sample'
        and s1.sampletypeid='BEG'
        and s1.samplestatus in ('Approved','Reported','Completed')
        and sd.paramid='Ag'
        and sd.paramtype='Standard'
        and sd.enteredvalue is not null
        and s1.s_sampleid <> s2.s_sampleid
        and s2.s_sampleid = 'S-05-001228'
        and s1.u_meltid = m1.u_meltid
        and s2.u_meltid = m2.u_meltid
        and m1.u_alloy_codeid = m2.u_alloy_codeid
        and m1.u_calc_no < m2.u_calc_no)as avgenteredvalue,
       (select stddev_samp(enteredvalue) 
	  from (select enteredvalue, 
              row_number() over (order by m1.u_calc_no desc) AS rowcount
              from sdidataitem sd, s_sample s1, s_sample s2, u_melt m1, u_melt m2
              where s1.s_sampleid=sd.keyid1
              and sd.sdcid='Sample'
              and s1.sampletypeid='BEG'
              and s1.samplestatus in ('Approved','Reported','Completed')
              and sd.paramid='Ag'
              and sd.paramtype='Standard'
              and sd.enteredvalue is not null
              and s1.s_sampleid <> s2.s_sampleid
              and s2.s_sampleid = 'S-05-001228'
              and s1.u_meltid = m1.u_meltid
              and s2.u_meltid = m2.u_meltid
              and m1.u_alloy_codeid = m2.u_alloy_codeid
              and m1.u_calc_no < m2.u_calc_no)
        where rowcount<=50) as stddev
from sdidataitem
where keyid1='S-05-001228'
and paramid='Ag'
and Paramtype='Standard');

[Updated on: Fri, 03 August 2007 14:45]

Report message to a moderator

Re: Need some arithmatic help for this one [message #257869 is a reply to message #256293] Thu, 09 August 2007 07:03 Go to previous message
cbriggs
Messages: 4
Registered: July 2007
Junior Member
Great thanks for the help Bill its working great now.
Previous Topic: Showing Databases in Server Machine
Next Topic: disadvantage of package in pl/sql
Goto Forum:
  


Current Time: Sun Dec 04 20:44:38 CST 2016

Total time taken to generate the page: 0.07388 seconds