SQL to Splunk Query conversion

[aravindan_v]

The following SQL query needs a splunk query replacement.
We are facing difficulty with the conversion of sub-query.
Please Help!!

Select x.PNUM, (select distinct ad.ANUM
from TABL1 ad

where ad.PNUM = a.PNUM
and ad.ASNUM = (select min(adt.ASNUM)
from TABL1 adt
where adt.PNUM = a.PNUM
and adt.actno in ('A','B','BA')
and x.ASNUM < adt.ASNUM)) as ldl

from TABL2 a
inner join
TABL1 x
on x.PNUM = a.PNUM
WHERE x.actno in ('A','B');

[aravindan_v]

Below mentioned are the tables and the query used:

Table1

PNUM ANUM actno ASNUM

1001 3737 AD 585

1011 3737 L1 586

1111 3737 B1 587

1131 3737 NA 587

1002 3637 AB 588

table2

PNUM actno

2001 AD

2011 L1

2111 B1

2121 NA

1001 AD

1002 AB

Select x.PNUM, (select distinct ad.ANUM
from TABL1 ad
where ad.PNUM = a.PNUM
and ad.ASNUM = (select min(adt.ASNUM)
from TABL1 adt
where adt.PNUM = a.PNUM
and adt.actno in ('A','B','AB')
and x.ASNUM <= adt.ASNUM)) as ldl

from TABL2 a
inner join
TABL1 x
on x.PNUM = a.PNUM
WHERE x.actno in ('A','B','AB');

This needs exact splunk query replacement.
We used DB connect, but as per requirement splunk query will be our best fit.

we added database input to fetch data from external db.
will this suffice or any other setup is required?

[Ayn]

Read Drainy's answer.

[aravindan_v]

Ran the query using the DBconnect/DBQuery.
All that i needed to do was connect to the DB using dbconnect & then using the dbquery.
| dbquery "DB conn name" "Select query ...."
and the result came out like magic.

[MuS]

yeah, Splunk is sometimes pure magic 😉

[Drainy]

So... its not really a simple matter of "converting" a SQL query into Splunk. If you could provide some sample data and what you're trying to achieve then someone may be able to help.
It doesn't look too complicated though from what you've pasted above, what have you tried so far?