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

BKDRockz — Thu, 17 Oct 2024 16:40:54 GMT

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:

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?

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

gcusello — Thu, 17 Oct 2024 18:29:09 GMT

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

topic Re: Combining the results of two sql queries from two databases using join command not working in Getting Data In

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

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