EXPRESSION BUILDER PROBLEMS

[deleted_user]

Hello,
I'm trying to build a query in the expression builder and it shows an error msg in the log, when I run the process for which i've written the query. If I run the same query in the Enhanced editor, it runs perfectly. Any help would greatly be appreciated.

The query goes like this in the expression builder:

"LIBNAME MMTGT ORACLE USER=MMTGT PASSWORD=XXXXX PATH='ORCL' PRESERVE_NAMES=YES;
LIBNAME MMSRC ORACLE USER=MMSRC PASSWORD=XXXXX PATH='ORCL' PRESERVE_NAMES=YES;

PROC SQL;
DROP TABLE MMTGT.CITY;
CREATE TABLE CITY (RENAME=(SUPP_CITY=CITY)) AS
(
SELECT DISTINCT(SUPP_CITY) FROM MMSRC.SUPPLIER_TBL
UNION
SELECT DISTINCT(TRANSPORTER_CITY) FROM
MMSRC.TRANSPORTER_TBL
UNION
SELECT DISTINCT(BANK_CITY) FROM MMSRC.BANK_TBL
);
QUIT;
=========================================================

The error msg I get in the log window goes like this:

LIBNAME MMTGT ORACLE USER=MMTGT PASSWORD=XXXXX PATH='ORCL'
PRESERVE_NAMES=YES;
NOTE: Libref MMTGT was successfully assigned as follows:
Engine: ORACLE
Physical Name: ORCL
LIBNAME MMSRC ORACLE USER=MMSRC PASSWORD=XXXXX PATH='ORCL'
PRESERVE_NAMES=YES;
NOTE: Libref MMSRC was successfully assigned as follows:
Engine: ORACLE
Physical Name: ORCL
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.07 seconds
cpu time 0.04 seconds

PROC SQL;
DROP TABLE MMTGT.CITY;
NOTE: Table MMTGT.CITY has been dropped.
CREATE TABLE CITY (RENAME=(SUPP_CITY=CITY)) AS
(
SELECT DISTINCT(SUPP_CITY) FROM MMSRC.SUPPLIER_TBL
UNION
SELECT DISTINCT(TRANSPORTER_CITY) FROM MMSRC.TRANSPORTER_TBL
UNION
SELECT DISTINCT(BANK_CITY) FROM MMSRC.BANK_TBL
);
NOTE: Table WORK.CITY created, with 7 rows and 1 columns.

QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.29 seconds
cpu time 0.07 seconds
NOTE: SCL source line.
AS SUPP_CITY length=20
--
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

"AS is UNDERLINED and is the ERROR"

[ChrisHemedinger]

Hello,

We've looked at the PROC SQL code and we cannot determine how you might have used the EG query builder or expression builder to generate this.

I'd suggest opening a tech support track with more information about how you arrived at this point. You can open a track with tech support with this form: http://support.sas.com/techsup/contact/submit_emits2.html

Chris

[deleted_user]

hello Chris,

Thanx for the suggestion. I'm facing this issue in SAS/Warehouse Administrator expression builder. I know i posted this msg out of context for the forum, since I couldnt find any relevant forum to the area for which i'm seeking help.

Anyways thanx for the redirection. Can u also suggest any forums for the sas/warehouse administrator product?

[ChrisHemedinger]

We're just getting started with these discussion forums, offering a few popular topics to see how it takes off.

I believe that new forums will come online later based on demand and popularity. If you want to see a forum on a particular topic, send the suggestion via http://support.sas.com/contact/software_comments.html

Chris