2 Replies Latest reply on Nov 21, 2012 9:07 AM by Robert Morton

    Teradata - SQL error - All expressions in a derived table must have a name

    Ben Porter

      Hello,

       

      I'm trying some custom SQL in my Teradata connection.  It works in the Teradata SQL assistant, however when I try it in Tableau I get the error referenced in the title.  I *think* that a derived error is due to an error in the FROM clause; however I can't figure out what's exactly wrong. 

       

      FYI - I tried the custom SQL without the group by clause at the end, still same error.

       

      sel

      EXTRACT(MONTH FROM b.date_application_received) || '_' || EXTRACT(YEAR FROM b.date_application_received),

      b.APPLICATION_STATUS, count(b.APPLICATION_ID)

       

      from pedw.vs_vstr_refrer_actvy c

      inner join ud215.cia_master b

      on (c.web_vstr_trkg_id = B.tracking_id)

       

       

      where c.web_actvy_dt between '2011-07-01' and '2012-06-30'

      and (

        c.extrnl_refrer_txt LIKE 'facebook.com' 

        OR c.extrnl_refrer_txt LIKE 'youtube.com'

        OR c.extrnl_refrer_txt LIKE 'linkedin.com'

        OR c.extrnl_refrer_txt LIKE 'delicious.com'

        OR c.extrnl_refrer_txt LIKE 't.co'

        OR c.extrnl_refrer_txt LIKE 'social.captl1.co'

        OR c.extrnl_refrer_txt LIKE 'twitter.com'

        OR c.extrnl_refrer_txt LIKE 'adf.ly'

        OR c.extrnl_refrer_txt LIKE 'stumbleupon.com'

        OR c.extrnl_refrer_txt LIKE 'captl1.co'

        OR c.extrnl_refrer_txt LIKE 'reddit.com'

        OR c.extrnl_refrer_txt LIKE 'pinterest.com'

        OR c.extrnl_refrer_txt LIKE 'bit.ly'

           OR c.extrnl_refrer_txt LIKE 'fb.me')

           AND (c.EXTRNL_CMPGN_ID LIKE '%_FB%' OR c.EXTRNL_CMPGN_ID LIKE '%_FAB%' OR c.EXTRNL_CMPGN_ID LIKE '%_FAC%' OR c.EXTRNL_CMPGN_ID LIKE '%_YT%' OR c.EXTRNL_CMPGN_ID LIKE '%_ SBCDSP%')

        AND c.dmn_cntry_cd LIKE 'USA'

       

      group 1,2

        • 1. Re: Teradata - SQL error - All expressions in a derived table must have a name
          Darin Coulter

          I'm not a Teradata user myself, but it seems like you're being forced to give an identifier name to your Date and Count columns.  So... I'm just spit balling here...

           

          EXTRACT(MONTH FROM b.date_application_received) || '_' || EXTRACT(YEAR FROM b.date_application_received)

          APPLICATION_DATE

          , b.APPLICATION_STATUS

          , count(b.APPLICATION_ID) APPLICATIONS

          from pedw.vs_vstr_refrer_actvy c

          inner join ud215.cia_master b

          on (c.web_vstr_trkg_id = B.tracking_id)

           

           

          where c.web_actvy_dt between '2011-07-01' and '2012-06-30'

          and (

            c.extrnl_refrer_txt LIKE 'facebook.com' 

            OR c.extrnl_refrer_txt LIKE 'youtube.com'

            OR c.extrnl_refrer_txt LIKE 'linkedin.com'

            OR c.extrnl_refrer_txt LIKE 'delicious.com'

            OR c.extrnl_refrer_txt LIKE 't.co'

            OR c.extrnl_refrer_txt LIKE 'social.captl1.co'

            OR c.extrnl_refrer_txt LIKE 'twitter.com'

            OR c.extrnl_refrer_txt LIKE 'adf.ly'

            OR c.extrnl_refrer_txt LIKE 'stumbleupon.com'

            OR c.extrnl_refrer_txt LIKE 'captl1.co'

            OR c.extrnl_refrer_txt LIKE 'reddit.com'

            OR c.extrnl_refrer_txt LIKE 'pinterest.com'

            OR c.extrnl_refrer_txt LIKE 'bit.ly'

               OR c.extrnl_refrer_txt LIKE 'fb.me')

               AND (c.EXTRNL_CMPGN_ID LIKE '%_FB%' OR c.EXTRNL_CMPGN_ID LIKE '%_FAB%' OR c.EXTRNL_CMPGN_ID LIKE '%_FAC%' OR c.EXTRNL_CMPGN_ID LIKE '%_YT%' OR c.EXTRNL_CMPGN_ID LIKE '%_ SBCDSP%')

            AND c.dmn_cntry_cd LIKE 'USA'

          GROUP BY

          EXTRACT(MONTH FROM b.date_application_received) || '_' || EXTRACT(YEAR FROM b.date_application_received)

          , b.APPLICATION_STATUS

          • 2. Re: Teradata - SQL error - All expressions in a derived table must have a name
            Robert Morton

            Darin is correct. It may be more clear to see those aliases with the optional "AS" clause present:

             

            sel
              EXTRACT(MONTH FROM b.date_application_received) || '_' || EXTRACT(YEAR FROM b.date_application_received)
                  AS "Application Received (Month/Year)",
              b.APPLICATION_STATUS AS "Application Status",
              count(b.APPLICATION_ID) AS "Application Count (by ID)" from pedw.vs_vstr_refrer_actvy c inner join ud215.cia_master b on (c.web_vstr_trkg_id = B.tracking_id) where c.web_actvy_dt between '2011-07-01' and '2012-06-30' and ( c.extrnl_refrer_txt LIKE 'facebook.com' OR c.extrnl_refrer_txt LIKE 'youtube.com' OR c.extrnl_refrer_txt LIKE 'linkedin.com' OR c.extrnl_refrer_txt LIKE 'delicious.com' OR c.extrnl_refrer_txt LIKE 't.co' OR c.extrnl_refrer_txt LIKE 'social.captl1.co' OR c.extrnl_refrer_txt LIKE 'twitter.com' OR c.extrnl_refrer_txt LIKE 'adf.ly' OR c.extrnl_refrer_txt LIKE 'stumbleupon.com' OR c.extrnl_refrer_txt LIKE 'captl1.co' OR c.extrnl_refrer_txt LIKE 'reddit.com' OR c.extrnl_refrer_txt LIKE 'pinterest.com' OR c.extrnl_refrer_txt LIKE 'bit.ly'     OR c.extrnl_refrer_txt LIKE 'fb.me')     AND (c.EXTRNL_CMPGN_ID LIKE '%_FB%' OR c.EXTRNL_CMPGN_ID LIKE '%_FAB%' OR c.EXTRNL_CMPGN_ID LIKE '%_FAC%' OR c.EXTRNL_CMPGN_ID LIKE '%_YT%' OR c.EXTRNL_CMPGN_ID LIKE '%_ SBCDSP%') AND c.dmn_cntry_cd LIKE 'USA' group 1,2