topic Re: EXPRESSION BUILDER PROBLEMS in SAS Enterprise Guide

EXPRESSION BUILDER PROBLEMS

deleted_user — Fri, 31 Mar 2006 02:17:49 GMT

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"

Re: EXPRESSION BUILDER PROBLEMS

ChrisHemedinger — Fri, 31 Mar 2006 15:07:37 GMT

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

Re: EXPRESSION BUILDER PROBLEMS

deleted_user — Fri, 31 Mar 2006 15:41:40 GMT

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?

Re: EXPRESSION BUILDER PROBLEMS

ChrisHemedinger — Mon, 03 Apr 2006 14:35:51 GMT

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