Archive

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 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_DR*(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_dr
end rev_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_DR*(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_dr
end rev_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

Tags (1)
0 Karma

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. :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.

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!