Oracle to T-SQL conversion. How can I make this work?

Question

Having trouble converting Oracle syntax to T-SQL. Trying to convert the following statement:

SELECT ORIG.*
,V.COUNTRY_COMMON_NAME
FROM 
(SELECT O.*
  ,LC.LOCAL_COUNCIL
  ,LC.REGIONAL_COUNCIL
FROM ES_W_ORG_DIM_INIT O
LEFT JOIN ES_W_ORG_DIM_INIT CNTR ON (O.RSC_CNTR_ORG_ID = CNTR.ORG_ID)
LEFT JOIN ES_W_LCL_CNCL_BASE LC ON (TO_CHAR(O.STK_DIST_UNIT_NUMBER) = 
TRIM(LC.UNITNUMBER))
WHERE O.ORG_TYPE_ID IN (7,8)
UNION
SELECT O.*
  ,LC.LOCAL_COUNCIL
  ,LC.REGIONAL_COUNCIL
FROM ES_W_ORG_DIM_INIT O
LEFT JOIN ES_W_ORG_DIM_INIT CNTR ON (O.RSC_CNTR_ORG_ID = CNTR.ORG_ID)
LEFT JOIN ES_W_LCL_CNCL_BASE LC ON (TO_CHAR(O.UNIT_NUMBER) = 
TRIM(LC.UNITNUMBER))
WHERE O.ORG_TYPE_ID IN (5,6)
UNION
SELECT O.*
  ,NULL AS LOCAL_COUNTCIL
  ,NULL AS REGIONAL_COUNCIL
FROM ES_W_ORG_DIM_INIT O
WHERE O.ORG_TYPE_ID IN (60,61)
) ORIG
LEFT JOIN DW_ERSDB_ORG_ADDR_VW V ON (ORIG.ORG_ID = V.ORG_ID AND 
V.ORG_ADDRESS_TYPE_ID = 1)

Attempted conversion:

WITH ORIG AS(
SELECT O.*
  ,LC.LOCAL_COUNCIL
  ,LC.REGIONAL_COUNCIL
FROM DSS_ERS_STAGE.ES_ORG_DIM O
LEFT JOIN DSS_ERS_STAGE.ES_ORG_DIM CNTR ON (O.RSC_CNTR_ORG_ID = CNTR.ORG_ID)
LEFT JOIN DSS_ERS_STAGE.ES_W_LCL_CNCL_BASE LC ON (CONVERT(VARCHAR, 
O.STK_DIST_UNIT_NUMBER) = RTRIM(LTRIM(LC.UNITNUMBER)))
WHERE O.ORG_TYPE_ID IN (7,8)
UNION
(SELECT O.*
  ,LC.LOCAL_COUNCIL
  ,LC.REGIONAL_COUNCIL
FROM DSS_ERS_STAGE.ES_ORG_DIM O
LEFT JOIN DSS_ERS_STAGE.ES_ORG_DIM CNTR ON (O.RSC_CNTR_ORG_ID = CNTR.ORG_ID)
LEFT JOIN DSS_ERS_STAGE.ES_W_LCL_CNCL_BASE LC ON (CONVERT(VARCHAR, 
O.UNIT_NUMBER) = RTRIM(LTRIM(LC.UNITNUMBER)))
WHERE O.ORG_TYPE_ID IN (5,6)
UNION
SELECT O.*
  ,NULL AS LOCAL_COUNCIL
  ,NULL AS REGIONAL_COUNCIL
FROM DSS_ERS_STAGE.ES_ORG_DIM O
WHERE O.ORG_TYPE_ID IN (60,61)
))
SELECT ORIG.*, V.COUNTRY_COMMON_NAME
FROM ORIG
LEFT JOIN DSS_ERS_STAGE.DW_ERSDB_ORG_ADDR_VW V ON (ORIG.ORG_ID = V.ORG_ID 
AND 
V.ORG_ADDRESS_TYPE_ID = 1)

*Just a note that the schemas specified are required in the target database

SQL Server error:

Msg 8156, Level 16, State 1, Line 1
The column 'LOCAL_COUNCIL' was specified multiple times for 'ORIG'.

Any ideas on how I can engineer this to make it work in SQL Server?

The error returned is because your query contains the column LOCAL_COUNCIL multiple times. This is usual when you use the * in your query. I would replace it by the columns you need in your program. Take a look on O.* and you will see that that column exists there also. — James
– James, Commented Jun 6, 2018 at 14:48
Also when you specify a varchar you should ALWAYS specify the size.sqlblog.org/2009/10/09/… — Sean Lange
– Sean Lange, Commented Jun 6, 2018 at 15:06

Joe Farrell · Accepted Answer · 2018-06-06 15:16:02Z

Jamie mentioned this in a comment but I'll try to explain in a bit more detail. For purposes of illustration, suppose I have the following two very simple tables.

create table CouncilA (LOCAL_COUNCIL int);
create table CouncilB (LOCAL_COUNCIL int);

insert CouncilA values (1);
insert CouncilB values (1);

SQL Server does allow you to query a result set that has non-unique column names. For instance, the following is legal:

select *
from
    CouncilA A
    inner join CouncilB B on A.LOCAL_COUNCIL = B.LOCAL_COUNCIL;

It produces the following result set:

LOCAL_COUNCIL   LOCAL_COUNCIL
1               1

However, the documentation for common table expressions explicitly states:

Duplicate names within a single CTE definition are not allowed.

So if I try to wrap my earlier query like this, as you've done in your attempted conversion:

with CTE as
(
    select *
    from
        CouncilA A
        inner join CouncilB B on A.LOCAL_COUNCIL = B.LOCAL_COUNCIL
)
select * from CTE;

Then I get the error message that you're seeing:

Msg 8156, Level 16, State 1, Line 7
The column 'LOCAL_COUNCIL' was specified multiple times for 'CTE'.

Incidentally, the same is true for a sub-SELECT:

select * from
(
    select *
    from
        CouncilA A
        inner join CouncilB B on A.LOCAL_COUNCIL = B.LOCAL_COUNCIL
) X;

Result:

Msg 8156, Level 16, State 1, Line 13
The column 'LOCAL_COUNCIL' was specified multiple times for 'X'.

The error message you see refers to ORIG, which is the name of your CTE, so the definition of that CTE has multiple columns called LOCAL_COUNCIL, which presumably means that your ES_W_ORG_DIM_INIT table has a column called LOCAL_COUNCIL. Make sure your column names are unique within your CTE and you should be okay.

Collectives™ on Stack Overflow

Oracle to T-SQL conversion. How can I make this work?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related