Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

I have two SQL statements in my code to pull data from diffe...


Debra Waybright

Recommended Posts

I have two SQL statements in my code to pull data from different tables. Can I join them Im getting an error that the access file record is absent for SQLOUT.

Here is basically what I am doing:

ENGINE SQLMSS SET DEFAULT_CONNECTION SQL_Reporting

SQL SQLMSS PREPARE PRO FOR

select key_field, some_other_field

from this_table

END

ENGINE SQLMSS SET DEFAULT_CONNECTION SQL_Reporting

SQL SQLMSS PREPARE SQLOUT FOR

select key_field, some_field

from that_table

END

JOIN FILE SQLOUT AT SQLOUT.key_field TO UNIQUE FILE PRO AT PRO.key_field TAG JS1

END

TABLE FILE SQLOUT

PRINT *

END

Thanks for any insight,

Deb

Link to comment
Share on other sites

Usually it would be better to do the join in SQL Server.

I think the issue with your code is that the Reporting Server doesnt send the SQL to be run and create Hold files until there is a TABLE FILE on sqlout or pro. I tried it with a little bit different syntax, this code worked for me:

ENGINE SQLMSS SET DEFAULT_CONNECTION SQL_Reporting

 

SQL SQLMSS

SELECT name,create_date FROM sys.all_views ;

TABLE ON TABLE HOLD AS sqlout

END

 

SQL SQLMSS

SELECT name,modify_date FROM sys.all_views ;

TABLE ON TABLE HOLD AS pro

END

 

JOIN name IN sqlout TO UNIQUE

name IN pro AS JOIN1

END

 

TABLE FILE sqlout

PRINT name create_date modify_date

END

For larger files, consider creating an index on the file you are joining to.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...