CODEWITHSUNDEEP
sqlsql-server-2008
stats101
stats101

Reputation: 1877

Multiple conditions on select query

I have a select statement and I wish to calculate the cubic volume based on other values within the table. However I want to check that neither pr.Length_mm or pr.Width_mm or pr.Height_mm are NULL prior. I've looked at CASE statements, however it only seems to evaluate one column at a time.

SELECT 
      sa.OrderName,
      sa.OrderType,
      pr.Volume_UOM
 ,pr.Length_mm*pr.Width_mm*pr.Height_mm AS Volume_Cubic
 ,pr.Length_mm*pr.Width_mm AS Volume_Floor
 ,pr.Length_mm
 ,pr.Height_mm
 ,pr.Width_mm
FROM CostToServe_MCB.staging.Sale sa
LEFT JOIN staging.Product pr
ON sa.ID = pr.ID

Upvotes: 1

Views: 111

Answers (3)

Jordan Kaye
Jordan Kaye

Reputation: 2887

SELECT pr.Volume_UOM
 ,pr.Length_mm*pr.Width_mm*pr.Height_mm AS Volume_Cubic
 ,pr.Length_mm*pr.Width_mm AS Volume_Floor
 ,pr.Length_mm
 ,pr.Height_mm
 ,pr.Width_mm
FROM CostToServe_MCB.staging.Sale sa
LEFT JOIN staging.Product pr
ON sa.ID = pr.ID
and pr.Length_mm is not null
and pr.Width_mm is not null
and pr.Height_mm is not null

Upvotes: 2

Michael Levy
Michael Levy

Reputation: 13287

where pr.Length_mm is not null 
  and pr.Width_mm is not null 
  and pr.Height_mm is not NULL

Upvotes: 0

roman
roman

Reputation: 117337

SELECT pr.Volume_UOM
 ,pr.Length_mm*pr.Width_mm*pr.Height_mm AS Volume_Cubic
 ,pr.Length_mm*pr.Width_mm AS Volume_Floor
 ,pr.Length_mm
 ,pr.Height_mm
 ,pr.Width_mm
FROM CostToServe_MCB.staging.Sale sa
    LEFT JOIN staging.Product pr ON sa.ID = pr.ID
where pr.Length_mm is not null and pr.Width_mm is not null and pr.Height_mm is not null

Upvotes: 2

Related Questions