Remote & Non Remote join

[JoaoPinto]

Hi,

I was wondering if anyone could help me out with the following.

I am trying to retrieve data from a remote SQL server database and a non remote Oracle database, then combine the results. So far I've done it like so

******************************************************
REMOTE DEST=CI414R45

-MRNOEDIT BEGIN
-REMOTE BEGIN

SET SQLENGINE=SQLMSS
SQL SQLMSS
select acd.ANI , acd.DateTime
from PEDAHR.dbo.ATS_Call_Detail acd,
PEDAHR.dbo.ATS_Call_Variable acv
where
acv.TCDRecoveryKey = acd.RecoveryKey and
acd.DateTime between '01/27/03 08:00:00' and '01/31/03 18:50:00' and
acv.ExpandedCallVariableID = 5028 and
convert(varchar(10),acd.DateTime,108) between '08:00:00' and '18:50:00' and
acv.ATSValue in ('CH','ST','WD')
Group by acd.ANI , acd.DateTime
order by ACD.ANI , acd.DateTime;

TABLE FILE SQLOUT
PRINT *
ON TABLE PCHOLD AS SQLHOLD
END

-REMOTE END
-MRNOEDIT END

SQL
SELECT aga_roloc, aga_terr_mgr, aga_agt_mgr, aga_agent_first_nm,
aga_agent_last_nm, aga_agent_nbr, aga_agent_email_id, aga_asc_start_dt,
loa_loc_time_zone, apy_auto_nbr_id, loa_state, loa_sales_loc_cd, loa_region_cd
FROM CPAGTINF,
ATSLSLOC,
CPPRIRTY
WHERE aga_agent_nbr = apy_agent_nbr
and loa_region_cd = '002'
and AGA_TERR_MGR = 'A'
and apy_roloc = loa_roloc;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOR
END

SQL
SELECT *
FROM SQLHOLD, SQLOR
WHERE SQLHOLD.ANI = SQLOR.APY_AUTO_NBR_ID;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLFIN
END
******************************************

TABLE FILE SQLFIN
PRINT *
END

I'm getting the error Message
********************************************************
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27

1
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0
0 ERROR AT OR NEAR LINE 37 IN PROCEDURE FOCSQL02FOCEXEC *
(FOC012) THE WORD 'FILE' OR THE FILENAME APPEARS TWICE
BYPASSING TO END OF COMMAND
0 ERROR AT OR NEAR LINE 87 IN PROCEDURE _test FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLFIN
BYPASSING TO END OF COMMAND

**********************************************************


However if I change the last section to this

SQL
SELECT *
FROM SQLHOLD, SQLOR
WHERE SQLHOLD.ANI = SQLOR.APY_AUTO_NBR_ID;
END

And don't use another hold file I get my data display correctly. The problem is that this way I can't add any Stylesheet formating.

Can anyone help?

Thanks,

 

[JoaoPinto]

Thanks :-(

 

[GrSw]

This is my test (should also run on your Oracle):

SET ASNAMES=ON
SQL SQLORA
SELECT 1 ID, 'SOMETHING' INFO FROM DUAL;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOR
END

SQL
SELECT * FROM SQLOR;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLFIN
END

TABLE FILE SQLFIN
PRINT *
END

Results in the same errors: (FOC012).

But when using this syntax:

SET ASNAMES=ON
SQL SQLORA
SELECT 1 ID, 'SOMETHING' INFO FROM DUAL;
TABLE
ON TABLE HOLD AS SQLOR
END

SQL
SELECT * FROM SQLOR;
TABLE
ON TABLE HOLD AS SQLFIN
END

TABLE FILE SQLFIN
PRINT *
END

everything works well.

Hope this helps
Grzegorz

 

[JoaoPinto]

Thank you so much. You have saved my life