CODEWITHSUNDEEP
sqlsql-servert-sql
Minty
Minty

Reputation: 25

SQL - Use a Column from a Sub Select Statement as a Query in Main Select Statement

Can you please have a look at the code and notice that I cannot use the "MYDELTOT" in the main Select Statement Query, either side of the "Union"

Code:

select 
    'POID' = DYN_PORDERS.ID, 'PSID' = SYS_SUPPLIERS.ID, 
    (select sum(DYN_PORDERDELS.DelQty) as MMTD 
     from DYN_PORDERDELS 
     where DYN_PORDERDELS.DelPOID = DYN_PORDERS.ID) as MYDELTOT,
    * 
from 
    DYN_PORDERSRS
inner join 
    DYN_PORDERS on DYN_PORDERS.id = DYN_PORDERSRS.RSOrderID
inner join 
    SYS_SUPPLIERS on SYS_SUPPLIERS.id = DYN_PORDERS.SupplierID
inner join 
    DYN_porderdels on DYN_PORDERS.ID = DYN_PORDERDELS.DelPOID
where 
    DYN_PORDERSRS.RSDate <= '20151031'
    and DYN_PORDERS.Qnty >= MYDELTOT

union

select 
    'POID' = DYN_PORDERS.ID, 'PSID' = SYS_SUPPLIERS.ID,
    (select sum(DYN_PORDERDELS.DelQty) as MMTD 
     from DYN_PORDERDELS 
     where DYN_PORDERDELS.DelPOID = DYN_PORDERS.ID) as MYDELTOT,
    * 
from 
    DYN_PORDERSRS
inner join 
    DYN_PORDERS on DYN_PORDERS.id = DYN_PORDERSRS.RSOrderID
inner join 
    SYS_SUPPLIERS on SYS_SUPPLIERS.id = DYN_PORDERS.SupplierID
inner join 
    DYN_porderdels on DYN_PORDERS.ID = DYN_PORDERDELS.DelPOID
where 
    DYN_PORDERSRS.rsdate  >= '20151101' 
    and DYN_PORDERS.Qnty >= MYDELTOT

Please help

Thanks Mike

Upvotes: 0

Views: 94

Answers (3)

Serpiton
Serpiton

Reputation: 3684

The aliases are applied after the WHERE condition, that mean that there is nothing that is called MYDELTOT when the condition is applied.

One way to address it is to create the alias in a subquery, with that the alias will be associated to the value at the subquery level

select 'POID' = DYN_PORDERS.ID
     , 'PSID' = SYS_SUPPLIERS.ID
     , t.MYDELTOT
     , * 
from   DYN_PORDERSRS
       inner join DYN_PORDERS on DYN_PORDERS.id = DYN_PORDERSRS.RSOrderID
       inner join SYS_SUPPLIERS on SYS_SUPPLIERS.id = DYN_PORDERS.SupplierID
       inner join DYN_porderdels on DYN_PORDERS.ID = DYN_PORDERDELS.DelPOID
       CROSS APPLY (select sum(DYN_PORDERDELS.DelQty) as MYDELTOT
                    from   DYN_PORDERDELS 
                    where  DYN_PORDERDELS.DelPOID = DYN_PORDERS.ID
                   ) as t
where  DYN_PORDERSRS.rsdate <= '20151031' 
  and  DYN_PORDERS.Qnty >= t.MYDELTOT

Upvotes: 0

AntDC
AntDC

Reputation: 1907

Your sql looks a bit funny but this should do it.....second one of these today

SELECT q.* FROM
(
  select 'POID' = DYN_PORDERS.ID, 'PSID' = SYS_SUPPLIERS.ID, DYN_PORDERS.Qnty,
  (select sum(DYN_PORDERDELS.DelQty) as MMTD from DYN_PORDERDELS where                           
  DYN_PORDERDELS.DelPOID = DYN_PORDERS.ID) as MYDELTOT,
  * from DYN_PORDERSRS
  inner join DYN_PORDERS on DYN_PORDERS.id=DYN_PORDERSRS.RSOrderID
  inner join SYS_SUPPLIERS on SYS_SUPPLIERS.id=DYN_PORDERS.SupplierID
  inner join DYN_porderdels on DYN_PORDERS.ID = DYN_PORDERDELS.DelPOID
  where DYN_PORDERSRS.RSDate  <=  '20151031'
  union
  select 'POID' = DYN_PORDERS.ID, 'PSID' = SYS_SUPPLIERS.ID, DYN_PORDERS.Qnty,
  (select sum(DYN_PORDERDELS.DelQty) as MMTD from DYN_PORDERDELS where                         
  DYN_PORDERDELS.DelPOID = DYN_PORDERS.ID) as MYDELTOT,
  * from DYN_PORDERSRS
  inner join DYN_PORDERS on DYN_PORDERS.id=DYN_PORDERSRS.RSOrderID
  inner join SYS_SUPPLIERS on SYS_SUPPLIERS.id=DYN_PORDERS.SupplierID
  inner join DYN_porderdels on DYN_PORDERS.ID = DYN_PORDERDELS.DelPOID
  where DYN_PORDERSRS.rsdate  >= '20151101' 
) q
where 
  AND q.Qnty >= q.MYDELTOT

Upvotes: 1

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

You can try to use CTE in following:

 ;with cte as (
    select 'POID' = DYN_PORDERS.ID, 'PSID' = SYS_SUPPLIERS.ID, 
    (select sum(DYN_PORDERDELS.DelQty) as MMTD from DYN_PORDERDELS where                           
    DYN_PORDERDELS.DelPOID = DYN_PORDERS.ID) as MYDELTOT,
    * from DYN_PORDERSRS
    inner join DYN_PORDERS on DYN_PORDERS.id=DYN_PORDERSRS.RSOrderID
    inner join SYS_SUPPLIERS on SYS_SUPPLIERS.id=DYN_PORDERS.SupplierID
    inner join DYN_porderdels on DYN_PORDERS.ID = DYN_PORDERDELS.DelPOID
    where DYN_PORDERSRS.RSDate  <=  '20151031'
    union
    select 'POID' = DYN_PORDERS.ID, 'PSID' = SYS_SUPPLIERS.ID,
    (select sum(DYN_PORDERDELS.DelQty) as MMTD from DYN_PORDERDELS where                         
    DYN_PORDERDELS.DelPOID = DYN_PORDERS.ID) as MYDELTOT,
    * from DYN_PORDERSRS
    inner join DYN_PORDERS on DYN_PORDERS.id=DYN_PORDERSRS.RSOrderID
    inner join SYS_SUPPLIERS on SYS_SUPPLIERS.id=DYN_PORDERS.SupplierID
    inner join DYN_porderdels on DYN_PORDERS.ID = DYN_PORDERDELS.DelPOID
    where DYN_PORDERSRS.rsdate  >= '20151101' 
)
select * 
from cte
where Qnty >= MYDELTOT

Upvotes: 2

Related Questions