Excute sql statement on splunk db connect

MAShawky — Tue, 29 Sep 2020 13:19:35 GMT

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 concatenated_segments "Account",account_desc "Account Desc"
, round(sum(accounted_dr),2) "Dr",round(sum(accounted_cr),2) "Cr"
,

case
when concatenated_segments=(select '01'|| '.' || rh.loss_SEGMENT2|| '.' || rh.loss_SEGMENT3|| '.' || rh.loss_SEGMENT4|| '.' || rh.loss_SEGMENT5 loss

from GL_REVALUATIONS rh
where rh.REVALUATION_ID=:REV11000
and rh.CHART_OF_ACCOUNTS_ID=:P_COA_ID)
then round(sum(rev_dr),2)+(-1*D_GET_REVALUATION_TB_XLA(nvl(:p_to_date,sysdate),:p_ledger_id,:P_COA_ID,:p_from_account,:p_to_account,:REV11000,(select CONVERSION_RATE_TYPE from GL_REVALUATIONS where REVALUATION_ID=:REV11000)))

else round(sum(rev_dr),2)

end "rev_Dr",

else round(sum(rev_cr),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 GL_REVALUATIONS rh,
GL_REVAL_ACCOUNT_RANGES rl
, gl_code_combinations_kfv cc1
where
rh.REVALUATION_ID=rl.REVALUATION_ID

and rl.REVALUATION_ID=:REV11000
and cc1.ENABLED_FLAG='Y'
and cc1.CHART_OF_ACCOUNTS_ID=:P_COA_ID
and cc1.segment3 between rl.SEGMENT3_LOW and rl.SEGMENT3_HIGH
and cc1.segment4 between rl.SEGMENT4_LOW and rl.SEGMENT4_HIGH
and cc1.segment2 between rl.SEGMENT2_LOW and rl.SEGMENT2_HIGH
and cc1.segment1 between rl.SEGMENT1_LOW and rl.SEGMENT1_HIGH
and cc1.segment5 between rl.SEGMENT5_LOW and rl.SEGMENT5_HIGH
and cc1.ENABLED_FLAG='Y'
AND ( cc1.concatenated_segments >= :p_from_account
AND cc1.concatenated_segments <= :p_to_account
))
then ENTERED_cR*(D_get_rate(l.currency_code,to_char(:p_to_date,'DD-MM-YYYY'),(select CONVERSION_RATE_TYPE from GL_REVALUATIONS where REVALUATION_ID=:REV11000)))
else accounted_cr
end rev_cr
,(D_get_rate(l.currency_code,to_char(:p_to_date,'DD-MM-YYYY'),(select CONVERSION_RATE_TYPE from GL_REVALUATIONS where REVALUATION_ID=:REV11000))) rt
FROM xla_ae_headers h,
xla_ae_lines l,
gl_code_combinations_kfv cc,
xla_subledgers pp,
xla_events ev,
xla.xla_transaction_entities en
WHERE h.ae_header_id = l.ae_header_id
AND h.ledger_id = l.ledger_id
AND l.code_combination_id = cc.code_combination_id
AND pp.application_id = h.application_id
AND ev.event_id = h.event_id
AND ev.application_id = h.application_id
AND h.entity_id = en.entity_id
AND NVL (h.zero_amount_flag, '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.concatenated_segments >= :p_from_account
AND cc.concatenated_segments <= :p_to_account)
UNION ALL
--GL

SELECT 4 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,
jl.effective_date, jh.default_effective_date doc_date,
NVL (TO_CHAR (jh.doc_sequence_value),
jl.description
) doc_number,
jh.je_category jcategory, 'GL' jsource,
jh.description h_desc, jl.description line_desc,
jh.currency_code curr, jh.currency_conversion_rate rate,
NVL (jl.entered_dr, -jl.entered_cr) entered
, 0 open_dr,0 open_cr
, jl.accounted_dr acconted_dr, jl.accounted_cr
,jl.ENTERED_CR,jl.ENTERED_DR
,0 open_cur_cr,0 open_cur_dr
,case when jh.je_source='Revaluation'
then 0
when cc.concatenated_segments in
(select distinct cc1.CONCATENATED_SEGMENTS

from GL_REVALUATIONS rh,
GL_REVAL_ACCOUNT_RANGES rl
, gl_code_combinations_kfv cc1
where
rh.REVALUATION_ID=rl.REVALUATION_ID

and rl.REVALUATION_ID=:REV11000
and cc1.ENABLED_FLAG='Y'
and cc1.CHART_OF_ACCOUNTS_ID=:P_COA_ID
and cc1.segment3 between rl.SEGMENT3_LOW and rl.SEGMENT3_HIGH
and cc1.segment4 between rl.SEGMENT4_LOW and rl.SEGMENT4_HIGH
and cc1.segment2 between rl.SEGMENT2_LOW and rl.SEGMENT2_HIGH
and cc1.segment1 between rl.SEGMENT1_LOW and rl.SEGMENT1_HIGH
and cc1.segment5 between rl.SEGMENT5_LOW and rl.SEGMENT5_HIGH
and cc1.ENABLED_FLAG='Y'
AND ( cc1.concatenated_segments >= :p_from_account
AND cc1.concatenated_segments <= :p_to_account
))
then ENTERED_cR*(D_get_rate(jh.currency_code,to_char(:p_to_date,'DD-MM-YYYY'),(select CONVERSION_RATE_TYPE from GL_REVALUATIONS where REVALUATION_ID=:REV11000)))
else accounted_cr
end rev_cr ,(D_get_rate(jh.currency_code,to_char(:p_to_date,'DD-MM-YYYY'),(select CONVERSION_RATE_TYPE from GL_REVALUATIONS where REVALUATION_ID=:REV11000))) rt
FROM gl_je_headers jh, gl_je_lines jl,
gl_code_combinations_kfv cc
WHERE jh.je_header_id = jl.je_header_id
AND ( jh.je_source IN ('Manual', '1', 'Revaluation','Closing Journal')
OR ( jh.je_source NOT IN ('Manual', '1', 'Revaluation','Closing Journal')
AND jh.reversed_je_header_id IS NOT NULL
)
)
AND cc.code_combination_id = jl.code_combination_id

AND (cc.concatenated_segments >= :p_from_account
AND cc.concatenated_segments <= :p_to_account)
AND jh.ledger_id = :p_ledger_id
AND (jl.effective_date >= :p_from_date OR :p_from_date IS NULL)
AND (jl.effective_date <= :p_to_date OR :p_to_date IS NULL))
WHERE (currency_code = :p_curr OR :p_curr IS NULL)
AND (jsource = :p_source OR :p_source IS NULL)
AND ( concatenated_segments >= :p_from_account
AND concatenated_segments <= :p_to_account
)
AND (transaction_number = :p_trx_number OR :p_trx_number IS NULL)
AND (amount = :p_amount OR :p_amount IS NULL)
group by concatenated_segments,account_desc

ORDER BY concatenated_segments

Re: Excute sql statement on splunk db connect

pmdba — Tue, 29 Sep 2020 13:21:23 GMT

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. :p_to_date, :p_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.

topic Re: Excute sql statement on splunk db connect in Splunk Search

Excute sql statement on splunk db connect

Re: Excute sql statement on splunk db connect