Oracle SQL to Splunk Languages - Part 1

[syusjk6]

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??)

SELECT A.white,A.black,A.blue,A.oceanblue,A.yellow,A.red,A.gray,A.orange,A.margenta,B.purple,B.pink
,(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

FROM

INNER JOIN B ON A.white = B.white AND A.black = B.black
WHERE 1=1 AND A.gray <= COALESCE(B.DEL, B.orange)

UNION ALL
SELECT A.white,black,A.blue,A.oceanblue,A.yellow,A.red,A.gray,B.orange,B.margenta,B.purple,B.pink
,(TO_DATE(A.bluye, '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
,ROW_NUMBER() OVER (PARTITION BY A.white, A.black ORDER BY B.pink DESC, B.purple DESC) SEQ

FROM

INNER JOIN B
ON A.white = B.white AND A.black = B.black

WHERE 1=1 AND NVL(A.gray, '99991231') > COALESCE(B.DEL, B.orange)
)

[psanford_splunk]

This answer should be a good starting point for you: http://splunk-base.splunk.com/answers/32249/how-can-i-convert-sql-query-to-splunk-search

[gkanapathy]

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 replace(fieldname,"\r",""). CHR(10) is simply a newline character, \n. Your date conversions would be strptime(fieldname,"%Y%m%d") and strptime(fieldname,"%Y%m%d%H%M%S"). NVL(fieldname,value) can be written as coalesce(fieldname,value).TO_CLOBandROW_NUMBER` are meaningless in Splunk.

http://docs.splunk.com/Documentation/Splunk/4.2/SearchReference/CommonEvalFunctions

[syusjk6]

Oh, right.

[lguinn2]

There is no such thing as a "Character Large Object" in Splunk; therefore, there is no such thing as TO_CLOB.

Splunk events do not have row numbers, so ditto for ROW_NUMBER.

[syusjk6]

Wow, thanks a lot.
What you mean by "meaningliess in Splunk" about TO_CLOB and ROW_NUMBER ?

[lguinn2]

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:

• What do the incoming events look like? A sample line or two (sanitized of course) is always welcome.
• What output do you want? What are you trying to find or measure? English is probably your best choice here. A mock-up of what you want can be helpful, but is not usually needed.
• What have you already tried? What documentation, etc. have you already looked at? We don't want to waste time with solutions that you have already tried and rejected.

[Ayn]

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.

[syusjk6]

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.