PL/SQL to T-SQL Code Error

Question

PL/SQL to T-SQL Code Error

carlton 84646

Ten Centuries

Points: 1026
More actions
October 7, 2021 at 11:11 am

#3938411
Hi Community,
Before I ask this question, I want to let you know that I followed the advice @Phil Parkins advice and first asked this question on an Oracle forum, however I told on that forum to use MSN Forum for this question. So here it is.
I'm trying to convert the following PL/SQL to T-SQL, but I'm getting the errors:
Incorrect syntax near the keyword 'WITH'.
And
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
The code in PL/SQL is as follows:
```
MERGE INTO tbl_legal_probatewlgmt_case trg

  USING

  (

  -- Derived attribute

  WITH

earliest_wlgmt_events

  AS

  (

 SELECT m.legal_case_id AS ce_case_data_id,

    MIN(m.event_created_timestamp) AS case_created_datetime

    FROM  v_legal_probatewlgmt_case_evt m 

WHERE m.legal_case_event_type_cid NOT IN (SELECT source_event_type_cid FROM v_prbtgrant_case_event_grps WHERE event_type_grp_key = gc_event_grp_draftcase_key)

GROUP BY m.legal_case_id

  )
```
Any thoughts on how to fix this error?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18938 More actions · Answer 1

CTEs work differently in Oracle and SQL. In SQL Server they have to be declared first but more can be done with them. eg

WITH earliest_wlgmt_events
AS
(
SELECT m.legal_case_id AS ce_case_data_id,
MIN(m.event_created_timestamp) AS case_created_datetime
FROM  v_legal_probatewlgmt_case_evt m 
WHERE m.legal_case_event_type_cid NOT IN (SELECT source_event_type_cid FROM v_prbtgrant_case_event_grps WHERE event_type_grp_key = gc_event_grp_draftcase_key)
GROUP BY m.legal_case_id
)
MERGE INTO tbl_legal_probatewlgmt_case trg
USING
(
SELECT ce_case_data_id, case_created_datetime
FROM earliest_wlgmt_events
...
)
...

MERGE is badly implemented in SQL Server so you may be better doing separate INSERT, UPDATE and DELETE statements.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

Before I ask this question, I want to let you know that I followed the advice @Phil Parkins advice and first asked this question on an Oracle forum, however I told on that forum to use MSN Forum for this question. So here it is.

This forum is SQL ServerCentral.com, not MSN.

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 3

Hi Kevin,

Thanks for getting in touch.

I'm getting the following error...

Incorrect syntax near ')'.

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 4

carlton 84646

Ten Centuries

Points: 1026

October 7, 2021 at 1:24 pm

#3938460

Oops!

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 5

Any thoughts from anyone else regarding the error from this query provided by Kevin?

Phil Parkin SSC Guru Points: 247180 More actions · Answer 6

carlton 84646 wrote:

Any thoughts from anyone else regarding the error from this query provided by Kevin?

I see no responses from Kevin.

Ken's query is a code fragment, as I assume is obvious from the ellipses (...)? You need to fill in the gaps.

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 7

carlton 84646

Ten Centuries

Points: 1026

October 7, 2021 at 1:59 pm

#3938482

Oops, again, sorry Ken.