Oracle Add Command Issue / SQL Question

[baycolor]

Hi,

I have no Oracle experience only Teradata however my current project requires me to write some SQL to put in a CR Add Command. I'm using CR XI.

I have one report that uses an Add Command to pull data back from Oracle (what version of Oracle I don't know but probably 9i). This report works fine. Add Command SQL is:

***************************************************
SELECT DISTINCT
RPT_KM_ASSET_DATA.ASSET_ID
,RPT_KM_ASSET_DATA.OWNER_USER_QLID
,RPT_KM_ASSET_DATA.ASSET_DOWNLOAD_USER_QLID
,RPT_KM_ASSET_DATA.ASSET_TITLE
FROM
RPT_KM_ASSET_DATA
WHERE
RPT_KM_ASSET_DATA.current_flag = 'Y'
***************************************************

I need a second version of this report that includes a field from another table. My first thought was to LEFT OUTER JOIN to that table via a derived table. I attempted this:

***************************************************
SELECT DISTINCT
RPT_KM_ASSET_DATA.ASSET_ID
,RPT_KM_ASSET_DATA.OWNER_USER_QLID
,RPT_KM_ASSET_DATA.ASSET_DOWNLOAD_USER_QLID
,RPT_KM_ASSET_DATA.ASSET_TITLE
,DT1.dt1_published_date_time
FROM
RPT_KM_ASSET_DATA
LEFT OUTER JOIN
(SELECT
dt1_asset_id
,dt1_published_date_time
FROM
RPT_KM_ASSET_PUBLISHING
WHERE current_flag = 'Y') DT1
ON RPT_KM_ASSET_DATA.asset_id = DT1.dt1_asset_id
WHERE
RPT_KM_ASSET_DATA.current_flag = 'Y'
***************************************************

When I put this in the new reports Add Command window and click OK CR just hangs and eventually crashes. I'm guessing my SQL is not correct and that's ultimately causing the problem.

Is the second SQL string above correct? Is there another way to write it?

Thanks in advance.

 

[synapsevampire]

Doesn't look right to me.

I suggest that you use Toad or one of the Oracle development tools to build out SQL first, as they will identify the offending code.

You might try:

SELECT DISTINCT
RPT_KM_ASSET_DATA.ASSET_ID
,RPT_KM_ASSET_DATA.OWNER_USER_QLID
,RPT_KM_ASSET_DATA.ASSET_DOWNLOAD_USER_QLID
,RPT_KM_ASSET_DATA.ASSET_TITLE
,DT1.dt1_published_date_time
FROM
RPT_KM_ASSET_DATA,
(SELECT
dt1_asset_id
,dt1_published_date_time
FROM
RPT_KM_ASSET_PUBLISHING
WHERE current_flag = 'Y') DT1
WHERE
RPT_KM_ASSET_DATA.asset_id =* DT1.dt1_asset_id
and
RPT_KM_ASSET_DATA.current_flag = 'Y'

My Oracle is a little rusty.

-k