Date:         Thu, 8 Apr 2004 14:49:20 -0400
Reply-To:     Venky Chakravarthy <venky.chakravarthy@PFIZER.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Venky Chakravarthy <venky.chakravarthy@PFIZER.COM>
Subject:      Re: SQL: unresolved reference to table
Comments: To: Michael Mace <michael_mace@GOODYEAR.COM>
Content-Type: text/plain; charset=ISO-8859-1

The following is without testing. So be forewarned.

If you provide explicit aliases to each table that is referenced twice, it
may help the SQL processor in handling it correctly.

From newx as n1, criteria as c1

After the UNION

From newx as n2, criteria as c2

This might work. Make sure you reference the columns in the select and
where clauses with the aliases.

Venky

On Thu, 8 Apr 2004 14:19:00 -0400, Michael Mace <michael_mace@GOODYEAR.COM>
wrote:

>All:
>
>What am i missing / doing wrong in the WHERE clauses of the PROC SQL step
>below ?
>
><code snipped>
>NOTE: 38 records were read from the infile SODCCG.
>NOTE: The data set WORK.CRITERIA has 38 observations and 5 variables.
>NOTE: The DATA statement used 0.02 CPU seconds and 8220K.
>
><code snipped>
>NOTE: 283259 records were read from the infile INDD.
>NOTE: The data set WORK.NEWX has 283259 observations and 12 variables.
>NOTE: The DATA statement used 2.75 CPU seconds and 8348K.
>
><code snipped>
>NOTE: 251527 records were read from the infile CPNPREV.
>NOTE: The data set WORK.OLDX has 251527 observations and 12 variables.
>NOTE: The DATA statement used 2.49 CPU seconds and 9081K.
>
>77        +
>78        +PROC SQL ;
>79        +CREATE TABLE newds AS
>80        +SELECT newx.*
>81        +FROM newx, criteria
>82        +WHERE newx.cg ¬= "  " AND newx.sodccg = criteria.sodccg
>83        +UNION
>84        +SELECT newx.*
>85        +FROM newx, criteria
>86        +WHERE newx.cg = "  " AND newx.sodc = criteria.sodc
>87        +ORDER BY newx.key
>88        +;
>ERROR: Unresolved reference to table/correlation name newx.
>ERROR: Unresolved reference to table/correlation name newx.
>ERROR: Unresolved reference to table/correlation name newx.
>
>Thank you for your assistance!
>Have a joyous Easter weekend !
>
>        At your service,
>michael mace
>330.796.3981
>michael_mace@goodyear.com
>NAT e-Business / infoLink
>for The Goodyear Tire & Rubber Co.