CODEWITHSUNDEEP
sql-serveroracle-databasesubquerycommon-table-expression
Joel Jacobson
Joel Jacobson

Reputation: 145

Converting Oracle Query into T-SQL query using WITH

I have a query from an old database that we converting into T-SQL, but having issues using CTE:

Original Query in Oracle:

    select company_name, display_name, active_flag, naic_code, group_number, alien_code, fein, 
    status_desc, status_detail_desc, due_to_merger_flag, co_code, to_char(status_date, 'MM/DD/YYYY') 
    Inactive_Date, active_flag from 
    (select nm.COMPANY_NAME, cmp.recordid_number, orgtp.display_name, cmp.active_flag, 
    cmp.naic_code, grpnm.group_number, cmp.alien_code, cmp.fein, st.status_desc, stdt.status_detail_desc, 
    storgjn.due_to_merger_flag, storgjn.co_code, storgjn.status_date, st.active_flag as activestatus, 
    max(storgjn.status_date) over (partition by cmp.recordid_number, orgtp.display_name) max_status_date
    from aip.co_company cmp join aip.CO_NAME nm on cmp.COMPANY_ID = nm.company_id and nm.active_flag = 1
    left outer join aip.co_company_group_jn cmpgrpjn on nm.COMPANY_ID = cmpgrpjn.company_id and cmpgrpjn.active_flag = 1
    left outer join aip.co_group_name grpnm on cmpgrpjn.group_id = grpnm.group_id and grpnm.active_flag = 1
    join aip.co_org org on cmp.COMPANY_ID = org.company_id
    join aip.co_org_type orgtp on org.org_type_id = orgtp.org_type_id
    join aip.co_status_org_jn storgjn on org.org_id = storgjn.org_id
    join aip.co_status_detail stdt on storgjn.status_detail_id = stdt.status_detail_id
    join aip.co_status st on stdt.status_id = st.status_id
    WHERE cmp.recordid_number = '10632' AND
    stdt.status_detail_desc <> 'Record Begin Date')
    WHERE status_date = max_status_date

And converting into T-SQl im using:

WITH YOURCTE(WHATEVA) AS
(
SELECT nm.COMPANY_NAME, cmp.recordid_number, orgtp.display_name, cmp.active_flag, 
cmp.naic_code, grpnm.group_number, cmp.alien_code, cmp.fein, st.status_desc, stdt.status_detail_desc, 
storgjn.due_to_merger_flag, storgjn.co_code, storgjn.status_date, st.active_flag as activestatus, 
max(storgjn.status_date) over (partition by cmp.recordid_number, orgtp.display_name) max_status_date
from aip.co_company cmp join aip.CO_NAME nm on cmp.COMPANY_ID = nm.company_id and nm.active_flag = 1
left outer join aip.co_company_group_jn cmpgrpjn on nm.COMPANY_ID = cmpgrpjn.company_id and cmpgrpjn.active_flag = 1
left outer join aip.co_group_name grpnm on cmpgrpjn.group_id = grpnm.group_id and grpnm.active_flag = 1
join aip.co_org org on cmp.COMPANY_ID = org.company_id
join aip.co_org_type orgtp on org.org_type_id = orgtp.org_type_id
join aip.co_status_org_jn storgjn on org.org_id = storgjn.org_id
join aip.co_status_detail stdt on storgjn.status_detail_id = stdt.status_detail_id
join aip.co_status st on stdt.status_id = st.status_id
WHERE cmp.recordid_number = '10632' AND
stdt.status_detail_desc <> 'Record Begin Date'
) 
select company_name, display_name, active_flag, naic_code, group_number, alien_code, fein, 
status_desc, status_detail_desc, due_to_merger_flag, co_code, CONVERT(VARCHAR(10),status_date,120) AS Inactive_Date,
active_flag
FROM YOURCTE
WHERE status_date = max_status_date

But I get the following error :

Msg 8158, Level 16, State 1, Line 2

'YOURCTE' has more columns than were specified in the column list.

The reason i have more columns in my cte is because I'm using the recordid_number column to bring other data for where conditioning. I will appreciate some help, thank you.

Upvotes: 2

Views: 43

Answers (1)

user1443098
user1443098

Reputation: 7645

Either replace "WHATEVA" with a complete list of the column aliases you want to use, or remove it and let the original column names stand.

Inherit

;WITH YOURCTE AS
(...

Explicit

;WITH YOURCTE (COMPANY_NAME, recordid_number, ...) AS
(...

Upvotes: 3

Related Questions