CODEWITHSUNDEEP
mysqlsqlpostgresql
Varun Krishnan
Varun Krishnan

Reputation: 15

One Query output execution in another query

Description

output value as radtera and i want to display radiance value and radtera value as output

I tried this query, It is showing so many values in radiance and it shows many null values in radtera, but i want only display one radiance value and one radtera value

SELECT radiance, (700+(radiance-r700))/((r740-r700)*40) as radtera
FROM (
  SELECT (MAX(reflectance)+MIN(reflectance))/2+MIN(reflectance) as radiance, 
         case when wavelength=700 then reflectance end as r700,
         case when wavelength=740 then reflectance end as r740
  FROM table_name
  WHERE wavelength between 650 and 800
  GROUP BY wavelength,reflectance
) AS SE_23693370

If i remove

GROUP BY wavelength,reflectance

this from query, it is showing error

Here is SQL fiddle.

I checked it, i dont know how it is displaying many values instead of one value show.. Anyone help me to correct mistakes please...

I tried using case statement in SQL fiddle.

select reip,700+(reip-r45)/(r72-r45)*40 as reipw
from ( 
  select (mx+mn))/2+mn as reip 
from ( 
  select case max(tert) as mx, 
  case min(tert) as mn 
  case when iner=44.5 then tert end as r45, 
  case when iner=72.1 then tert end as r72
  from  table_name
  where iner between 43 and 79)bar
)as SE_23693370

It shows *ERROR: subquery in FROM must have an alias: select reip,700+(reip-r45)/(r72-r45)40 as reipw from ( select (mx+mn))/2+mn as reip from ( select case max(tert) as mx, case min(tert) as mn case when iner=44.5 then tert end as r45, case when iner=72.1 then tert end as r72 from table_name where iner between 43 and 79) )as SE_23693370

Upvotes: 1

Views: 72

Answers (1)

eric.christensen
eric.christensen

Reputation: 3241

You could do this with the ROW_NUMBER function. It is available in SQL Server, Oracle, and PostgreSQL, amongst others.

select radiance, r700, r740,
  (700+(radiance-r700))/((r740-r700)*40) as radtera
from
(select t.reflectance as radiance, 
  t700.wavelength as r700, t740.wavelength as r740
from
  (select max(reflectance) as reflectance
  from table_name) t,
    (SELECT reflectance, wavelength,
      row_number() over (order by abs(wavelength-700)) as rn700
    FROM table_name
    WHERE wavelength between 650 and 800) as t700,
   (SELECT reflectance,wavelength,
      row_number() over (order by abs(wavelength-740)) as rn740
    FROM table_name
    WHERE wavelength between 650 and 800) as t740
  where t700.rn700=1 and t740.rn740=1) as di

Fiddle

Upvotes: 0

Related Questions