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 7 (of 7 total)

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

Ken McKelvey SSCoach Points: 18842 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: 246959 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 246959 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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.