10 Replies Latest reply on Apr 10, 2016 8:17 PM by Gerardo Varela

    I am new to Tableau 9.0  Given a assignment to recreate a legacy report connecting to Oracle.

    Anita Christy

      The report query has to return all accounts given a date range of when the Account was opened for

      1) a particular zipcode; if no zipcode is entered then to return all

      2) user has identified a group(MSA) of 50 zipcodes so needs the capability to retrieve all information for this particular group.

       

      I created the following parameters

      Parameters.P_ZIPCODE (String)

      Parameters.ZIP_CMD (String)

      Parameters.MSA (Boolean)

      Parameters.BEGIN_DATE and END DATE

       

      My Code is:

      SELECT

      VAD.ZIP_CODE,

      COUNT(DISTINCT VEA.ETC_ACCOUNT_ID) ACCOUNT_COUNT,

      COUNT(VED.DEVICE_NO) DEVICE_COUNT

      FROM

      VECTOR_READONLY.V_ETC_ACCOUNT VEA JOIN VECTOR_READONLY.V_ADDRESS VAD ON VEA.ETC_ACCOUNT_ID = VAD.ETC_ACCOUNT_ID

      JOIN VECTOR_READONLY.V_DEVICE VED ON VED.ETC_ACCOUNT_ID = VAD.ETC_ACCOUNT_ID

      WHERE

      (CASE WHEN <Parameters.P_ZIPCODE> IS NOT NULL THEN

                <Parameters.ZIP_CMD> := 'VAD.ZIP_CODE  IN(<Parameters.P_ZIPCODE>) AND';

                 (CASE WHEN <Parameters.MSA> IS TRUE THEN

                     <Parameters.ZIP_CMD> := 'VAD.ZIP_CODE  IN('1','2','3','4') AND';

                        END)

           END)

      <Parameters.ZIP_CMD>

      AND VEA.ACCOUNT_TYPE IN (1,3)

      AND VEA.ACCT_ACT_STATUS IN (3)

      AND ADDRESS_TYPE_CD = 'MAILING'

      AND VED.DEVICE_STATUS IN (3,5,6,7,8,9,15,19,20)--ACTIVE, RETURNED, DAMAGED, RETURNDEF,LOST, STOLEN, LOSTINMAIL,INACTIVE, DISPOSED

      AND VEA.OPEN_DATE BETWEEN <Parameters.BEGIN_DATE>  AND <Parameters.END_DATE>

      AND VAD.ZIP_CODE  IN(<Parameters.P_ZIPCODE>)

      GROUP BY

      VAD.ZIP_CODE

      ORDER BY

      1

       

      I am getting the following error:

      Oracle database error 905: ORA-00905: missing keyword

       

      Any help or advice would be most appreciated. 

      Thanks

       

      Anita