Combining the results of two sql queries from two databases using join command not working

[BKDRockz]

Hi Team,

I am fetching unique "ITEM" values from first sql query running on one database. Then passing those values to another sql query to fetch the corresponding values in the second database.

first SQL query:

select distinct a.item from price a, skus b, deps c,supp_country s
where zone_id in (5, 25)
and a.item = b.sku
and b.dept = c.dept
and a.item = s.item and s.primary_supp_ind = 'Y' and s.primary_pack_ind = 'Y'
and b.dept in
(7106, 1666, 1650, 1651, 1654, 1058, 4158, 4159, 489, 491, 492, 493, 495, 496, 497, 498, 499, 501, 7003, 502, 503, 7004, 450,
451, 464, 465, 455, 457, 458, 459, 460, 461, 467, 494, 7013, 448, 462, 310, 339, 7012, 7096, 200, 303, 304, 1950, 1951, 1952,
1970, 1976, 1201, 1206, 1207, 1273, 1352, 1274, 1969, 1987, 342, 343, 7107, 7098, 7095, 7104, 2101, 2117, 7107, 7098, 1990, 477,
162, 604, 900, 901, 902, 903, 904, 905, 906, 908, 910, 912, 916, 918, 7032, 919, 7110, 7093, 7101, 913, 915, 118, 119, 2701, 917)
and b.js_status in ('CO');

Second SQL:

WITH RankedData AS
(SELECT Product_Id,
BusinessUnit_Id,
Price,
LastUpdated,
ROW_NUMBER()
OVER (PARTITION BY Product_Id, BusinessUnit_Id
ORDER BY LastUpdated DESC) AS RowNum
FROM RETAIL.DBO.CAT_PRICE(nolock)
WHERE BusinessUnit_Id IN ('zone_5', 'zone_25')
AND Product_Id IN ($ITEM$) )
SELECT Product_Id,
BusinessUnit_Id,
Price,
LastUpdated
FROM RankedData
WHERE RowNum = 1;

When I am using map command as shown below, expected results are fetched but only 10k records as per map command limitations. But I want to to fetch all the records(around 30K)

Splunk query:

| dbxquery query="First SQL query" connection="ABC"
|eval comma="'"
|eval ITEM='comma' + 'ITEM' + 'comma'+","
|mvcombine ITEM
|nomv ITEM
|fields - comma
|eval ITEM=rtrim(tostring(ITEM),",")| map search="| dbxquery query=\"Second SQL query" connection=\"XYZ\""

But when i am using join command as shown below to get all the results(more than 10K), I am not getting the desired output. The output only contains results from first query.
I tried replacing the column name Product_Id in second sql with ITEM at all places, but still no luck.

| dbxquery query="First SQL query" connection="ABC"
|fields ITEM
| join type=outer ITEM[search dbxquery query=\"Second SQL query" connection=\"XYZ\""

Could someone help me in understanding what is going wrong and how can i get all the matching results from second query?

[gcusello · Accepted solution]

Hi @BKDRockz ,

I undertand that in this way you don't consume license but using dbxquery in searches isn't the best approach to extract data from a database because the db-connect is a very slow extracting tool.

The best approach is to extract data separately using both the queries saving results in an index and then using the indexed data for a search.

In addition don't use join because it's a very slow command: you can dind in Community many examples of correlation searches.

I hint to redesign your ingestion and search process.

Ciao.

Giuseppe