1 2 Previous Next 22 Replies Latest reply on Jun 6, 2013 1:59 PM by Robert Morton

    Custom SQL for Data Connection

    Ryan Johnson

      Hi, guys!  I'm trying to write some custom SQL for one of my data sources, but T is giving me a "table or view not found" error when I plug in the query.  I've attached the full query.  I ran into trouble when I copied/pasted the statement that begins with "where not exists".  I'm guessing it has something to do with one of the tables that I'm referring to (q_quote_links).  When I run it in our SQL tool it runs fine though.

       

      Any SQL wizards out there care to take a crack?  One case of virtual beer on me if you can help me troubleshoot it... 

       

      Thanks,

      Ryan

        • 1. Re: Custom SQL for Data Connection
          Michel Caissie

          Ryan,

           

          you could try replacing the 'not exist' with 'Q_QUOTES.quote_id NOT IN'

           

          WHERE Q_QUOTES.quote_id NOT IN

            (SELECT ql2.quote_id

                                  FROM q_quote_links ql2

                                  WHERE Q_QUOTES.quote_id = ql2.linked_quote_id

                                  )

           

           

          AND Q_QUOTES.quote_id NOT IN

                                  (select ql.quote_id

                                  FROM q_quote_links ql

                                  WHERE q_quotes.quote_id = ql.quote_id

                                  )

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: Custom SQL for Data Connection
            Ryan Johnson

            Thanks, Michel.  Still returns the "table or view does not exist error" though..

            • 3. Re: Custom SQL for Data Connection
              Michel Caissie

              What about a more simple,

               

              WHERE Q_QUOTES.quote_id NOT IN

                (SELECT ql2.quote_id   FROM q_quote_links ql2 )

               

              This way the subselect wont return  a null view , and the same logic will be apply

              • 4. Re: Custom SQL for Data Connection
                Ryan Johnson

                Same thing.  Could it be that it doesn't like the aliases or that I need to reference the schema or something like that?

                • 5. Re: Custom SQL for Data Connection
                  Michel Caissie

                  According to the error message "table or view not found",  i was thinking that maybe T could not handle  a subselect returning no rows. But maybe it can not found the table in the subselect. So yes it could be that it doesn't support the alias.My next test would be to try removing the ql2

                   

                  Replace

                  SELECT ql2.quote_id   FROM q_quote_links ql2


                  with

                  SELECT q_quote_links.quote_id   FROM q_quote_links


                  You could try first this single query to pinpoint where the problem is coming from.


                  On my side , i don't have problems in Tableau with alias in subqueries  but i only use mySql.


                  • 6. Re: Re: Custom SQL for Data Connection
                    Robin Kennedy

                    Think it could be this bit:

                     

                    AND not exists

                                            (select ql.quote_id

                                            FROM q_quote_links ql

                                            WHERE q_quotes.quote_id = ql.quote_id

                                                    )

                    AND q_quotes.project_total is not null

                     

                    The table q_quotes is not being referenced in that subquery...?

                    1 of 1 people found this helpful
                    • 7. Re: Custom SQL for Data Connection
                      Ryan Johnson

                      Ugh..  Still no luck.  I even went back to make sure the table name is indeed "q_quote_links".  Ready to take a hammer to this thing! 

                      • 8. Re: Custom SQL for Data Connection
                        Ryan Johnson

                        Thanks, Robin.  I only know a little SQL - how would you fix what you're pointing out?

                        • 9. Re: Custom SQL for Data Connection
                          Robert Morton

                          Hi Ryan,

                           

                          You should generally try to avoid correlated subqueries, which often lead to poor performance. The subqueries in your WHERE clause both reference a field in the outer query, so both subqueries will be evaluated repeatedly for each row of data from your outer query.

                           

                          Since you're asking for help with syntax errors, this gives me a chance to rewrite your query. Fundamentally, the syntax problem with your original query was likely caused by omitting the schema qualifier "MDLINX" in your FROM clause in both subqueries. Below is a new version which avoids subqueries and instead relies on JOIN logic to give the filtering semantics you want (i.e., exclude records which have a matching entry in the q_quote_links table). Note that the new subquery does not reference fields in the outer query, so it is not correlated. The join flag field is a constant which will be NULL due to the LEFT JOIN for any record which does not have a matching entry in the links table. The WHERE clause then keeps only the NULL values for the join flag.

                           

                          SELECT "Q_QUOTES"."QUOTE_ID" AS "QUOTE_ID",
                            "Q_QUOTES"."SURVEY_NAME" AS "SURVEY_NAME",
                            "Q_QUOTES"."SALES_ID" AS "SALES_ID",
                            "Q_QUOTES"."CLIENT_ID" AS "CLIENT_ID",
                            "Q_QUOTES"."CLIENT_NAME" AS "CLIENT_NAME",
                            "Q_QUOTES"."CONTACT_FNAME" AS "CONTACT_FNAME",
                            "Q_QUOTES"."CONTACT_LNAME" AS "CONTACT_LNAME",
                            "Q_QUOTES"."CONTACT_EMAIL" AS "CONTACT_EMAIL",
                            "Q_QUOTES"."CONTACT_FAX" AS "CONTACT_FAX",
                            "Q_QUOTES"."QUOTE_DT" AS "QUOTE_DT",
                            "Q_QUOTES"."SALESFORCE_ID" AS "SALESFORCE_ID",
                            "Q_QUOTES"."START_DT" AS "START_DT",
                            "Q_QUOTES"."END_DT" AS "END_DT",
                            "Q_QUOTES"."TOTAL_HONORARIA" AS "TOTAL_HONORARIA",
                            "Q_QUOTES"."TOTAL_COMPLETES" AS "TOTAL_COMPLETES",
                            "Q_QUOTES"."TOTAL_BEST_EFFORTS" AS "TOTAL_BEST_EFFORTS",
                            "Q_QUOTES"."TARGET_TOTALS" AS "TARGET_TOTALS",
                            "Q_QUOTES"."EST_PROFIT_CASH" AS "EST_PROFIT_CASH",
                            "Q_QUOTES"."EST_PROFIT_PERCENT" AS "EST_PROFIT_PERCENT",
                            "Q_QUOTES"."PROJECT_TOTAL" AS "PROJECT_TOTAL",
                            "Q_QUOTES"."ACTIVE_IND" AS "ACTIVE_IND",
                            "Q_QUOTES"."STATUS_ID" AS "STATUS_ID",
                            "Q_QUOTES"."CREATE_DT" AS "Q_QUOTES_CREATE_DT",
                            "Q_QUOTES"."UPDATE_DT" AS "UPDATE_DT",
                            "Q_QUOTES"."STAFF_ID" AS "STAFF_ID",
                            "Q_QUOTES"."CONTACT_PHONE" AS "CONTACT_PHONE",
                            "Q_QUOTES"."DAYS_IN_FIELD" AS "DAYS_IN_FIELD",
                            "Q_QUOTES"."QUOTE_STATUS_ID" AS "QUOTE_STATUS_ID",
                            "Q_QUOTES"."TOTAL_FEASIBLE" AS "TOTAL_FEASIBLE",
                            "Q_QUOTES"."COST_TOTALS" AS "COST_TOTALS",
                            "Q_QUOTES"."SURVEY_ID" AS "SURVEY_ID",
                            "Q_QUOTES"."REF_SURVEY_ID" AS "REF_SURVEY_ID",
                            "Q_QUOTES"."BACKLOG_NUM" AS "BACKLOG_NUM",
                            "Q_QUOTES"."INVOICE_NUMBER" AS "INVOICE_NUMBER",
                            "Q_QUOTES"."BILLING_STATUS_ID" AS "BILLING_STATUS_ID",
                            "Q_QUOTES"."INVOICE_DT" AS "INVOICE_DT",
                            "Q_QUOTES"."ADDRESS1" AS "ADDRESS1",
                            "Q_QUOTES"."ADDRESS2" AS "ADDRESS2",
                            "Q_QUOTES"."CITY" AS "CITY",
                            "Q_QUOTES"."STATE_CD" AS "STATE_CD",
                            "Q_QUOTES"."ZIP" AS "ZIP",
                            "Q_QUOTES"."COUNTRY" AS "COUNTRY",
                            "Q_QUOTES"."INVOICE_NUMBER_NEW" AS "INVOICE_NUMBER_NEW",
                            "Q_QUOTES"."PO_NUMBER" AS "PO_NUMBER",
                            "Q_QUOTES"."PAYMENT_DT" AS "PAYMENT_DT",
                            "Q_QUOTES"."PAYMENT_AMOUNT" AS "PAYMENT_AMOUNT",
                            "Q_QUOTES"."PARTNER_UPLOAD_INVOICE" AS "PARTNER_UPLOAD_INVOICE",
                            "Q_QUOTES"."PARTNER_UPLOAD_INVOICE_DT" AS "PARTNER_UPLOAD_INVOICE_DT",
                            "Q_QUOTES"."PARTNER_INVOICE_ARCHIVE_IND" AS "PARTNER_INVOICE_ARCHIVE_IND",
                            "Q_QUOTES"."IO_CREATE_DT" AS "IO_CREATE_DT",
                            "Q_QUOTES"."COMPANY_ENTERED_ID" AS "COMPANY_ENTERED_ID",
                            "Q_QUOTES"."PROJECT_CURRENCY" AS "PROJECT_CURRENCY",
                            "Q_QUOTES"."QUOTE_WON_DT" AS "QUOTE_WON_DT",
                            "Q_QUOTES"."SAGE_INVOICE_ID" AS "SAGE_INVOICE_ID",
                            "Q_QUOTES"."CLIENT_PROJECT_REF_ID" AS "CLIENT_PROJECT_REF_ID",
                            "Q_QUOTES"."QUOTE_TYPE" AS "QUOTE_TYPE",
                            "Q_QUOTES"."THERA" AS "THERA",
                            "Q_QUOTES"."SUBTHERA" AS "SUBTHERA",
                            "Q_QUOTES"."INIT_BIDDER" AS "INIT_BIDDER",
                            "Q_QUOTES"."FINAL_BIDDER" AS "FINAL_BIDDER",
                            "Q_QUOTES"."TO_COMPLETE_DT" AS "TO_COMPLETE_DT",
                            "Q_QUOTES"."REVIEW_IND" AS "REVIEW_IND",
                            "Q_QUOTES"."PERF_SURV_PM_NAME" AS "PERF_SURV_PM_NAME",
                            "Q_QUOTES"."PERF_SURV_PM_EMAIL" AS "PERF_SURV_PM_EMAIL",
                            "Q_QUOTE_STATUS"."QUOTE_STATUS_ID" AS "Q_QUOTE_STATUS_QUOTE_STATUS_ID",
                            "Q_QUOTE_STATUS"."QUOTE_STATUS_DESC" AS "QUOTE_STATUS_DESC",
                            "Q_REF_CLIENT"."CLIENT_ID" AS "Q_REF_CLIENT_CLIENT_ID",
                            "Q_REF_CLIENT"."NAME" AS "NAME",
                            "Q_REF_CLIENT"."CREATE_DT" AS "Q_REF_CLIENT_CREATE_DT",
                            "Q_REF_CLIENT"."LOGO_URL" AS "LOGO_URL",
                            "Q_REF_CLIENT"."ACTIVE_IND" AS "Q_REF_CLIENT_ACTIVE_IND",
                            "Q_REF_CLIENT"."RATE" AS "RATE",
                            "Q_REF_CLIENT"."ADDRESS1" AS "Q_REF_CLIENT_ADDRESS1",
                            "Q_REF_CLIENT"."ADDRESS2" AS "Q_REF_CLIENT_ADDRESS2",
                            "Q_REF_CLIENT"."CITY" AS "Q_REF_CLIENT_CITY",
                            "Q_REF_CLIENT"."STATE_CD" AS "Q_REF_CLIENT_STATE_CD",
                            "Q_REF_CLIENT"."ZIP" AS "Q_REF_CLIENT_ZIP",
                            "Q_REF_CLIENT"."COUNTRY" AS "Q_REF_CLIENT_COUNTRY",
                            "Q_REF_CLIENT"."PHONE" AS "PHONE",
                            "Q_REF_CLIENT"."FAX" AS "FAX",
                            "Q_REF_CLIENT"."CUSTOMER_ID" AS "CUSTOMER_ID",
                            "Q_REF_CLIENT"."EMS_ID" AS "EMS_ID",
                            "Q_REF_CLIENT"."PARTNER_ID" AS "PARTNER_ID",
                            "REF_SURVEY_SALESP"."SALESP_ID" AS "SALESP_ID",
                            "REF_SURVEY_SALESP"."SALESP_NAME" AS "SALESP_NAME",
                            "REF_SURVEY_SALESP"."SALESFORCE_EMAIL" AS "SALESFORCE_EMAIL",
                            "REF_SURVEY_SALESP"."SALESP_EMAIL" AS "SALESP_EMAIL",
                            "REF_SURVEY_SALESP"."SALESP_TITLE" AS "SALESP_TITLE",
                            "REF_SURVEY_SALESP"."COMMISSION_RATE" AS "COMMISSION_RATE",
                            "REF_SURVEY_SALESP"."PEACHTREE_NAME" AS "PEACHTREE_NAME",
                            "REF_SURVEY_SALESP"."SALESP_PHONE" AS "SALESP_PHONE",
                            "REF_SURVEY_SALESP"."SALESP_CELL" AS "SALESP_CELL",
                            "REF_SURVEY_SALESP"."SALESP_FAX" AS "SALESP_FAX",
                            "REF_SURVEY_SALESP"."SALESP_FNAME" AS "SALESP_FNAME",
                            "REF_SURVEY_SALESP"."SALESP_LNAME" AS "SALESP_LNAME",
                            "REF_SURVEY_SALESP"."SALESP_ACTIVE" AS "SALESP_ACTIVE",
                            "Q_QUOTE_THERA"."AREA_ID" AS "AREA_ID",
                            "Q_QUOTE_THERA"."THERA_AREA" AS "THERA_AREA",
                            "Q_QUOTE_THERA"."CREATE_DT" AS "Q_QUOTE_THERA_CREATE_DT",
                            "Q_QUOTE_SUBTHERA"."SUB_AREA_ID" AS "SUB_AREA_ID",
                            "Q_QUOTE_SUBTHERA"."MAIN_AREA_ID" AS "MAIN_AREA_ID",
                            "Q_QUOTE_SUBTHERA"."SUB_AREA" AS "SUB_AREA",
                            "Q_QUOTE_SUBTHERA"."CREATE_DT" AS "Q_QUOTE_SUBTHERA_CREATE_DT"
                          FROM "MDLINX"."Q_QUOTES" "Q_QUOTES"
                            INNER JOIN "MDLINX"."Q_QUOTE_STATUS" "Q_QUOTE_STATUS" ON ("Q_QUOTES"."QUOTE_STATUS_ID" = "Q_QUOTE_STATUS"."QUOTE_STATUS_ID")
                            INNER JOIN "MDLINX"."Q_REF_CLIENT" "Q_REF_CLIENT" ON ("Q_QUOTES"."CLIENT_ID" = "Q_REF_CLIENT"."CLIENT_ID")
                            INNER JOIN "MDLINX"."REF_SURVEY_SALESP" "REF_SURVEY_SALESP" ON ("Q_QUOTES"."SALES_ID" = "REF_SURVEY_SALESP"."SALESP_ID")
                            FULL JOIN "MDLINX"."Q_QUOTE_THERA" "Q_QUOTE_THERA" ON ("Q_QUOTES"."THERA" = "Q_QUOTE_THERA"."AREA_ID")
                            FULL JOIN "MDLINX"."Q_QUOTE_SUBTHERA" "Q_QUOTE_SUBTHERA" ON ("Q_QUOTES"."SUBTHERA" = "Q_QUOTE_SUBTHERA"."SUB_AREA_ID")
                            LEFT JOIN
                             (SELECT
                               quote_id,
                               linked_quote_id,
                               1 AS join_flag
                              FROM "MDLINX".q_quote_links
                              ) "subq_ql"
                             ON Q_QUOTES.quote_id = "subq_ql".linked_quote_id
                             OR Q_QUOTES.quote_id = "subq_ql".quote_id
                          WHERE
                          "subq_ql".join_flag IS NULL
                          AND q_quotes.project_total is not null
                          

                           

                          I hope this helps,

                          Robert

                          1 of 1 people found this helpful
                          • 10. Re: Custom SQL for Data Connection
                            Ryan Johnson

                            I plugged your query in, Robert.  I still get an error message, but now it says, "Oracle database error 904: ORA-00904: "subq_ql"."join_flag": invalid identifier".

                             

                            Is it bad that I'm excited to at least get a different error message?!?

                            • 11. Re: Re: Custom SQL for Data Connection
                              Toby Erkson

                              Try replacing the double-quotes with single ones.  Also try using the alias keyword "AS":

                                  FROM "MDLINX".q_quote_links
                                  ) AS 'subq_ql'
                              ...
                              WHERE
                              'subq_ql'.join_flag IS NULL
                               
                              1 of 1 people found this helpful
                              • 12. Re: Custom SQL for Data Connection
                                Ryan Johnson

                                So this instead?

                                 

                                LEFT JOIN

                                   (SELECT

                                     quote_id,

                                     linked_quote_id,

                                     1 AS join_flag

                                    FROM "MDLINX".q_quote_links

                                    ) AS 'subq_ql'

                                   ON Q_QUOTES.quote_id = "subq_ql".linked_quote_id

                                   OR Q_QUOTES.quote_id = "subq_ql".quote_id

                                WHERE

                                'subq_ql'.join_flag IS NULL

                                AND q_quotes.project_total is not null

                                • 13. Re: Custom SQL for Data Connection
                                  Ryan Johnson

                                  Used that and got:

                                  Oracle database error 905: ORA-00905: missing keyword

                                   

                                  Definitely feels like we're getting closer...!

                                  • 14. Re: Custom SQL for Data Connection
                                    Toby Erkson

                                    Robert "Salty Bob" Morton is more the expert than I so I'll default to his expertise, however, I would replace all double-quotes with single quotes for consistency -- some dbs do differentiate between the two quotes (DB2 comes to mind for me).  Using the AS keyword shouldn't hurt as it's a valid Oracle keyword.

                                    1 2 Previous Next