Hi, I'm working with some Oracle SQL as follows :
(And I want sql below expressed in splunk languages. The bold typed commands or functions are to be converted to splunk languages... Any suggestions??)
,(TO_DATE(A.gray, 'YYYYMMDD') - TO_DATE(B.pink, 'YYYYMMDDHH24MISS')) AS DIFF_DAYS
,TO_CLOB(REPLACE(NVL(B.B1, ' '), CHR(13), '')
|| CASE WHEN B.B2 IS NOT NULL THEN CHR(10) || REPLACE(B.B2, CHR(13), '') END
|| CASE WHEN B.B3 IS NOT NULL THEN CHR(10) || REPLACE(B.B3, CHR(13), '') END
|| CASE WHEN B.B4 IS NOT NULL THEN CHR(10) || REPLACE(B.B4, CHR(13), '') END
|| CASE WHEN B.B5 IS NOT NULL THEN CHR(10) || REPLACE(B.B5, CHR(13), '') END
|| CASE WHEN B.B6 IS NOT NULL THEN CHR(10) || REPLACE(B.B6, CHR(13), '') END
|| CASE WHEN B.BCM IS NOT NULL THEN CHR(10) || REPLACE(B.BCM, CHR(13), '') END) RSLTCNT
,-1 * ROW_NUMBER() OVER (PARTITION BY A.white, A.black ORDER BY COALESCE(B.DEL, B.margenta), B.purple ) SEQ
It would take a lot more than simply converting the functions you have bolded to make a meaningful (or even usable) Splunk query. I'm not sure what the point of simply converting those, and if someone is having trouble with those simple functions, it is quite unlikely that they are are able to express the overall SQL query in Splunk Processing Language. But to give you the narrowest (and I suspect, ultimately unsatisfying) answer to your question:
Look at the
eval search command here. There is a
replace() function and
strptime() conversion function. Your
REPLACE(fieldname,CHR(13),'') can simply be expressed as
CHR(10) is simply a newline character,
\n. Your date conversions would be
NVL(fieldname,value) can be written as
andROW_NUMBER` are meaningless in Splunk.
First, Splunk is not SQL. So a direct translation may not be possible. And it probably isn't desirable, as Splunk works differently.
Second, a lot more people on this forum know Splunk rather than SQL. So a lot of us can't translate this even if we want to help.
Finally, here are some suggestions for getting help. We would like to know just a few things:
It's not a matter of how many direct mappings "Splunkers" can find. Splunk's search language is not SQL, and so it works differently so even if you found a bunch of mappings it wouldn't be very relevant anyway. It's a bit like taking a code snippet in a procedural language and ask someone to convert individual lines into equivalents in a functional language - it just doesn't make sense.
I'm confused. It seems that the ones who utilize Splunk in whichever domain, in this case, Database Search Queries to fit into won't be necessary considering in mapping/expressing/translating/converting to Splunk search languages...? Whether or not how Splunk works differently from SQL, shouldn't Splunkers understand direct/indirect mapping from SQL to Splunk Search Languages or viceversa? Not that I am ignorant of the power of manipulating splunk search languages, though.