Posted: Fri Dec 10, 2004 12:17 am Post subject: Geting error in SAS as RQA > 9999 BYTES
Hi all,
when I written a SQL query in SAS it's giving the following error message
Quote:
ERROR: WHERE CLAUSE TOO LARGE; RQA > 9999 BYTES.
My SQL query is
Code:
PROC SQL;
CREATE TABLE WORK.FAIRMKT AS
SELECT DISTINCT CON.CNTRNBRA,
FAC.ASOFX,
CON.CMPYCD
FROM VIEW.D123FAC AS FAC,
VIEW.D456CON AS CON
WHERE FAC.CNTRNBRA = CON.CNTRNBRA
AND FAC.CMPYCD = CON.CMPYCD
AND FAC.ACTVTYP = 'PYMT'
AND CON.CNTRSTAT = 'C' AND CON.TERMX = ' '
AND CON.SUSPCD = '0'
AND CON.PENSTYP IN ('6','8','W','B')
ORDER BY CON.CNTRNBRA ASC,FAC.ASOFX ASC;
I have contacted SAS technical support team for this erroe there response is as follows
Quote:
The query being generated under the covers apparently is exceeding the CA-DATACOM/DB limit for the RQA(9999 bytes). When performing a join a performance "feature" in SAS/ACCESS will attempt to build the entire query when joining a relatively large table with a relatively small SAS table. It is possible that if one of your tables is just the "wrong" size in terms of # of rows and the other table is a "larger" Datacom table that this feature could activate and produce a query with all of your key values that may overflow the RQA. The size of the values in the CNTRNBRA CMPYCD columns will also play a factor. So, as you've discovered, the only circumvention is to break up the SAS table into smaller tables, run the join over each and append the results with proc append or a DATA Step set statement. You will probably never see this with a larger SAS table.
My information may be out of date. I'll let the Datacom experts confirm if this is still an issue at 8.2
The query being generated under the covers apparently is exceeding the CA-DATACOM/DB limit for the RQA(9999 bytes). When performing a join a performance "feature" in SAS/ACCESS will attempt to build the entire query when joining a relatively large table with a relatively small SAS table. It is possible that if one of your tables is just the "wrong" size in terms of # of rows and the other table is a "larger" Datacom table that this feature could activate and produce a query with all of your key values that may overflow the RQA. The size of the values in the CNTRNBRA CMPYCD columns will also play a factor. So, as you've discovered, the only circumvention is to break up the SAS table into smaller tables, run the join over each and append the results with proc append or a DATA Step set statement. You will probably never see this with a larger SAS table.
My information may be out of date. I'll let the Datacom experts confirm if this is still an issue at 8.2
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
