Archive
Highlighted

Excute sql statement on splunk db connect

Explorer

Hi All,

am connected to oracle database & am trying to get data from it using splunk dbconnect,
amd trying to excute the below statement but it give error "Invalid Query
java.sql.SQLException: Missing IN or OUT parameter at index:: 1" while it working fine from oracle database, can any one provide me a solution for this issue?

SELECT concatenatedsegments "Account",accountdesc "Account Desc"
, round(sum(accounteddr),2) "Dr",round(sum(accountedcr),2) "Cr"
,

case
when concatenatedsegments=(select '01'|| '.' || rh.lossSEGMENT2|| '.' || rh.lossSEGMENT3|| '.' || rh.lossSEGMENT4|| '.' || rh.loss_SEGMENT5 loss

from GLREVALUATIONS rh
where rh.REVALUATION
ID=:REV11000
and rh.CHARTOFACCOUNTSID=:PCOAID)
then round(sum(rev
dr),2)+(-1*DGETREVALUATIONTBXLA(nvl(:ptodate,sysdate),:pledgerid,:PCOAID,:pfromaccount,:ptoaccount,:REV11000,(select CONVERSIONRATETYPE from GLREVALUATIONS where REVALUATIONID=:REV11000)))

else round(sum(rev_dr),2)

end "rev_Dr",

else round(sum(revcr),2)
end*/
round(sum(rev
cr),2) "rev_Cr"

FROM (
      --XLA
      SELECT 2 seq, cc.concatenated_segments,
             cc.segment2 || '.' || cc.segment3 || '.'
             || cc.segment4 jaccount,
                xxseg_descr.segment_desc
                                    ('SEGMENT2',
                                     cc.segment2,
                                     cc.chart_of_accounts_id
                                    )
             || '.'
             || xxseg_descr.segment_desc ('SEGMENT3',
                                          cc.segment3,
                                          cc.chart_of_accounts_id
                                         )
             || '.'
             || xxseg_descr.segment_desc ('SEGMENT4',
                                          cc.segment4,
                                          cc.segment3,
                                          cc.chart_of_accounts_id
                                         ) account_desc,
             l.accounting_date, ev.transaction_date,
             TO_CHAR (en.transaction_number) transaction_number,
             h.je_category_name doc_type, pp.je_source_name jsource,
             h.description, l.description line_desc, l.currency_code,
             l.currency_conversion_rate,
             NVL (l.entered_dr, -l.entered_cr) amount, 0 open_dr,
             0 open_cr
             , l.accounted_dr, l.accounted_cr
              ,0 open_cur_cr,0 open_cur_dr
              ,l.ENTERED_CR,l.ENTERED_DR
                      ,case 
             when  cc.concatenated_segments in 
             (select distinct cc1.CONCATENATED_SEGMENTS

from GLREVALUATIONS rh,
GL
REVALACCOUNTRANGES rl
, glcodecombinationskfv cc1
where
rh.REVALUATION
ID=rl.REVALUATION_ID

and rl.REVALUATIONID=:REV11000
and cc1.ENABLED
FLAG='Y'
and cc1.CHARTOFACCOUNTSID=:PCOAID
and cc1.segment3 between rl.SEGMENT3
LOW and rl.SEGMENT3HIGH
and cc1.segment4 between rl.SEGMENT4
LOW and rl.SEGMENT4HIGH
and cc1.segment2 between rl.SEGMENT2
LOW and rl.SEGMENT2HIGH
and cc1.segment1 between rl.SEGMENT1
LOW and rl.SEGMENT1HIGH
and cc1.segment5 between rl.SEGMENT5
LOW and rl.SEGMENT5HIGH
and cc1.ENABLED
FLAG='Y'
AND ( cc1.concatenatedsegments >= 😛fromaccount
AND cc1.concatenated
segments <= 😛toaccount
))
then ENTEREDDR*(Dgetrate(l.currencycode,tochar(:ptodate,'DD-MM-YYYY'),(select CONVERSIONRATETYPE from GLREVALUATIONS where REVALUATIONID=:REV11000)))
else accounted
dr
end revdr
,case
when cc.concatenated
segments in
(select distinct cc1.CONCATENATED_SEGMENTS

from GLREVALUATIONS rh,
GL
REVALACCOUNTRANGES rl
, glcodecombinationskfv cc1
where
rh.REVALUATION
ID=rl.REVALUATION_ID

and rl.REVALUATIONID=:REV11000
and cc1.ENABLED
FLAG='Y'
and cc1.CHARTOFACCOUNTSID=:PCOAID
and cc1.segment3 between rl.SEGMENT3
LOW and rl.SEGMENT3HIGH
and cc1.segment4 between rl.SEGMENT4
LOW and rl.SEGMENT4HIGH
and cc1.segment2 between rl.SEGMENT2
LOW and rl.SEGMENT2HIGH
and cc1.segment1 between rl.SEGMENT1
LOW and rl.SEGMENT1HIGH
and cc1.segment5 between rl.SEGMENT5
LOW and rl.SEGMENT5HIGH
and cc1.ENABLED
FLAG='Y'
AND ( cc1.concatenatedsegments >= 😛fromaccount
AND cc1.concatenated
segments <= 😛toaccount
))
then ENTEREDcR*(Dgetrate(l.currencycode,tochar(:ptodate,'DD-MM-YYYY'),(select CONVERSIONRATETYPE from GLREVALUATIONS where REVALUATIONID=:REV11000)))
else accounted
cr
end revcr
,(D
getrate(l.currencycode,tochar(:ptodate,'DD-MM-YYYY'),(select CONVERSIONRATETYPE from GLREVALUATIONS where REVALUATIONID=:REV11000))) rt
FROM xla
aeheaders h,
xla
aelines l,
gl
codecombinationskfv cc,
xlasubledgers pp,
xla
events ev,
xla.xlatransactionentities en
WHERE h.aeheaderid = l.aeheaderid
AND h.ledgerid = l.ledgerid
AND l.codecombinationid = cc.codecombinationid
AND pp.applicationid = h.applicationid
AND ev.eventid = h.eventid
AND ev.applicationid = h.applicationid
AND h.entityid = en.entityid
AND NVL (h.zeroamountflag, 'N') <> 'Y'

         AND l.ledger_id = :p_ledger_id
         AND (h.accounting_date >= :p_from_date OR :p_from_date IS NULL)
         AND (h.accounting_date <= :p_to_date OR :p_to_date IS NULL)

AND (cc.concatenatedsegments >= 😛fromaccount
AND cc.concatenated
segments <= 😛toaccount)
UNION ALL
--GL

SELECT 4 seq, cc.concatenatedsegments,
cc.segment2 || '.' || cc.segment3 || '.'
|| cc.segment4 jaccount,
xxseg
descr.segmentdesc
('SEGMENT2',
cc.segment2,
cc.chart
ofaccountsid
)
|| '.'
|| xxsegdescr.segmentdesc ('SEGMENT3',
cc.segment3,
cc.chartofaccountsid
)
|| '.'
|| xxseg
descr.segmentdesc ('SEGMENT4',
cc.segment4,
cc.segment3,
cc.chart
ofaccountsid
) accountdesc,
jl.effective
date, jh.defaulteffectivedate docdate,
NVL (TO
CHAR (jh.docsequencevalue),
jl.description
) docnumber,
jh.je
category jcategory, 'GL' jsource,
jh.description hdesc, jl.description linedesc,
jh.currencycode curr, jh.currencyconversionrate rate,
NVL (jl.entered
dr, -jl.enteredcr) entered
, 0 open
dr,0 opencr
, jl.accounted
dr acconteddr, jl.accountedcr
,jl.ENTEREDCR,jl.ENTEREDDR
,0 opencurcr,0 opencurdr
,case when jh.jesource='Revaluation'
then 0
when cc.concatenated
segments in
(select distinct cc1.CONCATENATED_SEGMENTS

from GLREVALUATIONS rh,
GL
REVALACCOUNTRANGES rl
, glcodecombinationskfv cc1
where
rh.REVALUATION
ID=rl.REVALUATION_ID

and rl.REVALUATIONID=:REV11000
and cc1.ENABLED
FLAG='Y'
and cc1.CHARTOFACCOUNTSID=:PCOAID
and cc1.segment3 between rl.SEGMENT3
LOW and rl.SEGMENT3HIGH
and cc1.segment4 between rl.SEGMENT4
LOW and rl.SEGMENT4HIGH
and cc1.segment2 between rl.SEGMENT2
LOW and rl.SEGMENT2HIGH
and cc1.segment1 between rl.SEGMENT1
LOW and rl.SEGMENT1HIGH
and cc1.segment5 between rl.SEGMENT5
LOW and rl.SEGMENT5HIGH
and cc1.ENABLED
FLAG='Y'
AND ( cc1.concatenatedsegments >= 😛fromaccount
AND cc1.concatenated
segments <= 😛toaccount
)
)
then ENTEREDDR*(Dgetrate(jh.currencycode,tochar(:ptodate,'DD-MM-YYYY'),(select CONVERSIONRATETYPE from GLREVALUATIONS where REVALUATIONID=:REV11000)))
else accounted
dr
end revdr
,case when jh.je
source='Revaluation'
then 0
when cc.concatenatedsegments in
(select distinct cc1.CONCATENATED
SEGMENTS

from GLREVALUATIONS rh,
GL
REVALACCOUNTRANGES rl
, glcodecombinationskfv cc1
where
rh.REVALUATION
ID=rl.REVALUATION_ID

and rl.REVALUATIONID=:REV11000
and cc1.ENABLED
FLAG='Y'
and cc1.CHARTOFACCOUNTSID=:PCOAID
and cc1.segment3 between rl.SEGMENT3
LOW and rl.SEGMENT3HIGH
and cc1.segment4 between rl.SEGMENT4
LOW and rl.SEGMENT4HIGH
and cc1.segment2 between rl.SEGMENT2
LOW and rl.SEGMENT2HIGH
and cc1.segment1 between rl.SEGMENT1
LOW and rl.SEGMENT1HIGH
and cc1.segment5 between rl.SEGMENT5
LOW and rl.SEGMENT5HIGH
and cc1.ENABLED
FLAG='Y'
AND ( cc1.concatenatedsegments >= 😛fromaccount
AND cc1.concatenated
segments <= 😛toaccount
))
then ENTEREDcR*(Dgetrate(jh.currencycode,tochar(:ptodate,'DD-MM-YYYY'),(select CONVERSIONRATETYPE from GLREVALUATIONS where REVALUATIONID=:REV11000)))
else accounted
cr
end revcr ,(Dgetrate(jh.currencycode,tochar(:ptodate,'DD-MM-YYYY'),(select CONVERSIONRATETYPE from GLREVALUATIONS where REVALUATIONID=:REV11000))) rt
FROM gl
jeheaders jh, gljelines jl,
gl
codecombinationskfv cc
WHERE jh.jeheaderid = jl.jeheaderid
AND ( jh.jesource IN ('Manual', '1', 'Revaluation','Closing Journal')
OR ( jh.je
source NOT IN ('Manual', '1', 'Revaluation','Closing Journal')
AND jh.reversedjeheaderid IS NOT NULL
)
)
AND cc.code
combinationid = jl.codecombination_id

AND (cc.concatenatedsegments >= 😛fromaccount
AND cc.concatenated
segments <= 😛toaccount)
AND jh.ledgerid = 😛ledgerid
AND (jl.effective
date >= 😛fromdate OR 😛fromdate IS NULL)
AND (jl.effectivedate <= 😛todate OR 😛todate IS NULL))
WHERE (currency
code = 😛curr OR 😛curr IS NULL)
AND (jsource = 😛source OR 😛source IS NULL)
AND ( concatenatedsegments >= 😛fromaccount
AND concatenated
segments <= 😛toaccount
)
AND (transactionnumber = 😛trxnumber OR 😛trxnumber IS NULL)
AND (amount = 😛
amount OR 😛amount IS NULL)
group by concatenated
segments,account_desc

ORDER BY concatenated_segments

Tags (1)
0 Karma
Highlighted

Re: Excute sql statement on splunk db connect

Builder

Your query includes bind variables which need values. That's what the "Missing IN or OUT parameter" error means. There's an example of building a parameterized query here http://docs.splunk.com/Documentation/DBX/3.0.2/DeployDBX/Commands. It should look something like this:

dbxquery query="select * from actor where actor_id > ? and actor_name = ?" connection="mysql" params="3,BOB"

You'll have to replace every ":p" parameter (e.g. 😛todate, 😛ledger_id, etc.) with a question mark and list out every occurrence of every value, in order. Given the size of your query that might be tricky. At the very least I believe that you will have to build a custom dashboard/report interface with fields to provide those values.

0 Karma