Post reply

CASE statement

  • 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

  • April 8, 2004 at 9:00 am

    #502165

    It works for me (sql2000 sp3).

    case approved when 1 then approvedvalue else unapprovedvalue end

  • April 8, 2004 at 9:10 am

    #502169

    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.

     

  • April 8, 2004 at 9:19 am

    #502172

    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

  • April 8, 2004 at 9:30 am

    #502175

    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))

  • April 8, 2004 at 11:46 am

    #502192

    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

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

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