All Apps and Add-ons

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

john_glasscock
Path Finder

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

0 Karma
1 Solution

adambloom
Path Finder

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.

View solution in original post

0 Karma

adambloom
Path Finder

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.

0 Karma

koshyk
Super Champion

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

0 Karma

john_glasscock
Path Finder

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

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

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?
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...