5 Replies Latest reply on Feb 3, 2017 12:56 PM by Jamieson Christian

    Oracle database - identifier is too long - need help identifying...

    Alec Chen

      (pun intended)

       

      I don't understand why my code is throwing the "identifier too long" error, especially when it is ran in Oracle successfully. Below is my code, please help me identify which identifier is responsible for the code. Is the "sum" aggregation included in the 30 char max? Is it the store_num calculation?

       

      Also, a critique on my code would be great too. Thanks in advance.

       

       

      SELECT G.CARD_DESIGN_CODE, Y.*, Z.*
      FROM OPS$GC.GCDW_LU_CARD_DESIGN G
      INNER JOIN
             (SELECT X.CARD_DESIGN_ID, sum(X.ORIGINAL_ALLOCATION_QUANTITY), X.STORE_ID
                FROM 
                      (SELECT A.CARD_DESIGN_ID, B.ORIGINAL_ALLOCATION_QUANTITY, B.STORE_ID
                        FROM OPS$GC.GCDW_LU_CARD_INVENTORY_BATCH A
                        INNER JOIN OPS$GC.GCDW_STORE_INVENTORY_SUMMARY B
                        ON A.INVENTORY_BATCH_ID = B.INVENTORY_BATCH_ID
                        UNION ALL 
                        SELECT BO.CARD_DESIGN_ID, BO.ORIGINAL_ALLOCATION_QUANTITY, BO.STORE_ID
                        FROM BACK_OFFICE_ALLOCATION BO
                        ) X
              GROUP BY X.CARD_DESIGN_ID, X.STORE_ID) Y
      ON G.CARD_DESIGN_ID = Y.CARD_DESIGN_ID
      LEFT  JOIN 
              (SELECT D.ACQ_STRATEGY_CODE, SUM(F.NUM_CARD_ISSUE), FLOOR(F.STORE_DEPT_REGISTER_KEY/10000000) AS STORE_NUM
                FROM OPS$GC.FACT_GIFTCARD F
                INNER JOIN OPS$GC.DIM_GIFTCARD_ACCOUNTS D
                ON F.APPNUM_SKEY = D.APPNUM_SKEY
                GROUP BY FLOOR(F.STORE_DEPT_REGISTER_KEY/10000000), D.ACQ_STRATEGY_CODE) Z
      ON G.CARD_DESIGN_CODE = Z.ACQ_STRATEGY_CODE AND Y.STORE_ID = Z.STORE_NUM
      WHERE G.CARD_DESIGN_CODE IN ('X001', 'X002', 'X003', 'X004')
      ORDER BY Z.STORE_NUM;
      
        • 1. Re: Oracle database - identifier is too long - need help identifying...
          Jamieson Christian

          Alec,

           

          You're getting that error when you run the query in Tableau? If so, keep in mind that Tableau wraps custom queries, so what is actually sent to the DBMS will be different from your original query.

           

          I recommend you start the Tableau Performance Recorder, load the workbook (to trigger the query), then stop the recorder and use the resultant performance workbook to extract the actual SQL that Tableau generated. Copy and paste it to the SQL editor of your choice (like Toad or SQL Developer) and run it from there.

           

          If it errors out there, you will be able to get much more detailed information about exactly what part of the query is causing problems. It will likely be something in the wrapper code that Tableau generated. Hopefully you will be able to identify changes you can make to the original query to prevent the wrapper from triggering the error.

           

          Hope this helps.

          • 2. Re: Oracle database - identifier is too long - need help identifying...
            Alec Chen

            Thanks for the reply, Jamieson. I followed your steps and was able to get the performance workbook, but am not able to make sense of it. I have it attached, if you or anyone else has some time to take a look.

             

             

            Thanks -

            • 3. Re: Oracle database - identifier is too long - need help identifying...
              Jamieson Christian

              Alec,

               

              Thanks for posting. It looks like there are 3 queries that you will want to test. You can click on any of the "Executing Query" lines to see the query in the detail at the bottom of the dashboard. You will probably need to click on the query and copy/paste it to a text editor to see the whole thing.

               

               

              Once you have the query in a text editor, you will need to make the following modifications to test it in your SQL editor of choice:

               

              • Remove the line "Command" from the top.
              • Replace all pairs of quotation marks "" with a single quotation mark "
              • Remove the quotation mark at the beginning (right before the SELECT) and at the end (right after "Custom SQL Query")

               

              So, where your query starts out looking like this:

               

              Command
              "SELECT ""Custom SQL Query"".""ACQ_STRATEGY_CODE"" AS ""ACQ_STRATEGY_CODE"",
                ""Custom SQL Query"".""CARD_DESIGN_CODE"" AS ""CARD_DESIGN_CODE"",
                ""Custom SQL Query"".""CARD_DESIGN_ID"" AS ""CARD_DESIGN_ID"",
                1 AS ""Number of Records"",
                ""Custom SQL Query"".""STORE_ID"" AS ""STORE_ID"",
                ROUND(TRUNC(""Custom SQL Query"".""STORE_NUM""),0) AS ""STORE_NUM"",
                ""Custom SQL Query"".""SUM(F.NUM_CARD_ISSUE)"" AS ""SUM(F.NUM_CARD_ISSUE)"",
                ""Custom SQL Query"".""SUM(X.ORIGINAL_ALLOCATION_QUANTITY)"" AS ""SUM(X.ORIGINAL_ALLOCATION_QUAN""
              FROM (
                SELECT G.CARD_DESIGN_CODE, Y.*, Z.*
                FROM OPS$GC.GCDW_LU_CARD_DESIGN G
                INNER JOIN
                      (SELECT X.CARD_DESIGN_ID, sum(X.ORIGINAL_ALLOCATION_QUANTITY), X.STORE_ID
                          FROM
                                (SELECT A.CARD_DESIGN_ID, B.ORIGINAL_ALLOCATION_QUANTITY, B.STORE_ID
                                  FROM OPS$GC.GCDW_LU_CARD_INVENTORY_BATCH A
                                  INNER JOIN OPS$GC.GCDW_STORE_INVENTORY_SUMMARY B
                                  ON A.INVENTORY_BATCH_ID = B.INVENTORY_BATCH_ID
                                  UNION ALL
                                  SELECT BO.CARD_DESIGN_ID, BO.ORIGINAL_ALLOCATION_QUANTITY, BO.STORE_ID
                                  FROM BACK_OFFICE_ALLOCATION BO
                                  ) X
                        GROUP BY X.CARD_DESIGN_ID, X.STORE_ID) Y
                ON G.CARD_DESIGN_ID = Y.CARD_DESIGN_ID
                LEFT  JOIN
                        (SELECT D.ACQ_STRATEGY_CODE, SUM(F.NUM_CARD_ISSUE), FLOOR(F.STORE_DEPT_REGISTER_KEY/10000000) AS STORE_NUM
                          FROM OPS$GC.FACT_GIFTCARD F
                          INNER JOIN OPS$GC.DIM_GIFTCARD_ACCOUNTS D
                          ON F.APPNUM_SKEY = D.APPNUM_SKEY
                          GROUP BY FLOOR(F.STORE_DEPT_REGISTER_KEY/10000000), D.ACQ_STRATEGY_CODE) Z
                ON G.CARD_DESIGN_CODE = Z.ACQ_STRATEGY_CODE AND Y.STORE_ID = Z.STORE_NUM
                WHERE G.CARD_DESIGN_CODE IN ('FGC001', 'FGC002', 'FGC003', 'FGC004')
                ORDER BY Z.STORE_NUM
              ) ""Custom SQL Query"""
              

               

               

              After cleanup, it will look like this:

               

              SELECT "Custom SQL Query"."ACQ_STRATEGY_CODE" AS "ACQ_STRATEGY_CODE",
                "Custom SQL Query"."CARD_DESIGN_CODE" AS "CARD_DESIGN_CODE",
                "Custom SQL Query"."CARD_DESIGN_ID" AS "CARD_DESIGN_ID",
                1 AS "Number of Records",
                "Custom SQL Query"."STORE_ID" AS "STORE_ID",
                ROUND(TRUNC("Custom SQL Query"."STORE_NUM"),0) AS "STORE_NUM",
                "Custom SQL Query"."SUM(F.NUM_CARD_ISSUE)" AS "SUM(F.NUM_CARD_ISSUE)",
                "Custom SQL Query"."SUM(X.ORIGINAL_ALLOCATION_QUANTITY)" AS "SUM(X.ORIGINAL_ALLOCATION_QUAN"
              FROM (
                SELECT G.CARD_DESIGN_CODE, Y.*, Z.*
                FROM OPS$GC.GCDW_LU_CARD_DESIGN G
                INNER JOIN
                      (SELECT X.CARD_DESIGN_ID, sum(X.ORIGINAL_ALLOCATION_QUANTITY), X.STORE_ID
                          FROM
                                (SELECT A.CARD_DESIGN_ID, B.ORIGINAL_ALLOCATION_QUANTITY, B.STORE_ID
                                  FROM OPS$GC.GCDW_LU_CARD_INVENTORY_BATCH A
                                  INNER JOIN OPS$GC.GCDW_STORE_INVENTORY_SUMMARY B
                                  ON A.INVENTORY_BATCH_ID = B.INVENTORY_BATCH_ID
                                  UNION ALL
                                  SELECT BO.CARD_DESIGN_ID, BO.ORIGINAL_ALLOCATION_QUANTITY, BO.STORE_ID
                                  FROM BACK_OFFICE_ALLOCATION BO
                                  ) X
                        GROUP BY X.CARD_DESIGN_ID, X.STORE_ID) Y
                ON G.CARD_DESIGN_ID = Y.CARD_DESIGN_ID
                LEFT  JOIN
                        (SELECT D.ACQ_STRATEGY_CODE, SUM(F.NUM_CARD_ISSUE), FLOOR(F.STORE_DEPT_REGISTER_KEY/10000000) AS STORE_NUM
                          FROM OPS$GC.FACT_GIFTCARD F
                          INNER JOIN OPS$GC.DIM_GIFTCARD_ACCOUNTS D
                          ON F.APPNUM_SKEY = D.APPNUM_SKEY
                          GROUP BY FLOOR(F.STORE_DEPT_REGISTER_KEY/10000000), D.ACQ_STRATEGY_CODE) Z
                ON G.CARD_DESIGN_CODE = Z.ACQ_STRATEGY_CODE AND Y.STORE_ID = Z.STORE_NUM
                WHERE G.CARD_DESIGN_CODE IN ('FGC001', 'FGC002', 'FGC003', 'FGC004')
                ORDER BY Z.STORE_NUM
              ) "Custom SQL Query"
              

               

               

              You can immediately see that Tableau has taken your original custom query and wrapped it with another outer query that does additional Tableau stuff. This outer query frequently causes issues — the most common issue I experience is a precipitous drop in performance. A query that takes under a minute in the SQL editor can suddenly run 30 minutes in Tableau without showing any signs of stopping. I'm inclined to think it is this wrapper SQL that is causing your DBMS to complain, since your inner query doesn't trigger any errors.

               

              Plus each query into your SQL editor and see if you can get a more succinct error message, particularly which line it occurs on.

              • 4. Re: Oracle database - identifier is too long - need help identifying...
                Alec Chen

                Thanks for walking me through this Jamieson. I narrowed it down and it looks like the culprit is the alias from one of the aggregate functions:

                 

                "Custom SQL Query"."SUM(X.ORIGINAL_ALLOCATION_QUANTITY)" AS "SUM(X.ORIGINAL_ALLOCATION_QUAN"
                

                 

                Is there any way around this? The table resulting from the subquery that this code is on needs to be aggregated..

                 

                 

                EDIT: I used an alias in the subquery so that Tableau would reference the alias instead of creating its own (sorry, newbie here, should have thought of that earlier). Tested it, and voila it works!

                 

                Thanks Jamieson for the help!

                • 5. Re: Oracle database - identifier is too long - need help identifying...
                  Jamieson Christian

                  Alex,

                   

                  Glad you were able to find the source of the problem and the fix for it! Yes, anytime you do a calculation or aggregation in your SQL, it's good to always give it an alias so that it can be more succinctly referenced.

                   

                  Happy viz-ing!