Proper syntax for named subquery - As ABC

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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.
Karen SchaeferBI ANALYST

Author

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%');
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
:) Now it makes more sense

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial