CASE statement

Question

Post reply

CASE statement

Neil Munday

Old Hand

Points: 301
More actions
April 8, 2004 at 8:47 am

#107506

Hi,
I am building a view that links three tables.
One of these Tables has the following structure.
Approved - Bit
UnApprovedValue - int
ApprovedValue - int
In my View I need to Select all rows from this Table but only one of the Integer columns. The column to select is based on the 'Approved' flag. If 'Approved = TRUE' I need the 'ApprovedValue' other wise if False or Null I want the UnApprovedValue.
I thought the CASE statement would be good for this, however when I try and use it in the View Designer I get a message saying that the 'Query Designer does not support the Case statement'. Is there another command I can use?
Any help would be much appreciated.
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

wz700 SSCertifiable Points: 7133 More actions · Answer 1

It works for me (sql2000 sp3).

case approved when 1 then approvedvalue else unapprovedvalue end

Neil Munday Old Hand Points: 301 More actions · Answer 2

This is the code I am using...

case dbo.tcOrder.Approved when 1 then Adjusted_Number_Of_Days_Shipped_Early_Or_Late else Number_Of_Days_Shipped_Early_Or_Late end

Which column of the View Designer grid do I paste this into? I have tried the Citeria and the Column? Both give me the Case statement not supported error.

Pat6295 Mr or Mrs. 500 Points: 502 More actions · Answer 3

While the Case Statement is supported in Views, it is not supported in the View Designer grid and will need to be typed into the SQL statement directly.

Pat

Neil Munday Old Hand Points: 301 More actions · Answer 4

I have just tried to put it into the SQL code but the layout and results grid go grey when I run it an the error appears. This is my SQL code. The column is the last one 'TestCol'. Have I coded it wrong?

SELECT dbo.tcOrder.System_Code AS System, dbo.tcOrder.Order_Number, dbo.trTicketCenter.Ticket_Center_Name AS Production_Ticket_Center,

dbo.tcOrder.Customer_Purchase_Order_Number, dbo.tcOrder.Customer_Order_Reference_Number, dbo.tcOrder.Product_Number,

dbo.tcProduct.Product_Description, dbo.tcOrder.Product_Delivery_Lead_Time,

dbo.trProductionTeam.Production_Team_Description AS Production_Team, dbo.trRetailer.Retailer_Description AS Retailer,

dbo.trFamily.Family_Description AS Product_Family, dbo.tcProduct.Report_Group, dbo.trGlobalProductLine.Global_Product_Line,

dbo.tcProduct.Program_Name, dbo.trRegion.Region_Name AS Ship_To_Region, dbo.tcOrder.Deliver_To_Account_Number,

trRegion_Deliver_To.Region_Name AS Deliver_To_Region, dbo.tcOrder.Date_Received AS Order_Receive_Date,

dbo.tcOrder.Resolution_Date_Of_Last_Hold_Reason, dbo.tcOrder.Production_Ticket_Center_Received_Date, dbo.tcOrder.Date_Sent_To_Production,

dbo.tcOrder.Packed_Date, dbo.tcOrder.Shipment_Due_Date, dbo.tcOrder.Shipment_Date, dbo.tcOrder.Quantity_Shipped,

dbo.tcOrder.Order_Cost_Per1000, dbo.tcOrder.Order_Value_In_USD, dbo.trCurrency.Currency_Description AS Currency,

dbo.tcOrder.Turn_Time_Calculation_Start_Date, dbo.tcOrder.Number_Of_Days_Waiting_For_Production, dbo.tcOrder.Number_Of_Days_To_Produce,

dbo.tcOrder.Number_Of_Days_Waiting_For_Shipment, dbo.tcOrder.Number_Of_Days_In_Production, dbo.tcProductTicketCenter.Local_Product_Line,

dbo.tcOrder.WPR2_Version,

TestCol = (CASE dbo.tcOrder.Approved WHEN 1 THEN dbo.tcOrder.Adjusted_Number_Of_Days_Shipped_Early_Or_Late ELSE dbo.tcOrder.Number_Of_Days_Shipped_Early_Or_Late

END)

FROM dbo.trProductionTeam INNER JOIN

dbo.tcProductTicketCenter ON dbo.trProductionTeam.Production_Team_Code = dbo.tcProductTicketCenter.Production_Team_Code RIGHT OUTER JOIN

dbo.tcOrder INNER JOIN

dbo.trTicketCenter ON dbo.tcOrder.Production_Ticket_Center_Code = dbo.trTicketCenter.Ticket_Center_Code INNER JOIN

dbo.tcProduct ON dbo.tcOrder.Product_Number = dbo.tcProduct.Product_Number INNER JOIN

dbo.trRetailer ON dbo.tcProduct.Retailer_Code = dbo.trRetailer.Retailer_Code INNER JOIN

dbo.trFamily ON dbo.tcProduct.Family_Code = dbo.trFamily.Family_Code INNER JOIN

dbo.trGlobalProductLine ON dbo.tcProduct.Global_Product_Line_Code = dbo.trGlobalProductLine.Global_Product_Line_Code INNER JOIN

dbo.tcCustomerMaster tcCustomerMaster_Ship_To ON

dbo.tcOrder.Ship_To_Account_Number = tcCustomerMaster_Ship_To.Account_Number INNER JOIN

dbo.trRegion ON tcCustomerMaster_Ship_To.Region_Code = dbo.trRegion.Region_Code INNER JOIN

dbo.tcCustomerMaster tcCustomerMaster_Deliver_To ON

dbo.tcOrder.Deliver_To_Account_Number = tcCustomerMaster_Deliver_To.Account_Number INNER JOIN

dbo.trRegion trRegion_Deliver_To ON tcCustomerMaster_Deliver_To.Region_Code = trRegion_Deliver_To.Region_Code ON

dbo.tcProductTicketCenter.Ticket_Center_Code = dbo.tcOrder.Production_Ticket_Center_Code AND

dbo.tcProductTicketCenter.Product_Number = dbo.tcOrder.Product_Number LEFT OUTER JOIN

dbo.trCurrency ON dbo.tcOrder.Currency_Code = dbo.trCurrency.Currency_Code

WHERE (dbo.tcOrder.Shipment_Date >=

(SELECT [Data_Cube_Minimum_Shipment_Date]

FROM trWPR2Settings)) AND (dbo.tcOrder.Shipment_Date <=

(SELECT [Data_Cube_Maximum_Shipment_Date]

FROM trWPR2Settings))

Pat6295 Mr or Mrs. 500 Points: 502 More actions · Answer 5

What happens when you run the view? The two panes greying out and the message that it cannot display the view in the designer are normal.

Try putting a CREATE VIEW statement around it and execute it from Query Analyzer and then open the view.

Pat