Proper syntax for named subquery - As ABC

Need help with the correct syntax to use a named subquery into another query.

AS VSI  
  SELECT VENDOR_SITE_ID
  FROM tblAll
WHERE creation_date >= ADD_MONTHS(TRUNC(SYSDATE, 'YY'), -24)
    and STATE = 'IL' ;
 
  SELECT *
  FROM VSI
  --INNER JOIN A.VENDOR_ID as
  WHERE (A.VENDOR_NAME like '%DEPARTMENT%'
  or A.VENDOR_NAME like '%ILLINOIS%');

what am I missing to call VSI as the datasource for 2nd query?

Thanks,

Karen
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
SELECT *
  FROM VSI A
  WHERE (A.VENDOR_NAME like '%DEPARTMENT%'
  or A.VENDOR_NAME like '%ILLINOIS%');


You do not need a join.  At least there is no apparent reason from what is seen in the question. You dont even really need a cte as the where clauses coild be combined.
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
This is my final results:  Thanks for the great assist.

WITH VSI AS
  (
  SELECT VENDOR_SITE_ID, STATE
  FROM tblALL
  WHERE STATE = 'IL'
  )
  SELECT S.VENDOR_NAME, A.VENDOR_SITE_ID, A.STATE
  FROM  tbl123 S
  INNER JOIN VSI A on A.VENDOR_SITE_ID = S.VENDOR_ID;  
  --WHERE (S.VENDOR_NAME like '%DEPARTMENT%'
  --    or S.VENDOR_NAME like '%ILLINOIS%');
1
 
PortletPaulfreelancerCommented:
:) Now it makes more sense
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.