I need a value in a field regardless of the true false statement.

Fordraiders
Fordraiders used Ask the Experts™
on
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..

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

Open in new window



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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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
Sam JacobsDirector of Technology Development, IPM

Commented:
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

Open in new window

Database Developer
Commented:
Rewrite as:
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

Open in new window

Fordraiders

Author

Commented:
THANKS

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial