Can not open the file that contains the SQL statement
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 135
- Joined: Tue Dec 09, 2008 10:35 am
Can not open the file that contains the SQL statement
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.
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.
Siva
-
- Participant
- Posts: 135
- Joined: Tue Dec 09, 2008 10:35 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.".
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.".
Siva
Hashed files don't use external SQL.
Can you post your SQL statement in stage "TMP_SQE_RUN_RATE" please?
Can you post your SQL statement in stage "TMP_SQE_RUN_RATE" please?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 135
- Joined: Tue Dec 09, 2008 10:35 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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 135
- Joined: Tue Dec 09, 2008 10:35 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
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
Siva
Is that SELECT statement in your stage, or do you have "select statement from file" and this select is in the file?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 135
- Joined: Tue Dec 09, 2008 10:35 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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: