All Apps and Add-ons

im getting an error while running a sql query in db connect input

raghu0463
Explorer

im trying to run this query in splunk db connect input, in editor mode, the below
query with order by clause is giving the errors
if im running this query without the the order by clause its running perfectly

select distinct ca.FCRU_Rep_Num ,ca.Queue_Date, ca.Close_Date, ca.Sar_File_Date
from customer ca left join org or on ca.org = or.[Org]
order by DateDiff(day, ca.Queue_Date, GETDATE( ))

below is the error im getting

External search command 'dbxquery' returned error code 1. Script output = "RuntimeError: Failed to run query: "SELECT * FROM (select distinct ca.FCRU_Rep_Num ,ca.Queue_Date, ca.Close_Date, ca.Sar_File_Date from customer ca left join org or on ca.org = ro.[Org] order by DateDiff(day, ca.Queue_Date, GETDATE( ))) t", params: "None", caused by: Exception(' java.sql.SQLException: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified..',). "

0 Karma

tmuth_splunk
Splunk Employee
Splunk Employee

First step in debugging a query with DBX: run it in a non-Splunk tool. In this case, whatever tool you use for SQL Server (just guessing). I suspect you'll get the same error in this case.

Thy this:

select TOP 100 PERCENT distinct ca.FCRU_Rep_Num ,ca.Queue_Date, ca.Close_Date, ca.Sar_File_Date
from customer ca left join org or on ca.org = or.[Org] 
order by DateDiff(day, ca.Queue_Date, GETDATE( ))
0 Karma

raghu0463
Explorer

my doubt is does order by clause work in db connect ??
In some other cases where I'm executing sql queries in db connect which are having variables, CTE's are also giving errors.

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...