Can not open the file that contains the SQL statement

esivaprasad · Post by **esivaprasad** » Thu Sep 20, 2012 7:54 am

Hi,

I am trying to run one job,but job is aborting with fatal error
"CopyOfjDMSQERunRate_Test..TMP_SQE_RUN_RATE: Can not open the file that contains the SQL statement".

After that i tried to view data in one ORAOCI8 stage, then i am getting the foolowing error
"CopyOfjDMSQERunRate_Test..TMP_SQE_RUN_RATE: Can not open the file that contains the SQL statement
CopyOfjDMSQERunRate_Test..TMP_SQE_RUN_RATE.DSLink1: DSP.Open GCI $DSP.Open error -100.".

Jon design:- source stage is oraoci 8 , target is hashed file, in between using the 2 transform stages and one oraoci 8 stage.till between oraoci 8 input data loading without any issue/warning.while reading data from same table , i am getting error which i mentioned above.

Please help me in this and let me know if you need any other details.

chulett · Post by **chulett** » Thu Sep 20, 2012 7:57 am

"Can not open the file that contains the SQL statement" is your issue. You've told the stage to get its SQL from a file and it can't find it.

esivaprasad · Post by **esivaprasad** » Thu Sep 20, 2012 8:29 am

But in hashed file input and output data loading/reading happening normally without issue.
only in intermediate oraci 8 stage, output view data is giving error that

"CopyOfjDMSQERunRate_Test..TMP_SQE_RUN_RATE: Can not open the file that contains the SQL statement
CopyOfjDMSQERunRate_Test..TMP_SQE_RUN_RATE.DSLink1: DSP.Open GCI $DSP.Open error -100.".

ArndW · Post by **ArndW** » Thu Sep 20, 2012 8:33 am

Hashed files don't use external SQL.
Can you post your SQL statement in stage "TMP_SQE_RUN_RATE" please?

esivaprasad · Post by **esivaprasad** » Thu Sep 20, 2012 8:54 am

same query, when i ran in toad, results coming without any warning/issue.

ArndW · Post by **ArndW** » Thu Sep 20, 2012 9:09 am

Siva, that is an important piece of information, but would be more useful if you could cut-and-paste the query from DataStage into this thread.

ray.wurlod · Post by **ray.wurlod** » Thu Sep 20, 2012 3:28 pm

This has got nothing at all to do with the query. For whatever reason DataStage (that is, the user ID under which DataStage job executes) is unable to open the file that contains the query.

ArndW · Post by **ArndW** » Fri Sep 21, 2012 3:22 am

Ray, that is why I asked the OP to post the query - so that we can then point out the file name and show that it isn't accessible.

esivaprasad · Post by **esivaprasad** » Fri Sep 21, 2012 5:28 am

Please find the query below.
SELECT
A1.PART_NUMBER,
A1.DUNS_NUMBER,
A1.RAR_ACTIVITY_CODE,
A1.GROUP_NAME,
A1.CUSTOMER_STATUS_ID,
A1.CUSTOMER_STATUS_DESC,
A1.SUPPLIER_UPDATE_ID,
A1.SUPPLIER_UPDATE_DESC,
A1.PENDING_FLAG,
A1.PART_SYSTEM_ADD_FLAG,
A1.QTC_OVERRIDE,
A1.LCR_OVERRIDE,
A1.QTC_HRS_OVERRIDE,
A1.RAR_SCHEDULE_SEQ,
B1.RAR_STATUS_ID,
B1.RAR_SCHEDULED_DATE,
B1.RAR_COMPLETE_DATE,
A1.CONDUCTED_DATE,
A1.NET_GOOD_PARTS,
A1.DURATION_HOURS,
A1.COMMENTS,
A1.EXEMPT_CODE,
A1.EXEMPT_OTHER_REMARK,
A1.DETAIL_SYSTEM_ADD_FLAG,
A1.RAR_STATUS_DESC
FROM
(
SELECT
X1.PART_NUMBER as PART_NUMBER,
X1.RAR_STATUS_ID AS RAR_STATUS_ID,
X1.COMPLETED_DATE as RAR_COMPLETE_DATE,
X2.STATUSDATE as RAR_SCHEDULED_DATE
from
(
SELECT PART_NUMBER, RAR_STATUS_ID, COMPLETED_DATE from DM.TMP_SQE_RUN_RATE a
where EXISTS (SELECT * FROM DM.TMP_SQE_RUN_RATE b
where a.PART_NUMBER=b.PART_NUMBER and a.created_date = (select max(created_date)
from DM.TMP_SQE_RUN_RATE c where c.PART_NUMBER=a.PART_NUMBER and c.RAR_STATUS_ID <> 3) )
) X1
LEFT OUTER JOIN
(
SELECT
BB.PART_NUMBER,
BB.STATUSDATE
from
(SELECT PART_NUMBER, MAX(COMPLETED_DATE) as COMPDATE, MAX(created_date) as CREATEDATE
from DM.TMP_SQE_RUN_RATE where RAR_STATUS_ID <> 3 GROUP by PART_NUMBER) AA,
(SELECT PART_NUMBER, MAX(SCHEDULED_DATE) as STATUSDATE, MAX(created_date) as CREATEDATE
from DM.TMP_SQE_RUN_RATE where RAR_STATUS_ID = 3 or RAR_STATUS_ID IS NULL GROUP by PART_NUMBER) BB
where
AA.PART_NUMBER=BB.PART_NUMBER and
BB.CREATEDATE > AA.CREATEDATE
) X2
ON
X1.PART_NUMBER = X2.PART_NUMBER
UNION

SELECT
X2.PART_NUMBER as PART_NUMBER,
X2.RAR_STATUS_ID AS RAR_STATUS_ID,
X1.COMPLETED_DATE as RAR_COMPLETE_DATE,
X2.STATUSDATE as RAR_SCHEDULED_DATE
from
(
SELECT PART_NUMBER, RAR_STATUS_ID, COMPLETED_DATE from DM.TMP_SQE_RUN_RATE a where EXISTS
(SELECT * FROM DM.TMP_SQE_RUN_RATE b where a.PART_NUMBER=b.PART_NUMBER
and a.created_date = (select max(created_date) from DM.TMP_SQE_RUN_RATE c
where c.PART_NUMBER=a.PART_NUMBER and c.RAR_STATUS_ID <> 3) )
) X1
RIGHT OUTER JOIN
(
SELECT
BB.PART_NUMBER,
-- IIF(IsNull(BB.RAR_STATUS_ID),"<Blank>",BB.RAR_STATUS_ID) as STATUS,
--DECODE(BB.RAR_STATUS_ID,NULL, '', BB.RAR_STATUS_ID) RAR_STATUS_ID,
nvl(BB.RAR_STATUS_ID, '') as RAR_STATUS_ID,
BB.STATUSDATE
FROM
(
SELECT PART_NUMBER, RAR_STATUS_ID, MAX(SCHEDULED_DATE) as STATUSDATE, MAX(created_date) as CREATEDATE
from DM.TMP_SQE_RUN_RATE where RAR_STATUS_ID = 3 or RAR_STATUS_ID IS NULL
GROUP by PART_NUMBER, RAR_STATUS_ID
) BB
WHERE
BB.PART_NUMBER not in (SELECT PART_NUMBER from DM.TMP_SQE_RUN_RATE where RAR_STATUS_ID <> 3 )
) X2
ON
X1.PART_NUMBER = X2.PART_NUMBER) B1,
DM.TMP_SQE_RUN_RATE A1
where A1.PART_NUMBER = B1.PART_NUMBER

Please let me know if you need any other details

ArndW · Post by **ArndW** » Fri Sep 21, 2012 5:33 am

Is that SELECT statement in your stage, or do you have "select statement from file" and this select is in the file?

esivaprasad · Post by **esivaprasad** » Fri Sep 21, 2012 6:11 am

This query is in oraoci8 stage.

chulett · Post by **chulett** » Fri Sep 21, 2012 6:59 am

Let's go back to a statement you made in passing...

Did you not say the job runs fine and this issue occurs only when you try to View Data from the OCI stage? If so then you seem to be firmly in Bugville and should be contacting your official support provider.

The only other thing I might suggest is right-clicking on the stage, selecting the old style 'Grid view' option and see if there are any stray entries in there. I've seen some odd things that only show up when you go 'under the GUI' to the property list view. You could also try deleting the stage from the job and setting it back up again so that it is 'clean' and seeing if the problem still exists.

chulett · Post by **chulett** » Fri Sep 21, 2012 7:03 am

I changed the subject of your post to state the proper issue.

ray.wurlod · Post by **ray.wurlod** » Fri Sep 21, 2012 3:13 pm

The error message suggests that the SQL is not in the OCI stage, but that the SQL File property has been used and DataStage cannot open that file.

chulett · Post by **chulett** » Fri Sep 21, 2012 3:21 pm

Right, we've all noted that fact by now. I'm looking for a clarification to the statement that the job actually runs fine with this issue only being seen when doing a View Data from the stage...