HI,
I have requirement such that client will be giving inputs to the bind variables such as unit ,etc.
we have used nvl function to say if client is not enter any value it fetch record.
code below:
select distinct
gl.Name "LEDGER_NAME",
gcc.CHART_OF_ACCOUNTS_ID,
gcc.CONCATENATED_SEGMENTS,
gcc.Segment1 "UNIT",
gcc.Segment2 "ACCOUNT",
gcc.Segment3 "DEPARTMENT",
gcc.Segment4 "LEGAL_ENTITY",
gcc.Segment5 "LOCATION",
gcc.Segment6 "FUTURE",
gcc.Segment7 "INTER_COMPANY",
gcc.Attribute1 "LEGACY_UNIT",
gcc.Attribute2 "LEGACY_ACCOUNT",
gcc.Attribute3 "LEGACY_SUB_ACCOUNT",
gcc.ENABLED_FLAG
from apps.gl_code_combinations_kfv gcc, apps.gl_ledgers gl
where gcc.chart_of_accounts_id = gl.chart_of_accounts_id
and gcc.segment1 = NVL(:UNIT,segment1)
and gcc.segment2 = NVL(:ACCOUNT,segment2)
and gcc.segment3 = NVL(:DEPARTMENT,segment3)
and gcc.segment4 = NVL(:LEGAL_ENTITY,segment4)
and gcc.segment5 = NVL(:LOCATION,segment5)
and gcc.segment6 = NVL(:FUTURE,segment6)
and gcc.segment7 = NVL(:INTER_COMPANY,segment7)
and gl.name = NVL(:LEDGER_NAME,name);
Now requirement
client want to give input as strings such as unit 12,14
department 23,24
etc..
initially
I tried doing
like
select
:
:
from
from apps.gl_code_combinations_kfv gcc, apps.gl_ledgers gl
where gcc.chart_of_accounts_id = gl.chart_of_accounts_id
and gcc.segment1 in(&unit)
::
::
and gl.name in(&name)
in that case it is allowing to enter string give the values.
but requirement is such that nvl function have to be given
such a s
select
:
:
from apps.gl_code_combinations_kfv gcc, apps.gl_ledgers gl
where gcc.chart_of_accounts_id = gl.chart_of_accounts_id
and gcc.segment1 in( Nvl(&unit),segment1)
..
..
and gl.name in(nvl(&name),name)
it show errors
we will have input string as well as nvl function
please help me in this>>
Thanks in advance.