PL/SQL to T-SQL Code Error

Question

PL/SQL to T-SQL Code Error

carlton 84646

SSC Eights!

Points: 902
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: 18484 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: 245813 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

carlton 84646 SSC Eights! Points: 902 More actions · Answer 3

Hi Kevin,

Thanks for getting in touch.

I'm getting the following error...

Incorrect syntax near ')'.

carlton 84646 SSC Eights! Points: 902 More actions · Answer 4

carlton 84646

SSC Eights!

Points: 902

October 7, 2021 at 1:24 pm

#3938460

Oops!

carlton 84646 SSC Eights! Points: 902 More actions · Answer 5

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

Phil Parkin SSC Guru Points: 245813 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

carlton 84646 SSC Eights! Points: 902 More actions · Answer 7

carlton 84646

SSC Eights!

Points: 902

October 7, 2021 at 1:59 pm

#3938482

Oops, again, sorry Ken.