CODEWITHSUNDEEP
sql-serveroracle-databasecoldfusion
bman2013
bman2013

Reputation: 407

Combining MSSQL results with Oracle into CFSPREADSHEET

I have a report that generates an excel file daily with data extracted from a MS-SQL database. I now have to add additional columns to my spreadsheet from an Oracle database where the ID matches the ID in the MS-SQL query results.

My problem is that I have about 1200-1400+ unique IDs generated on this report from the first query. When I plug them into an IN list with the Oracle query and try to do a CFDUMP to see if the results will come out as it should, I receive a CF error saying that query cannot list more than 1000 results from the oracle query.

I basically set the values from the first query into a valuelist for the ID column and then put that into the IN clause for the Oracle query. I then do a cfdump on the Oracle where I receive that error. I've also tried wrapping cfloop query = "firstquery"> around the Oracle query and just placing #firstquery.columnIDname# but that does not work either.

So two questions I have here is ..

  1. How do I handle the limit on Oracle with 1k limit and if I only have read only access to the Oracle database with ColdFusion?

  2. After #1 is figured out, how could I combine the results from the Oracle Query with my MSSQL query or in other words, add the columns I'm pulling from the Oracle query to the spreadsheet for the matching ID.

Thanks.

Upvotes: 0

Views: 95

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20794

For your quick, dirty, and sub-optimal approach, visit cflib.org and look for a function called ListSplit(). It converts a long list to an array of short lists.

You then loop through this array and run a query each time. Make sure the query name changes with each loop iteration.

After the loop, do a query of queries union query. Then do whatever you have to do to combine that data with what you got from sql server.

Note that you will probably have to use array notation to access your dynamically named query objects.

Upvotes: 0

Related Questions