Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert Oracle Query to SQL Server Query

Author  Topic 

PeeJay2583
Starting Member

40 Posts
Posted - 2012-07-02 : 09:24:16
Hi everyone,

I have a working query from a colleague who uses Oracle, and I would like to use this same query in SQLServer.

This query uses TRUNC,DECODE,LENGTH commands, and I cannot seem to find an equivalent way to do this in SQLServer.

Here is the query:

(
SELECT
DM_CUSTOMER_RES_CUST_DATA_DIM.U_RESOURCE_HR_ID,
DM_VW_RESOURCE_DIM.level3,
DM_VW_RESOURCE_DIM.level4,
CASE WHEN DM_VW_ACTIVITY_DIM.activity_type = 'P' THEN 'Project' WHEN DM_VW_ACTIVITY_DIM.activity_type = 'L' THEN 'Service' ELSE 'Standard' END,
DM_VW_RESOURCE_DIM.level5,
DM_VW_WBS22_DIM.LEVEL2,
DM_VW_ACTIVITY_DIM.work_id,
DM_VW_ACTIVITY_DIM.PROJECT,
DM_VW_WORK_ATTRIBUTE_DIM.wbs6,
DM_VW_WORK_ATTRIBUTE_DIM.wbs7,
DM_VW_WORK_ATTRIBUTE_DIM.wbs5,
sum( DM_VW_BASE_FACT.actual_effort ),
DM_TIME_DIM.yearmo,
DM_VW_ACTIVITY_DIM.description,
DM_VW_ACTIVITY_DIM.work_type,
DM_VW_RESOURCE_DIM.description,
DM_VW_WORK_ATTRIBUTE_DIM.wbs2,
DECODE(TO_CHAR(DM_TIME_DIM.period_start,'D'),
'1', DM_TIME_DIM.period_start,
TRUNC(DM_TIME_DIM.period_finish - 6)),
DM_VW_ACTIVITY_DIM.work_status,
DM_VW_RESOURCE_ATTRIBUTE_DIM.obs20,
DM_VW_RESOURCE_DIM.level6,
DM_VW_RES_ATT_30_39_DIM.descr37,
DM_VW_RESOURCE_DIM.resource_type,
DM_VW_RESOURCE_DIM.level2,
LPAD(DM_VW_ACTIVITY_DIM.FULL_DESCRIPTION,LENGTH(DM_VW_ACTIVITY_DIM.FULL_DESCRIPTION) + DM_VW_ACTIVITY_DIM.DEPTH*2,' '),
DM_VW_ACTIVITY_DIM.structure_order
FROM
DM_VW_RES_ATT_30_39_DIM RIGHT OUTER JOIN DM_VW_RESOURCE_DIM ON (DM_VW_RESOURCE_DIM.RESOURCE_CODE=DM_VW_RES_ATT_30_39_DIM.RESOURCE_CODE)
INNER JOIN DM_VW_BASE_FACT ON (DM_VW_BASE_FACT.resource_code=DM_VW_RESOURCE_DIM.resource_code)
INNER JOIN DM_TIME_DIM ON (DM_TIME_DIM.PERIOD_NUMBER=DM_VW_BASE_FACT.PERIOD_NUMBER)
INNER JOIN DM_VW_ACTIVITY_BR ON (DM_VW_ACTIVITY_BR.CHILD_CODE=DM_VW_BASE_FACT.ACTIVITY_CODE)
INNER JOIN DM_VW_ACTIVITY_DIM ON (DM_VW_ACTIVITY_DIM.activity_code=DM_VW_ACTIVITY_BR.parent_code)
INNER JOIN DM_VW_USER_ACTIVITY_BR ON (DM_VW_USER_ACTIVITY_BR.structure_code=DM_VW_ACTIVITY_DIM.ACTIVITY_CODE AND DM_VW_USER_ACTIVITY_BR.user_name = 'PVMASTER')
INNER JOIN DM_VW_WORK_ATTRIBUTE_DIM ON (DM_VW_ACTIVITY_DIM.activity_code=DM_VW_WORK_ATTRIBUTE_DIM.activity_code)
INNER JOIN DM_VW_WBS22_DIM ON (DM_VW_WORK_ATTRIBUTE_DIM.wbs22_code=DM_VW_WBS22_DIM.STRUCTURE_CODE)
INNER JOIN DM_VW_USER_RESOURCE_BR ON (DM_VW_RESOURCE_DIM.RESOURCE_CODE=DM_VW_USER_RESOURCE_BR.structure_code AND DM_VW_USER_RESOURCE_BR.user_name= 'PVMASTER')
INNER JOIN DM_VW_RESOURCE_ATTRIBUTE_DIM ON (DM_VW_RESOURCE_DIM.RESOURCE_CODE=DM_VW_RESOURCE_ATTRIBUTE_DIM.RESOURCE_CODE)
INNER JOIN DM_CUSTOMER_RES_CUST_DATA_DIM ON (DM_VW_RESOURCE_DIM.RESOURCE_ID=DM_CUSTOMER_RES_CUST_DATA_DIM.RESOURCE_ID)

WHERE
(
DM_VW_RESOURCE_DIM.level3 In @prompt('Enter value(s) for Business Area:','A','Resources\Business Area',Multi,Free,Persistent,,User:0)
AND
DM_TIME_DIM.yearmo In @prompt('Enter value(s) for Year/Mo:','N','Time Periods\Year/Mo',Multi,Free,Persistent,,User:1)
AND
CASE WHEN DM_VW_ACTIVITY_DIM.activity_type = 'P' THEN 'Project' WHEN DM_VW_ACTIVITY_DIM.activity_type = 'L' THEN 'Service' ELSE 'Standard' END = 'Project'
AND
CASE WHEN DM_VW_ACTIVITY_BR.gap = 0 THEN 'Y' ELSE 'N' END = 'Y'
AND
DM_VW_ACTIVITY_DIM.detailed = 'N'
)
GROUP BY
DM_CUSTOMER_RES_CUST_DATA_DIM.U_RESOURCE_HR_ID,
DM_VW_RESOURCE_DIM.level3,
DM_VW_RESOURCE_DIM.level4,
CASE WHEN DM_VW_ACTIVITY_DIM.activity_type = 'P' THEN 'Project' WHEN DM_VW_ACTIVITY_DIM.activity_type = 'L' THEN 'Service' ELSE 'Standard' END,
DM_VW_RESOURCE_DIM.level5,
DM_VW_WBS22_DIM.LEVEL2,
DM_VW_ACTIVITY_DIM.work_id,
DM_VW_ACTIVITY_DIM.PROJECT,
DM_VW_WORK_ATTRIBUTE_DIM.wbs6,
DM_VW_WORK_ATTRIBUTE_DIM.wbs7,
DM_VW_WORK_ATTRIBUTE_DIM.wbs5,
DM_TIME_DIM.yearmo,
DM_VW_ACTIVITY_DIM.description,
DM_VW_ACTIVITY_DIM.work_type,
DM_VW_RESOURCE_DIM.description,
DM_VW_WORK_ATTRIBUTE_DIM.wbs2,
DECODE(TO_CHAR(DM_TIME_DIM.period_start,'D'),
'1', DM_TIME_DIM.period_start,
TRUNC(DM_TIME_DIM.period_finish - 6)),
DM_VW_ACTIVITY_DIM.work_status,
DM_VW_RESOURCE_ATTRIBUTE_DIM.obs20,
DM_VW_RESOURCE_DIM.level6,
DM_VW_RES_ATT_30_39_DIM.descr37,
DM_VW_RESOURCE_DIM.resource_type,
DM_VW_RESOURCE_DIM.level2,
LPAD(DM_VW_ACTIVITY_DIM.FULL_DESCRIPTION,LENGTH(DM_VW_ACTIVITY_DIM.FULL_DESCRIPTION) + DM_VW_ACTIVITY_DIM.DEPTH*2,' '),
DM_VW_ACTIVITY_DIM.structure_order
HAVING
(
sum( DM_VW_BASE_FACT.actual_effort ) > 0
OR
sum( DM_VW_BASE_FACT.reported_effort ) > 0
)
UNION
SELECT
DM_CUSTOMER_RES_CUST_DATA_DIM.U_RESOURCE_HR_ID,
DM_VW_RESOURCE_DIM.level3,
DM_VW_RESOURCE_DIM.level4,
CASE WHEN DM_VW_ACTIVITY_DIM.activity_type = 'P' THEN 'Project' WHEN DM_VW_ACTIVITY_DIM.activity_type = 'L' THEN 'Service' ELSE 'Standard' END,
DM_VW_RESOURCE_DIM.level5,
DM_VW_WBS22_DIM.LEVEL2,
DM_VW_ACTIVITY_DIM.work_id,
DM_VW_ACTIVITY_DIM.PROJECT,
DM_VW_WORK_ATTRIBUTE_DIM.wbs6,
DM_VW_WORK_ATTRIBUTE_DIM.wbs7,
DM_VW_WORK_ATTRIBUTE_DIM.wbs5,
sum( DM_VW_BASE_FACT.actual_effort ),
DM_TIME_DIM.yearmo,
DM_VW_ACTIVITY_DIM.description,
DM_VW_ACTIVITY_DIM.work_type,
DM_VW_RESOURCE_DIM.description,
DM_VW_WORK_ATTRIBUTE_DIM.wbs2,
DECODE(TO_CHAR(DM_TIME_DIM.period_start,'D'),
'1', DM_TIME_DIM.period_start,
TRUNC(DM_TIME_DIM.period_finish - 6)),
DM_VW_ACTIVITY_DIM.work_status,
DM_VW_RESOURCE_ATTRIBUTE_DIM.obs20,
DM_VW_RESOURCE_DIM.level6,
DM_VW_RES_ATT_30_39_DIM.descr37,
DM_VW_RESOURCE_DIM.resource_type,
DM_VW_RESOURCE_DIM.level2,
LPAD(DM_VW_ACTIVITY_DIM.FULL_DESCRIPTION,LENGTH(DM_VW_ACTIVITY_DIM.FULL_DESCRIPTION) + DM_VW_ACTIVITY_DIM.DEPTH*2,' '),
DM_VW_ACTIVITY_DIM.structure_order
FROM
DM_VW_RES_ATT_30_39_DIM RIGHT OUTER JOIN DM_VW_RESOURCE_DIM ON (DM_VW_RESOURCE_DIM.RESOURCE_CODE=DM_VW_RES_ATT_30_39_DIM.RESOURCE_CODE)
INNER JOIN DM_VW_BASE_FACT ON (DM_VW_BASE_FACT.resource_code=DM_VW_RESOURCE_DIM.resource_code)
INNER JOIN DM_TIME_DIM ON (DM_TIME_DIM.PERIOD_NUMBER=DM_VW_BASE_FACT.PERIOD_NUMBER)
INNER JOIN DM_VW_ACTIVITY_BR ON (DM_VW_ACTIVITY_BR.CHILD_CODE=DM_VW_BASE_FACT.ACTIVITY_CODE)
INNER JOIN DM_VW_ACTIVITY_DIM ON (DM_VW_ACTIVITY_DIM.activity_code=DM_VW_ACTIVITY_BR.parent_code)
INNER JOIN DM_VW_USER_ACTIVITY_BR ON (DM_VW_USER_ACTIVITY_BR.structure_code=DM_VW_ACTIVITY_DIM.ACTIVITY_CODE AND DM_VW_USER_ACTIVITY_BR.user_name = 'PVMASTER')
INNER JOIN DM_VW_WORK_ATTRIBUTE_DIM ON (DM_VW_ACTIVITY_DIM.activity_code=DM_VW_WORK_ATTRIBUTE_DIM.activity_code)
INNER JOIN DM_VW_WBS22_DIM ON (DM_VW_WORK_ATTRIBUTE_DIM.wbs22_code=DM_VW_WBS22_DIM.STRUCTURE_CODE)
INNER JOIN DM_VW_USER_RESOURCE_BR ON (DM_VW_RESOURCE_DIM.RESOURCE_CODE=DM_VW_USER_RESOURCE_BR.structure_code AND DM_VW_USER_RESOURCE_BR.user_name= 'PVMASTER')
INNER JOIN DM_VW_RESOURCE_ATTRIBUTE_DIM ON (DM_VW_RESOURCE_DIM.RESOURCE_CODE=DM_VW_RESOURCE_ATTRIBUTE_DIM.RESOURCE_CODE)
INNER JOIN DM_CUSTOMER_RES_CUST_DATA_DIM ON (DM_VW_RESOURCE_DIM.RESOURCE_ID=DM_CUSTOMER_RES_CUST_DATA_DIM.RESOURCE_ID)

WHERE
(
DM_VW_RESOURCE_DIM.level3 In @prompt('Enter value(s) for Business Area:','A','Resources\Business Area',Multi,Free,Persistent,,User:1)
AND
CASE WHEN DM_VW_ACTIVITY_DIM.activity_type = 'P' THEN 'Project' WHEN DM_VW_ACTIVITY_DIM.activity_type = 'L' THEN 'Service' ELSE 'Standard' END = 'Standard'
AND
DM_VW_ACTIVITY_DIM.depth = 7
AND
DM_VW_RESOURCE_DIM.depth = 7
AND
DM_TIME_DIM.yearmo In @prompt('Enter value(s) for Year/Mo:','N','Time Periods\Year/Mo',Multi,Free,Persistent,,User:2)
)
GROUP BY
DM_CUSTOMER_RES_CUST_DATA_DIM.U_RESOURCE_HR_ID,
DM_VW_RESOURCE_DIM.level3,
DM_VW_RESOURCE_DIM.level4,
CASE WHEN DM_VW_ACTIVITY_DIM.activity_type = 'P' THEN 'Project' WHEN DM_VW_ACTIVITY_DIM.activity_type = 'L' THEN 'Service' ELSE 'Standard' END,
DM_VW_RESOURCE_DIM.level5,
DM_VW_WBS22_DIM.LEVEL2,
DM_VW_ACTIVITY_DIM.work_id,
DM_VW_ACTIVITY_DIM.PROJECT,
DM_VW_WORK_ATTRIBUTE_DIM.wbs6,
DM_VW_WORK_ATTRIBUTE_DIM.wbs7,
DM_VW_WORK_ATTRIBUTE_DIM.wbs5,
DM_TIME_DIM.yearmo,
DM_VW_ACTIVITY_DIM.description,
DM_VW_ACTIVITY_DIM.work_type,
DM_VW_RESOURCE_DIM.description,
DM_VW_WORK_ATTRIBUTE_DIM.wbs2,
DECODE(TO_CHAR(DM_TIME_DIM.period_start,'D'),
'1', DM_TIME_DIM.period_start,
TRUNC(DM_TIME_DIM.period_finish - 6)),
DM_VW_ACTIVITY_DIM.work_status,
DM_VW_RESOURCE_ATTRIBUTE_DIM.obs20,
DM_VW_RESOURCE_DIM.level6,
DM_VW_RES_ATT_30_39_DIM.descr37,
DM_VW_RESOURCE_DIM.resource_type,
DM_VW_RESOURCE_DIM.level2,
LPAD(DM_VW_ACTIVITY_DIM.FULL_DESCRIPTION,LENGTH(DM_VW_ACTIVITY_DIM.FULL_DESCRIPTION) + DM_VW_ACTIVITY_DIM.DEPTH*2,' '),
DM_VW_ACTIVITY_DIM.structure_order
HAVING
(
sum( DM_VW_BASE_FACT.reported_effort ) > 0
OR
sum( DM_VW_BASE_FACT.actual_effort ) > 0
)
)


Any help would be appreciated!
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site