How to fix START WITH "ORACLE syntax in SQLSERVER

Question

Here is a sample oracle query ..wants to convert into SQL Server.

failing at "START WITH" and "fetch first row only"

how to resolve these ..any pointers would be helpful

SELECT F1.FILE_ID, F1.REVISION, F1.REVISION_TIME,F1.NAMESPACE, F1.FULL_PATH,F1.OWNED_BY_NAME, F1.OWNED_BY_TYPE,F1.EXT_ATTRIBUTES,F1.SYS_CREATED_BY_NAME,F1.SYS_CREATED_BY_TYPE,F1.SYS_MODIFIED_BY_NAME,F1.SYS_MODIFIED_BY_TYPE,F1.ACL_ID, 

 ROW_NUMBER() OVER (PARTITION BY F1.LOOKUP_KEY, F1.PATH_DEPTH ORDER BY F1.REVISION_TIME DESC) RANK 

FROM 

BI180123.CSS_SI_FILES

F1

WHERE F1.SERVICE_INSTANCE_ID = 1

AND F1.NAMESPACE = 'demo' START WITH F1.PARENT_ID=(SELECT FT.FILE_ID FROM

BI180123.CSS_SI_FILES

FT

WHERE UPPER(convert(varchar, FT.FULL_PATH))= UPPER('/shared')

AND FT.NAMESPACE= 'demo'

fetch first row only)

CONNECT BY F1.PARENT_ID = PRIOR F1.FILE_ID

ORDER BY FILE_ID

Answer 1

From your question and a check on ORACLE's syntax, you appear to need a recursive query that has a single anchor member and then recurses through based on the relationship between PARENTID and FILEID.

SQL Server's T-SQL syntax approached this differently. It uses a CTE expression to effectively add the recursive part with the anchor member.

I have assumed that the fetch first row only statement restricts it to a single root, but as you haven't provided sample data I don't know if it is safe to assume there will only be a single row returned by the anchor component. I have therefore used a TOP statement to enforce this.

Without sample data, I haven't tested the query, but it should be used as a starting point and refined as required. You can find more about recursive CTEs at this Docs page

SAMPLE QUERY

WITH ParentQuery ()
AS (
	SELECT TOP (1) 
		FT.[FILE_ID], 
		FT.REVISION, 
		FT.REVISION_TIME,
		FT.[NAMESPACE], 
		FT.FULL_PATH,
		FT.OWNED_BY_NAME, 
		FT.OWNED_BY_TYPE,
		FT.EXT_ATTRIBUTES,
		FT.SYS_CREATED_BY_NAME,
		FT.SYS_CREATED_BY_TYPE,
		FT.SYS_MODIFIED_BY_NAME,
		FT.SYS_MODIFIED_BY_TYPE,
		FT.ACL_ID, 
		0 as [RANK]
	FROM BI180123.CSS_SI_FILES FT
	WHERE UPPER(convert(varchar, FT.FULL_PATH))= UPPER('/shared')
		  AND FT.NAMESPACE= 'demo'
	UNION ALL
	SELECT 
		F1.[FILE_ID], 
		F1.REVISION, 
		F1.REVISION_TIME,
		F1.[NAMESPACE], 
		F1.FULL_PATH,
		F1.OWNED_BY_NAME, 
		F1.OWNED_BY_TYPE,
		F1.EXT_ATTRIBUTES,
		F1.SYS_CREATED_BY_NAME,
		F1.SYS_CREATED_BY_TYPE,
		F1.SYS_MODIFIED_BY_NAME,
		F1.SYS_MODIFIED_BY_TYPE,
		F1.ACL_ID, 
		ROW_NUMBER() OVER (
							PARTITION BY F1.LOOKUP_KEY, F1.PATH_DEPTH 
							ORDER BY F1.REVISION_TIME DESC
		)
	FROM BI180123.CSS_SI_FILES F1
		INNER JOIN ParentQuery pq ON pq.[FILE_ID] = F1.PARENT_ID
	WHERE F1.SERVICE_INSTANCE_ID = 1
		AND F1.[NAMESPACE] = 'demo' 
)
SELECT 	[FILE_ID], 
		REVISION, 
		REVISION_TIME,
		[NAMESPACE], 
		FULL_PATH,
		OWNED_BY_NAME, 
		OWNED_BY_TYPE,
		EXT_ATTRIBUTES,
		SYS_CREATED_BY_NAME,
		SYS_CREATED_BY_TYPE,
		SYS_MODIFIED_BY_NAME,
		SYS_MODIFIED_BY_TYPE,
		ACL_ID, 
		[RANK]
FROM ParentQuery
ORDER BY [FILE_ID] ;

Answer 2

Hi @Sreenivas Gaddam

For the two problem points you encounter in the conversion process, there are corresponding function statements in SQL Server.

"WHERE... START WITH... Connect By ... Prior..." is essentially a tree query structure that first filters the conditions after START WITH, then the conditions after Connect By, and finally the conditions after WHERE. In SQL Server, the same functionality can be achieved using multiple CTEs.

https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

"Fetch First Row Only" in SQL Server, you can use TOP instead.

https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver16

You can refer to the content in the above two links first. If you want more help with transcoding, you'll need to provide some data samples.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

thank you , will try these suggestions

Answer 4

Thanks a lot for your time PercyTang-MSFT

It would be great if you could help with the query.

serviceinstanceid =1

path= '/shared/DV'

NAMESPACE='catalog'

The expected result from the query would be

Answer 5

Thank you all for your support,

The below query is working, please advise if any optimizations are required.

WITH ParentQuery
AS (
	SELECT TOP (1) 
		FT.[FILE_ID], 
		FT.PARENT_ID,
		FT.REVISION, 
		FT.REVISION_TIME,
		FT.[NAMESPACE], 
		FT.FULL_PATH,
		FT.OWNED_BY_NAME, 
		FT.OWNED_BY_TYPE,
		FT.EXT_ATTRIBUTES,
		FT.SYS_CREATED_BY_NAME,
		FT.SYS_CREATED_BY_TYPE,
		FT.SYS_MODIFIED_BY_NAME,
		FT.SYS_MODIFIED_BY_TYPE,
		FT.ACL_ID
		
	FROM BI180123_BIPLATFORM.CSS_SI_FILES FT
	WHERE UPPER(convert(nvarchar(max), FT.FULL_PATH))= UPPER('/shared/Sample Lite/Sample Scorecard - Lite')
		  AND FT.NAMESPACE= 'content:catalog'
	UNION ALL
	SELECT 
		F1.[FILE_ID], 
		F1.PARENT_ID,
		F1.REVISION, 
		F1.REVISION_TIME,
		F1.[NAMESPACE], 
		F1.FULL_PATH,
		F1.OWNED_BY_NAME, 
		F1.OWNED_BY_TYPE,
		F1.EXT_ATTRIBUTES,
		F1.SYS_CREATED_BY_NAME,
		F1.SYS_CREATED_BY_TYPE,
		F1.SYS_MODIFIED_BY_NAME,
		F1.SYS_MODIFIED_BY_TYPE,
		F1.ACL_ID
		
	FROM BI180123_BIPLATFORM.CSS_SI_FILES F1
		INNER JOIN ParentQuery pq ON pq.[FILE_ID] = F1.PARENT_ID
	WHERE F1.SERVICE_INSTANCE_ID = 1
		AND F1.[NAMESPACE] = 'content:catalog' 
)
SELECT 	[FILE_ID],
PARENT_ID,
		REVISION, 
		REVISION_TIME,
		[NAMESPACE], 
		FULL_PATH,
		OWNED_BY_NAME, 
		OWNED_BY_TYPE,
		EXT_ATTRIBUTES,
		SYS_CREATED_BY_NAME,
		SYS_CREATED_BY_TYPE,
		SYS_MODIFIED_BY_NAME,
		SYS_MODIFIED_BY_TYPE,
		ACL_ID
	
		
FROM ParentQuery;