CODEWITHSUNDEEP
sqlteradata
Voystin
Voystin

Reputation: 304

Object does not exist memo from sql query

I have this sql query:

Select top 10
sub_id1

FROM
(Select top 10
offer_loj_OD.subs_id as sub_id1,
offer_loj_OD.offer_id,
offer_loj_OD.subs_inst_offer_id,
offer_loj_OD.loj_od, offer_loj_OD.loj_do
FROM
(SELECT subs_id, offer_id,  subs_inst_offer_id, CAST(data_od AS DATE) AS loj_od
, CAST(data_do AS DATE) AS loj_do   , src_system_cd 
FROM
(SELECT subs_id,subs_inst_offer_id, ST_DT, end_dt, early_term_dt, offer_id, src_system_cd
,COALESCE(MIN(ST_DT) OVER (PARTITION BY subs_id
, subs_inst_offer_id ORDER BY ST_DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)    ,  ST_DT )  AS data_od
, CASE WHEN early_term_dt <=end_dt AND early_term_dt IS NOT NULL THEN early_term_dt ELSE end_dt END AS data_do
FROM vd08_offer.T08010_SUBS_INST_OFFER
) AS umowy
QUALIFY ROW_NUMBER() OVER (PARTITION BY subs_id, subs_inst_offer_id ORDER BY ST_DT DESC) = 1
) AS offer_loj_OD

WHERE 
loj_od between '2015-01-01' and '2015-12-31' 
AND offer_loj_OD.src_system_cd =1 

) as offer_loj_OD_FINAL

left join 


(SELECT top 10
offer_loj_do.subs_id  
, offer_loj_do.offer_id 
, offer_loj_do.subs_inst_offer_id
, offer_loj_do.loj_od, offer_loj_do.loj_do -- datay 

FROM
(SELECT subs_id, offer_id,  subs_inst_offer_id, CAST(data_od AS DATE) AS loj_od
, CAST(data_do AS DATE) AS loj_do   , src_system_cd 
FROM
(SELECT subs_id,subs_inst_offer_id, ST_DT, end_dt, early_term_dt, offer_id, src_system_cd
,COALESCE(MIN(ST_DT) OVER (PARTITION BY subs_id
, subs_inst_offer_id ORDER BY ST_DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)    ,  ST_DT )  AS data_od
, CASE WHEN early_term_dt <=end_dt AND early_term_dt IS NOT NULL THEN early_term_dt ELSE end_dt END AS data_do
FROM vd08_offer.T08010_SUBS_INST_OFFER
) AS umowy
QUALIFY ROW_NUMBER() OVER (PARTITION BY subs_id, subs_inst_offer_id ORDER BY ST_DT DESC) = 1
) AS offer_loj_do

WHERE 
loj_do between '2015-01-01' and '2015-12-31'
AND offer_loj_do.src_system_cd =1 
) as offer_loj_do_final on offer_loj_do.subs_id = offer_loj_OD.subs_id

When I'm trying to run it i receive error message: SELECT Failed. [3807]Object 'offer_loj_do' does not exist. Can you please tell me what I'm doing wrong here?

And maybe few words about query. I need to select data where agreement with client was going to be finished in 2015 (2nd part of query) but this client decided to sign new agreement (first part of query).

I hope you can help me.

Upvotes: 0

Views: 3266

Answers (1)

dnoeth
dnoeth

Reputation: 60482

The parser complains about the final ON where you try to join offer_loj_do and offer_loj_od, but only offer_loj_do_final and offer_loj_od_final are within the current scope. A valid join condition would be

 ON offer_loj_do_final.sub_id1 = offer_loj_od_final.subs_id

But this makes no sense, you join two Derived Tables with exactly the same Select.

And your formatting is horrible, this is how it looks after it passes a formatting tool :-)

SELECT
   TOP 10 sub_id1
FROM
   ( 
      SELECT
         TOP 10 offer_loj_od.subs_id AS sub_id1 
       , offer_loj_od.offer_id 
       , offer_loj_od.subs_inst_offer_id 
       , offer_loj_od.loj_od 
       , offer_loj_od.loj_do
      FROM
         ( 
            SELECT
               subs_id 
             , offer_id 
             , subs_inst_offer_id 
             , CAST(data_od AS DATE) AS loj_od 
             , CAST(data_do AS DATE) AS loj_do 
             , src_system_cd
            FROM
               ( 
                  SELECT
                     subs_id 
                   , subs_inst_offer_id 
                   , st_dt 
                   , end_dt 
                   , early_term_dt 
                   , offer_id 
                   , src_system_cd 
                   , COALESCE(MIN(st_dt) OVER (partition BY subs_id , subs_inst_offer_id order by st_dt ROWS BETWEEN 1 preceding AND 1 preceding) , st_dt ) AS data_od 
                   , CASE
                        WHEN early_term_dt            <=end_dt
                           AND early_term_dt is not null
                        THEN early_term_dt
                        ELSE end_dt
                     END AS data_do
                  FROM
                     vd08_offer.t08010_subs_inst_offer
               ) AS umowy 
            QUALIFY ROW_NUMBER() OVER (partition BY subs_id, subs_inst_offer_id order by st_dt DESC) = 1
         ) AS offer_loj_od
      WHERE loj_od BETWEEN '2015-01-01' AND '2015-12-31'
         AND offer_loj_od.src_system_cd =1
   ) AS offer_loj_od_final
LEFT JOIN
   ( 
      SELECT
         TOP 10 offer_loj_do.subs_id 
       , offer_loj_do.offer_id 
       , offer_loj_do.subs_inst_offer_id 
       , offer_loj_do.loj_od 
       , offer_loj_do.loj_do -- datay
      FROM
         ( 
            SELECT
               subs_id 
             , offer_id 
             , subs_inst_offer_id 
             , CAST(data_od AS DATE) AS loj_od 
             , CAST(data_do AS DATE) AS loj_do 
             , src_system_cd
            FROM
               ( 
                  SELECT
                     subs_id 
                   , subs_inst_offer_id 
                   , st_dt 
                   , end_dt 
                   , early_term_dt 
                   , offer_id 
                   , src_system_cd 
                   , COALESCE(MIN(st_dt) OVER (partition BY subs_id , subs_inst_offer_id order by st_dt ROWS BETWEEN 1 preceding AND 1 preceding) , st_dt ) AS data_od 
                   , CASE
                        WHEN early_term_dt            <=end_dt
                           AND early_term_dt is not null
                        THEN early_term_dt
                        ELSE end_dt
                     END AS data_do
                  FROM
                     vd08_offer.t08010_subs_inst_offer
               ) AS umowy 
            QUALIFY ROW_NUMBER() OVER (partition BY subs_id, subs_inst_offer_id order by st_dt DESC) = 1
         ) AS offer_loj_do
      WHERE loj_do BETWEEN '2015-01-01' AND '2015-12-31'
         AND offer_loj_do.src_system_cd =1
   ) AS offer_loj_do_final 
ON offer_loj_do.subs_id = offer_loj_od.subs_id

Upvotes: 1

Related Questions