multiple join in sub quary

Analytics
Fan

multiple join in sub quary

I am trying to join the following table in the sub query, but I am getting the following error message

expected something between cus_ian_id_no and ')'

Could somebody help me in this please?


SELECT

MIN(trs_dt)

,MAX(trs_dt)

    ,COUNT(DISTINCT a.cus_ian_id_no) Transactions

    ,SUM(ZEROIFNULL(CASE WHEN b.trs_ln_no IS NOT NULL THEN pos_sls_trs_am + md_am END)) (TITLE 'Coupon Net Sales')

  ,c.SUM(pos_sls_trs_am + tot_ln_md_am) (TITLE 'Total Net Sales')

  ,c.DistrictName

,c.State

,c.RegionName

FROM  

(

SELECT

a.trs_dt

        ,a.cus_ian_id_no

        ,a.trs_ln_no

        ,a.pos_sls_trs_am

        ,a.tot_ln_md_am

        ,DistrictName

   ,State

   ,RegionName

       

    FROM

    a

        INNER JOIN

        (

        SELECT

    cus_ian_id_no

        FROM

          b

        WHERE

        md_cpn_no IN ('28448')

       

        AND trs_dt> '2013-08-31'

        GROUP BY 1

) b

        ON a.cus_ian_id_no = b.cus_ian_id_no

  ) a

LEFT OUTER JOIN

            (

            SELECT

            cus_ian_id_no

            ,trs_ln_no

            ,SUM(md_am) md_am

          FROM  

             b

          WHERE

          md_cpn_no IN ('28448')

       

         AND trs_dt> '2013-08-31'

    GROUP BY 1,2

            ) b

   ON a.trs_ln_no = b.trs_ln_no 

AND  a.cus_ian_id_no = b.cus_ian_id_no

  )  c

INNER JOIN 

     (

     SELECT 

     DistrictName

     ,State

     ,RegionName

FROM 

 c


ON CAST(a.fty_id_no AS INTEGER) = c.StoreId 

) d   

GROUP BY  6,7,8;

--ORDER BY 1;

4 REPLIES
Senior Apprentice

Re: multiple join in sub quary

Seems you did some modifications of Derived Tables resulting in too many ')', easy to spot when you format the SQL:

SELECT
MIN(trs_dt)
,MAX(trs_dt)
,COUNT(DISTINCT a.cus_ian_id_no) Transactions
,SUM(ZEROIFNULL (
CASE
WHEN b.trs_ln_no IS NOT NULL
THEN pos_sls_trs_am + md_am
END)) (TITLE 'Coupon Net Sales')
,SUM(pos_sls_trs_am + tot_ln_md_am) (TITLE 'Total Net Sales') -- #1 not "c.SUM"
,c.DistrictName
,c.State
,c.RegionName
FROM
(
SELECT
a.trs_dt
,a.cus_ian_id_no
,a.trs_ln_no
,a.pos_sls_trs_am
,a.tot_ln_md_am
,DistrictName
,State
,RegionName
FROM
a
INNER JOIN
(
SELECT
cus_ian_id_no
FROM
b
WHERE
md_cpn_no IN ('28448')
AND trs_dt> '2013-08-31'
GROUP BY
1
) b
ON
a.cus_ian_id_no = b.cus_ian_id_no
) a
LEFT OUTER JOIN
(
SELECT
cus_ian_id_no
,trs_ln_no
,SUM(md_am) md_am
FROM
b
WHERE
md_cpn_no IN ('28448')
AND trs_dt> '2013-08-31'
GROUP BY
1
,2
) b
ON
a.trs_ln_no = b.trs_ln_no
AND a.cus_ian_id_no = b.cus_ian_id_no
-- ) c -- #2
INNER JOIN
( SELECT DistrictName ,State ,RegionName FROM c
) AS c -- #3, this was missing
ON
CAST(a.fty_id_no AS INTEGER) = c.StoreId
--) d -- #4
GROUP BY
6
,7
,8;
Fan

Re: multiple join in sub quary

thank you so much for your help. Appreciate your time.

When I tried to run it, got a message

expected something between the word cus_ian_id_no and ')' in line 60.

Senior Apprentice

Re: multiple join in sub quary

When i try to explain my query it fails with a "table not found", which means it's syntactically correct.

Btw, line 60 is commented out in my query, did you run this or your original one?

Fan

Re: multiple join in sub quary

I ran this after removing "comented out."