The documentation indicated the use of double quotes while using a reserved word for other purposes. I've tried out in this example below:
PROC FEDSQL; DROP TABLE WORK.T1; create table T1 ( "ROW_NUMBER" CHAR(2), name char(10), state char(2)); DESCRIBE TABLE WORK.T1; SELECT * FROM DICTIONARY.COLUMNS WHERE TABLE_NAME = 'T1'; QUIT;
The results are shown below for your reference:
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF TKTS_DATA_TYPE TKTS_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE WORK WORK T1 ROW_NUMBER -8 NCHAR 2 3 . . 1 -8 . 2 1 YES WORK WORK T1 NAME -8 NCHAR 10 11 . . 1 -8 . 10 2 YES WORK WORK T1 STATE -8 NCHAR 2 3 . . 1 -8 . 2 3 YES
The header has shifter a bit but have highlighted in red for ease of reading... Hope this helps.... Good Luck...!!!
Hi Kannan, thanks for your answer, but I think I did not state my question clear enough. What I meant is to use ROW_NUMBER as it really meant to be, i.e. as a function of retreving row number(e.g. https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions144.htm ).
I tried to use ROW_NUMBER to get row number but it did not work.
I believe the documentation states that the FEDSQL RESERVED words cannot be used as variable names or in any other way as indicated in the document below:
The documentation you provided in your URL refers to the ROW_NUMBER feature in ORACLE database. This is one among those nuiances between the two softwares that seems identical but not really so.
Hope this clarifies your conundrum.... Good Luck...!!!
I can totally understand if SAS FedSQL misses a function which Oracle SQL has. I am just curious since FedSQL already makes ROW_NUMBER a reserved word, I guess there might be a way of using ROW_NUMBER to retrieve row number in FedSQL. Maybe this function is undocumented but usable.
I would still consider FeqSQL as an very early implementation (new to 9.4) of ANSI SQL:1999 core standard, which is the part of 'in-database' prcessing effort along with DS2. Let's hope those reserved key words maybe put in use in the future release. As for the time being, you can still leverage ODS for the same functionality:
ods listing close; ods output fedsql_results=want; proc fedsql number; select * from yourlib.have ; quit; ods listing;
Haikuo, thanks for your response. My interpretation is the same as yours, i.e. ROW_NUMBER is a reserved word in FedSQL only means a possibility of future implementation.
Also thanks for your suggestion, but I guess this ODS approach won't work for ROW_NUMBER by group. I do know other turn around approaches to get ROW_NUMBER by group, just hope there is a staightfoward way of doing this in SAS.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.