CODEWITHSUNDEEP
coldfusion
Abhishek Raj
Abhishek Raj

Reputation: 51

How Can I use Two DataSources in a CFQUERY

 <cfquery name="checkinvoice" datasource="?">
    SELECT DISTINCT INVOICE_DATE,DEPARTMENT,VALUE,INVOICE_NO 
    FROM   ro_invoice 
    WHERE  sheet_no='#newROSheet.sheetNo#'
    MINUS
    SELECT DISTINCT INVOICE_DATE,DEPARTMENT,VALUE,INVOICE_NO 
    FROM   dbowner.rs_invoices 
    WHERE  rs_id ='#recordSheet.id#'
</cfquery>

In the above code the 1st select statement belongs to datasource1 and the 2nd to datasource2 (in oracle). How can I use cfquery to execute the two statements?

Upvotes: 1

Views: 1495

Answers (3)

genericHCU
genericHCU

Reputation: 4446

If both databases are Oracle (it isn't clear in your question) you can create a database link and query both databases as if they were one. This link is done in oracle so a "ColdFusion solution" isn't required. Oracle has documentation on linking databases here. Accessing and Modifying Information in Multiple Databases We've used this several times during data migration from legacy to new systems. It works well. You can also use insert, update, and delete

Once the tables are linked using it is easy, using the @dblink syntax.

<cfquery ...>
    select
        a.column1
      , b.column2
    from
      someTable@dblinkName a -- I came from the linked database
      inner join someOtherTable b on b.someColumn = a.someColumn
</cfquery>

Upvotes: 4

Dan Bracuk
Dan Bracuk

Reputation: 20804

There are a couple of ways to do this. One is with query of queries. However, based on your question, it's not clear if a union query is what you want. You might want something like this:

Query 1

select yourfields
, concat(your fields together, casting as string where necessary) concatfield
etc

Query 2 is the same as Query 1 but from the other database. Query 3 is

select yourfields
from Query1
where concatfield not in (<cfqueryparam 
    value="#ValueList(Query2.concatfield#" list="yes">

The other way is as Henry suggested. There are ways to write queries from database 1 that include tables from database 2. In fact they don't even have to be the same type of database. For example, you can set up linked servers on Sql Server and query oracle databases. However, the syntax depends on the RDBMSs and you didn't specify yours.

Upvotes: 2

Carl Von Stetten
Carl Von Stetten

Reputation: 1149

The short answer is that you can't. However, you can do two separate CFQUERYs, then join the results in a third CFQUERY as a query-of-query with a UNION operation (as long as the columns in both of the original CFQUERYs are equivalent). However, based on the query you showed, you want to remove the results of the second datasource from the results of the first datasource. This cannot be done within a CFQUERY statement.

Upvotes: 0

Related Questions