DB Connect 2 - Joining 2 tables from DB on same sever

[john_glasscock]

I am trying to use DB Connect 2 to pull data from two different tables in the same DB on the same server. So far I have been unsuccessful using the commands. Any help would be appreciated. Below is my example.

| dbxquery query="SELECT * FROM \"prod\".\"dbo\".\"TRANSACT_MONETARY\”"| join 'ACCOUNT_NBR' [ dbxquery query="SELECT * FROM \"prod\".\"dbo\".\"ACCOUNT\""] connection="DataMart" wrap=t

[adambloom · Accepted solution]

You should join in SQL. That way, you'll be able to setup a DB Input if you so desire. An example query for your join:

SELECT * FROM (
(SELECT * FROM prod.dbo.TRANSACT_MONETARY) t1 
join (SELECT * FROM prod.dbo.ACCOUNT) t2 
on t1.ACCOUNT_NBR=t2.ACCOUNT_NBR)

Note that this join is not particularly efficient if you are trying to filter in time - if you are trying to write a rising input make sure to use advanced mode and add a WHERE clause to the t1 query.

Notice the SELECT * FROM (...) structure to the query. Since it is already wrapped, dbx query wrapping can be disabled. Otherwise, you'll be double wrapping.

[koshyk]

on which column are you planning to join? please give sample of columns in TRANSACT_MONETARY AND ACCOUNT table

[john_glasscock]

I don't know Sql very well. I tried to use examples I found in here to get it to work, but nothing I have tried has worked. Can you reply with the SQL command that you think would work?

The wrap=t came from dbconnect when I selected automatic mode and pulled from one table. So, I figured that is needed for DBConnect 2.

Thanks,
John

[jcoates_splunk]

Hi,

1. if they're on the same system, why not join in SQL instead?
2. why does the second query have wrap=t on it?