I need a value in a field regardless of the true false statement.
I need a value in a field regardless of the true false statement.
This is telling me i have the field "Expected_Value" listed to many times..
so even if the First CASE is false i still need the ELSE part to populate with '$50,000'
etc...with the others
Thanks
fordraiders
This is telling me i have the field "Expected_Value" listed to many times..
CASE WHEN( MCD.Freight_GIS = 'Prepaid on A.com Orders (PPG)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 50000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$50,000' ELSE '$50,000' END AS Expected_Volume,
CASE WHEN( MCD.Freight_GIS = 'Prepaid (PPD)' AND mcd.contract_number like'%NCP%' AND (Sum_Of_Sales_Current < 250000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$250,000' ELSE '$50,000' END AS Expected_Volume,
CASE WHEN( MCD.Freight_GIS = 'Prepaid Parcel (PPP)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 50000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$50,000' ELSE '$50,000' END AS Expected_Volume,
CASE WHEN( MCD.Freight_GIS = 'Prepaid Parcel on G.com Orders (PPPG)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 100000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$100,000' ELSE '$50,000' END AS Expected_Volume,
CASE WHEN( MCD.Freight_GIS = 'Standard (PPA)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 50000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$50,000' ELSE '$50,000' END AS Expected_Volume
so even if the First CASE is false i still need the ELSE part to populate with '$50,000'
etc...with the others
Thanks
fordraiders
You just can't use the exact same column name over and over:
CASE WHEN( MCD.Freight_GIS = 'Prepaid on A.com Orders (PPG)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 50000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$50,000' ELSE '$50,000' END AS Expected_Volume_PPG,
CASE WHEN( MCD.Freight_GIS = 'Prepaid (PPD)' AND mcd.contract_number like'%NCP%' AND (Sum_Of_Sales_Current < 250000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$250,000' ELSE '$50,000' END AS Expected_Volume_PPD,
CASE WHEN( MCD.Freight_GIS = 'Prepaid Parcel (PPP)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 50000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$50,000' ELSE '$50,000' END AS Expected_Volume_PPP,
CASE WHEN( MCD.Freight_GIS = 'Prepaid Parcel on G.com Orders (PPPG)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 100000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$100,000' ELSE '$50,000' END AS Expected_Volume_PPPG,
CASE WHEN( MCD.Freight_GIS = 'Standard (PPA)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 50000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$50,000' ELSE '$50,000' END AS Expected_Volume_PPA
CASE WHEN( MCD.Freight_GIS = 'Prepaid on A.com Orders (PPG)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 50000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$50,000' ELSE '$50,000' END AS Expected_Volume_PPG,
CASE WHEN( MCD.Freight_GIS = 'Prepaid (PPD)' AND mcd.contract_number like'%NCP%' AND (Sum_Of_Sales_Current < 250000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$250,000' ELSE '$50,000' END AS Expected_Volume_PPD,
CASE WHEN( MCD.Freight_GIS = 'Prepaid Parcel (PPP)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 50000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$50,000' ELSE '$50,000' END AS Expected_Volume_PPP,
CASE WHEN( MCD.Freight_GIS = 'Prepaid Parcel on G.com Orders (PPPG)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 100000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$100,000' ELSE '$50,000' END AS Expected_Volume_PPPG,
CASE WHEN( MCD.Freight_GIS = 'Standard (PPA)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 50000 OR Sum_Of_Sales_Current IS NULL ) )THEN '$50,000' ELSE '$50,000' END AS Expected_Volume_PPA
Citrix Technology Professional / Director of TechDev Services, IPM
CERTIFIED EXPERT
Your CASE statement should be:
CASE WHEN( MCD.Freight_GIS = 'Prepaid on A.com Orders (PPG)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 50000 OR Sum_Of_Sales_Current IS NULL ) ) THEN '$50,000'
WHEN( MCD.Freight_GIS = 'Prepaid (PPD)' AND mcd.contract_number like'%NCP%' AND (Sum_Of_Sales_Current < 250000 OR Sum_Of_Sales_Current IS NULL ) ) THEN '$250,000'
WHEN( MCD.Freight_GIS = 'Prepaid Parcel (PPP)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 50000 OR Sum_Of_Sales_Current IS NULL ) ) THEN '$50,000'
WHEN( MCD.Freight_GIS = 'Prepaid Parcel on G.com Orders (PPPG)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 100000 OR Sum_Of_Sales_Current IS NULL ) ) THEN '$100,000'
WHEN( MCD.Freight_GIS = 'Standard (PPA)' AND mcd.contract_number LIKE '%NCP%' AND (Sum_Of_Sales_Current < 50000 OR Sum_Of_Sales_Current IS NULL ) ) THEN '$50,000'
ELSE '$50,000' END AS Expected_Volume
