×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Remote & Non Remote join

Remote & Non Remote join

Remote & Non Remote join

(OP)
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,

RE: Remote & Non Remote join

(OP)
Thanks

RE: Remote & Non Remote join

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

RE: Remote & Non Remote join

(OP)
Thank you so much. You have saved my life

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close