Enable Query parameter as Token to use it's value in Select Query

Post date: Nov 14, 2017 7:29:42 AM

Query tokens feature in eWorksheets allows user to replace the values in the model Query with the values selected in Query dialog, instead of appending to where condition.

Query in eDapter model:  Below query is configured with 2 tokens $APP_NAME$ and $APP_ID$.

                    SELECT T_GPS_APPLICATIONS.APPLICATION_ID,

                     T_GPS_APPLICATIONS.APPLICATION_NAME,

                          DECODE(T_GPS_APPLICATIONS.DESCRIPTION, NULL, '$APP_NAME$'),

                          T_GPS_APPLICATIONS.VISIBLE_FLAG,

                         CASE

                    WHEN T_GPS_APPLICATIONS.CYCLE_TIME_ENABLED_FLAG = 'N' 

                     THEN T_GPS_APPLICATIONS.CYCLE_TIME_ENABLED_FLAG || ' NOT NULL'

                  WHEN T_GPS_APPLICATIONS.CYCLE_TIME_ENABLED_FLAG IS NULL 

                     THEN '[0-Current]'

               END,

               CASE

                  WHEN T_GPS_APPLICATIONS.APPLICATION_ID < $APP_ID$ 

                    THEN T_GPS_APPLICATIONS.APPLICATION_ID * $APP_ID$

                  WHEN T_GPS_APPLICATIONS.APPLICATION_ID >= $APP_ID$ 

                    THEN T_GPS_APPLICATIONS.APPLICATION_ID / $APP_ID$

               END

         FROM STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS

    Note:  Tokens should be surrounded with '$' and it should match with Bean class variable.

Configuration : Select Query Token option under Query Info tab in Viewer Configuration Panel for the required fields.(Ex: APP_NAME and APP_ID)

Passing values from Query dialog:

Final Query :

SELECT T_GPS_APPLICATIONS.APPLICATION_ID,        

        T_GPS_APPLICATIONS.APPLICATION_NAME,     

        DECODE(T_GPS_APPLICATIONS.DESCRIPTION, NULL, 'Test App'),        

        T_GPS_APPLICATIONS.VISIBLE_FLAG,        

        CASE        

          WHEN T_GPS_APPLICATIONS.CYCLE_TIME_ENABLED_FLAG = 'N' 

            THEN  T_GPS_APPLICATIONS.CYCLE_TIME_ENABLED_FLAG || ' NOT NULL'        

          WHEN T_GPS_APPLICATIONS.CYCLE_TIME_ENABLED_FLAG IS NULL 

            THEN  '[0-Current]'       

        END,        

        CASE

          WHEN T_GPS_APPLICATIONS.APPLICATION_ID < 1000.0 

            THEN  T_GPS_APPLICATIONS.APPLICATION_ID * 1000.0        

          WHEN T_GPS_APPLICATIONS.APPLICATION_ID >= 1000.0

            THEN  T_GPS_APPLICATIONS.APPLICATION_ID / 1000.0        

        END 

   FROM STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS 

  WHERE T_GPS_APPLICATIONS.APPLICATION_ID < '500.0'