Rajarshi Bhadra Rajarshi Bhadra - 7 months ago 8
SQL Question

error while joining tables using nesting in SQL

My query looks like this

SELECT level1_idnt, day_dt, avg(sls_price) as avg_rp
FROM (SELECT * FROM (SELECT DISTINCT level1_idnt,day_dt,sls_price
FROM (SELECT a.level1_idnt,a.day_dt,a.sls_price,b.op
FROM (SELECT level1_idnt,day_dt,sls_price
FROM md1.loc_sku_dy_act_pos_full_v2
WHERE seasn_cd =2 and day_dt >= '2015-03-01 00:00:00' and day_dt <= '2015-09-30 00:00:00'
)l0
a left join (SELECT level1_idnt, AVG(org_sales_price) as op
FROM (SELECT DISTINCT level1_idnt, org_sales_price
FROM md1.item_lv1_org_price_m
WHERE fr_cntry_cde = '01' AND org_sales_price IS NOT NULL
) l
GROUP BY level1_idnt)l_op
b
ON a.level1_idnt = b.level1_idnt
)l1
WHERE sls_price <= op
)l2
WHERE sls_price is not null
) l3
GROUP BY level1_idnt,day_dt;


for this I am getting an error

syntax error at or near "a"


Any help on this will be greatly appreciated

Answer

Check this;) it looks like a alias mistake in subquery.

SELECT level1_idnt, day_dt, avg(sls_price) as avg_rp
FROM (SELECT * FROM (SELECT DISTINCT level1_idnt,day_dt,sls_price
                     FROM (SELECT a.level1_idnt,a.day_dt,a.sls_price,b.op
                           FROM (SELECT level1_idnt,day_dt,sls_price
                                 FROM md1.loc_sku_dy_act_pos_full_v2
                                 WHERE seasn_cd =2 and day_dt >= '2015-03-01 00:00:00' and day_dt <= '2015-09-30 00:00:00'
                                )
                          a left join (SELECT level1_idnt, AVG(org_sales_price) as op
                                       FROM (SELECT DISTINCT level1_idnt, org_sales_price
                                             FROM md1.item_lv1_org_price_m
                                             WHERE fr_cntry_cde = '01' AND org_sales_price IS NOT NULL
                                            ) l
                                       GROUP BY level1_idnt)
                    b
ON a.level1_idnt = b.level1_idnt
)l1
WHERE sls_price <= op
)l2
WHERE sls_price is not null
) l3
GROUP BY level1_idnt,day_dt;

Before a there is a string l0 and before b a string l_op

Comments