CODEWITHSUNDEEP
sqloracle-databasejoinsubquery
Michael Li
Michael Li

Reputation: 667

Oracle SQL join subquery dblink

Subquery join return error, could not found subquery table. Appreciate any helps!

I have two tables. One is normal one. ops_owner.track_mail_item. Another one is dblink (not sure how that works, but I run following it works

SELECT TRUNC(event_date,'HH24'),
        tmi_uid
    FROM TRACK_OWNER.TRACKING_DETAIL@dblink_track 
    WHERE event_id ='158'
    AND event_date BETWEEN to_date('04/01/2017 12:00:01 AM', 'MM/DD/YYYY HH12:MI:SS AM') AND to_date('04/30/2017 11:59:59 PM', 'MM/DD/YYYY HH12:MI:SS PM')

since the dblink table have multiple column as key (tmi_uid and event_id). I want to run subquery to make tmi_uid as key so I can use that later. However, when I run following the error is

ORA-00904: "M"."EVENT_DATE": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action: Error at Line: 21 Column: 6"

SELECT  i.originfacilitycode,
      i.zip5,
      TRUNC(i.receivedate,'HH24'),
      TRUNC(m.event_date,'HH24'),
      COUNT(i.tmi_uid)
FROM ops_owner.track_mail_item i
inner join (SELECT TRUNC(event_date,'HH24'),
    tmi_uid
FROM TRACK_OWNER.TRACKING_DETAIL@dblink_track 
WHERE event_id ='158'
   AND event_date BETWEEN to_date('04/01/2017 12:00:01 AM', 'MM/DD/YYYY HH12:MI:SS AM') AND to_date('04/30/2017 11:59:59 PM', 'MM/DD/YYYY HH12:MI:SS PM')
   ) m
   ON i.tmi_uid        = m.tmi_uid
   WHERE I.PRODUCT_ID IN (81, 82, 83, 36)
   AND I.RECEIVEDATE BETWEEN to_date('04/01/2017 00:00:01', 'MM/DD/YYYY HH24:MI:SS') AND to_date('04/30/2017 23:59:59', 'MM/DD/YYYY HH24:MI:SS')
   AND i.lasteventcountry = 'US'
   AND i.zip3 NOT        IN ('006', '007', '008', '009', '090', '091', '092', '093', '094', '095', '096', '097', '098', '340', '962', '963', '964', '965', '966', '967', '968', '969', '995', '996', '997', '998', '999')
GROUP BY     i.originfacilitycode,
         i.zip5,
         TRUNC(i.receivedate,'HH24'),
         TRUNC(m.event_date,'HH24');

I really appreciate any helps.

Upvotes: 0

Views: 1406

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

This is your subquery for m:

(SELECT TRUNC(event_date,'HH24'), tmi_uid
  FROM TRACK_OWNER.TRACKING_DETAIL@dblink_track 
WHERE event_id ='158' AND
      event_date BETWEEN to_date('04/01/2017 12:00:01 AM', 'MM/DD/YYYY HH12:MI:SS AM') AND to_date('04/30/2017 11:59:59 PM', 'MM/DD/YYYY HH12:MI:SS PM')
) m

It does not name the first column, so you cannot refer to it in an outer query. You can also simplify the date arithmetic, so:

(SELECT TRUNC(event_date,'HH24') as event_date, tmi_uid
 FROM TRACK_OWNER.TRACKING_DETAIL@dblink_track 
 WHERE event_id ='158' AND
      event_date >= date '2017-04-01' AND
      event_date < date '2017-05-01'
) m

Upvotes: 1

Related Questions