Oracle SQL join subquery dblink

Question

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.

your subquery did not return the date field... should be select trunc... as event_date or a different alias then change your main query m.<newname> — RoMEoMusTDiE
– RoMEoMusTDiE, Commented Jun 3, 2017 at 3:10

Gordon Linoff · Accepted Answer · 2017-06-03 03:45:49Z

1

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

answered Jun 3, 2017 at 3:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL join subquery dblink

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related