5 Replies Latest reply: Dec 17, 2012 8:47 AM by Fernando Obara Suzuki RSS

    Translating a CASE statement from SQL into QV

      Hello. So I'm trying to convert a SQL script that I know works outside of QlikView, but I'm not understanding how to bring in the CASE statement that sits in the FROM part of the script. I keep getting syntax errors on reload. Some have suggested mapping but I really don't think I want it for this instance.

       

      Within the FROM portion of the SQL script a variable called 'Plan_Status' is created like below..

       

       

      FROM

        CPR.CASPR.Active_Project_Attributes INNER JOIN (

        SELECT PROJ_NO, cpr_program,

      'Plan_STATUS' =

      CASE

      WHEN (TRIM_Actual_Transport_Delivery_Date IS NULL) AND (TRIM_PlanOnAir_Target_Date >= CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) THEN 'Grey'

       

      I also don't think I need the inner join to pull what I want, but I do need that 'Plan_Status' variable field to be Red, Yellow, or Grey depending on the logic above.

        • Re: Translating a CASE statement from SQL into QV
          Fernando Obara Suzuki

          Can you post the complete select statement?

            • Re: Translating a CASE statement from SQL into QV

              It's ugly but ok.

               

              SELECT

                CPR.CASPR.Active_Project_Attributes.CPR_Region,

                CPR.CASPR.Active_Project_Attributes.CPR_Market,

                CPR.CASPR.Active_Project_Attributes.CPR_Program,

                CPR.CASPR.Active_Project_Attributes.PROJ_NO,

                CPR.CASPR.Active_Project_Attributes.TRIM_Plan_On_Air_Date,

                CPR.CASPR.Active_Project_Attributes.PLAN149,

                CPR.CASPR.Active_Project_Attributes.PLAN150,

                CPR.CASPR.Active_Project_Attributes.ACTUAL099,

                CPR.CASPR.Active_Project_Attributes.ACTUAL100,

                count(CPR.CASPR.Active_Project_Attributes.PROJ_NO),

                CPR.CASPR.Active_Project_Attributes.TRIM_Actual_Transport_Delivery_Date,

                CPR.CASPR.Active_Project_Attributes.TRIM_PlanOnAir_Target_Date,

                TransHealth_Status_DT.FCST_STATUS,

                CPR.CASPR.Active_Project_Attributes.TRIM_30days_before_PlanOnAir_Date,

                TransHealth_Status_DT.Fcst_Project_Due_Status,

                CPR.CASPR.Active_Project_Attributes.TRIM_FcstOnAir_Target_Date,

                CPR.CASPR.Active_Project_Attributes.TRIM_Fcst_On_Air_Date

              FROM

                CPR.CASPR.Active_Project_Attributes INNER JOIN (

                SELECT PROJ_NO, cpr_program,

              'Plan_STATUS' =

              CASE

              WHEN (TRIM_Actual_Transport_Delivery_Date IS NULL) AND (TRIM_PlanOnAir_Target_Date >= CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) THEN 'Grey'

              WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) and (TRIM_Actual_Transport_Delivery_Date <= TRIM_PlanOnAir_Target_Date)THEN 'Green'

              WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) and ((TRIM_Actual_Transport_Delivery_Date >= TRIM_PlanOnAir_Target_Date) AND (TRIM_Actual_Transport_Delivery_Date <= TRIM_30days_before_PlanOnAir_Date)) THEN 'Yellow'

              WHEN (TRIM_Actual_Transport_Delivery_Date is NULL) and (TRIM_PlanOnAir_Target_Date < CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) THEN 'Red'

              WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) and (TRIM_Actual_Transport_Delivery_Date > TRIM_30days_before_PlanOnAir_Date) THEN 'Red'

              ELSE 'Other'

              END,

              'Tranport_Status' =

              CASE

              WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) THEN 'Transport Delivered'

              WHEN ((CASPR.Active_Project_Attributes.TRIM_Actual_Submit_Order_Date IS NULL) And (TRIM_Actual_Transport_Delivery_Date IS NULL)) THEN 'Transport not Ord'

              WHEN ((CASPR.Active_Project_Attributes.TRIM_Actual_Submit_Order_Date IS NOT NULL) And (TRIM_Actual_Transport_Delivery_Date IS NULL)) THEN 'Transport Ord'

              ELSE 'Other'

              END,

              'Plan_Project_Due_Status' =

              CASE

              WHEN ((TRIM_Actual_Transport_Delivery_Date IS NULL) AND ( CASPR.Active_Project_Attributes.TRIM_PlanOnAir_Target_Date - CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)>60)) THEN 'Due after 60 days'

              WHEN (((TRIM_Actual_Transport_Delivery_Date IS NULL)) And ( CASPR.Active_Project_Attributes.TRIM_PlanOnAir_Target_Date - CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)<0)) THEN 'Past Due'

              WHEN ((TRIM_Actual_Transport_Delivery_Date IS NULL) AND ( CASPR.Active_Project_Attributes.TRIM_PlanOnAir_Target_Date - CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)<=60)) THEN 'Due next 60 days'

              ELSE 'Other'

              END

              ,'FCST_STATUS' =

              CASE

              WHEN (TRIM_Actual_Transport_Delivery_Date IS NULL) AND (TRIM_FcstOnAir_Target_Date >= CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) THEN 'Grey'

              WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) and (TRIM_Actual_Transport_Delivery_Date <= TRIM_FcstOnAir_Target_Date)THEN 'Green'

              WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) and ((TRIM_Actual_Transport_Delivery_Date >= TRIM_FcstOnAir_Target_Date) AND (TRIM_Actual_Transport_Delivery_Date <= (TRIM_Fcst_On_Air_Date-30))) THEN 'Yellow'

              WHEN (TRIM_Actual_Transport_Delivery_Date is NULL) and (TRIM_FcstOnAir_Target_Date < CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) THEN 'Red'

              WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) and (TRIM_Actual_Transport_Delivery_Date > (TRIM_Fcst_On_Air_Date-30)) THEN 'Red'

              ELSE 'Other'

              END

              ,'Fcst_Project_Due_Status' =

              CASE

              WHEN ((TRIM_Actual_Transport_Delivery_Date IS NULL) AND ( CASPR.Active_Project_Attributes.TRIM_FcstOnAir_Target_Date - CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)>60)) THEN 'Due after 60 days'

              WHEN (((TRIM_Actual_Transport_Delivery_Date IS NULL)) And ( CASPR.Active_Project_Attributes.TRIM_FcstOnAir_Target_Date - CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)<0)) THEN 'Past Due'

              WHEN ((TRIM_Actual_Transport_Delivery_Date IS NULL) AND ( CASPR.Active_Project_Attributes.TRIM_FcstOnAir_Target_Date - CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)<=60)) THEN 'Due next 60 days'

              ELSE 'Other'

              END

               

              FROM CPR.CASPR.Active_Project_Attributes

               

               

                )  TransHealth_Status_DT ON (CPR.CASPR.Active_Project_Attributes.PROJ_NO=TransHealth_Status_DT.PROJ_NO)

               

              WHERE

                (

                 CPR.CASPR.Active_Project_Attributes.CPR_Program  IN  ( '1st Carrier','2nd Carrier','3rd Carrier','4th Carrier','5th Carrier','6th Carrier','LTE','NSB'  )

                 AND

                 CPR.CASPR.Active_Project_Attributes.TRIM_Fcst_On_Air_Date  BETWEEN  '01/01/2012 00:0:0'  AND  '08/16/2012 00:0:0'

                )

              GROUP BY

                CPR.CASPR.Active_Project_Attributes.CPR_Region,

                CPR.CASPR.Active_Project_Attributes.CPR_Market,

                CPR.CASPR.Active_Project_Attributes.CPR_Program,

                CPR.CASPR.Active_Project_Attributes.PROJ_NO,

                CPR.CASPR.Active_Project_Attributes.TRIM_Plan_On_Air_Date,

                CPR.CASPR.Active_Project_Attributes.PLAN149,

                CPR.CASPR.Active_Project_Attributes.PLAN150,

                CPR.CASPR.Active_Project_Attributes.ACTUAL099,

                CPR.CASPR.Active_Project_Attributes.ACTUAL100,

                CPR.CASPR.Active_Project_Attributes.TRIM_Actual_Transport_Delivery_Date,

                CPR.CASPR.Active_Project_Attributes.TRIM_PlanOnAir_Target_Date,

                TransHealth_Status_DT.FCST_STATUS,

                CPR.CASPR.Active_Project_Attributes.TRIM_30days_before_PlanOnAir_Date,

                TransHealth_Status_DT.Fcst_Project_Due_Status,

                CPR.CASPR.Active_Project_Attributes.TRIM_FcstOnAir_Target_Date,

                CPR.CASPR.Active_Project_Attributes.TRIM_Fcst_On_Air_Date

            • Re: Translating a CASE statement from SQL into QV

              I get numerous syntax errors when moving a SQL statement over to QV.

              • Re: Translating a CASE statement from SQL into QV
                Miguel Angel Baeyens de Arce

                Hi,

                 

                Are you using ODBC or OLE DB? Which type of errors are you getting? Everything after the SQL or SELECT statement goes directly into the driver, then sent to the database. So there may be a syntax issue (i. e.: not accepted keyword) in either the driver or the RDBMS, as long as QlikView does not do anything in that part.

                 

                Hope that helps.

                 

                Miguel