topic Re: How to use ROW_NUMBER in FedSQL? in SAS Procedures

How to use ROW_NUMBER in FedSQL?

jflycn — Sat, 31 Oct 2015 20:23:46 GMT

ROW_NUMBER is a reserved word in FedSQL (See: http://support.sas.com/documentation/cdl/en/fedsqlref/67364/HTML/default/p0x0k1rlx0l7e4n1ptngog3ce4jp.htm ), but I cannot figure out how to use ROW_NUMBER in it. Whatever I tried, I always got "ERROR: Syntax error at or near "ROW_NUMBER"".

So how can I use ROW_NUMBER in FedSQL?

Thanks!

Re: How to use ROW_NUMBER in FedSQL?

kannand — Sun, 01 Nov 2015 02:09:38 GMT

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...!!!

Re: How to use ROW_NUMBER in FedSQL?

jflycn — Sun, 01 Nov 2015 02:16:23 GMT

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.

Re: How to use ROW_NUMBER in FedSQL?

kannand — Sun, 01 Nov 2015 02:37:29 GMT

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:

https://support.sas.com/documentation/cdl/en/fedsqlref/67954/HTML/default/viewer.htm#p0x0k1rlx0l7e4n1ptngog3ce4jp.htm

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.

https://support.sas.com/documentation/cdl/en/fedsqlref/67954/HTML/default/viewer.htm#p0x0k1rlx0l7e4n1ptngog3ce4jp.htm

Hope this clarifies your conundrum.... Good Luck...!!!

Re: How to use ROW_NUMBER in FedSQL?

jflycn — Sun, 01 Nov 2015 02:45:52 GMT

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.

Re: How to use ROW_NUMBER in FedSQL?

kannand — Sun, 01 Nov 2015 03:16:05 GMT

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.

Re: How to use ROW_NUMBER in FedSQL?

Haikuo — Sun, 01 Nov 2015 19:07:45 GMT

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;

Re: How to use ROW_NUMBER in FedSQL?

jflycn — Mon, 02 Nov 2015 02:06:43 GMT

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.