10-31-2015 04:22 PM - edited 10-31-2015 04:23 PM
10-31-2015 10:09 PM
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...!!!
10-31-2015 10:16 PM
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.
10-31-2015 10:35 PM - edited 10-31-2015 10:37 PM
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...!!!
10-31-2015 10:45 PM
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.
10-31-2015 11:16 PM
It is possible that the reserved word functionality is for its internal use and if a user wants to use the name could use it through double quotes as per the documentation.
11-01-2015 02:07 PM
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;
11-01-2015 09:06 PM
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.