How to use param value in data item

[andoid95]

hello !

Since yesterday i have this problem.
This line return empty and i don't know why ---> to_number(?p_Year?)
i use also to_number(ParamValue('p_Year')) and it also doesn't work

thks

[charon]

Hi Android,
did this work before? 'to_number', i guess ure working with an oracle DB?
May i ask what you are trying to achieve? You might know the difference between "paramValue" and "paramDisplayValue" i assume, so how does the primary Key of Year looks like?
regards charon

[andoid95]

Yes i use oracle like database, i want to extract all data between previous yearMonth and the yearMonth given in prompt so i do :
IF (  to_number(substr(?p_Period?,5,2))+2 <=12 and

(
([Q_Join12].[Mois]=to_char(to_number(?p_Year?)-1) || '0'|| to_char(to_number(substr(?p_Period?,5,2))+2)) OR  ([Q_Join12].[Mois]=to_char(to_number(?p_Year?)-1) ||  to_char(to_number(substr(?p_Period?,5,2))+2))
)
  ) THEN

    ( [Q_Join12].[COUNCS] )

ELSE IF ([Q_Join12].[Mois]=?p_Year? || '0' ||   to_char((to_number(substr(?p_Period?,5,2))+2)-12)   )
THEN
    ( [Q_Join12].[COUNCS] )

ELSE (0)

but it always return 0

[MFGF]

Yes i use oracle like database, i want to extract all data between previous yearMonth and the yearMonth given in prompt so i do :
IF (  to_number(substr(?p_Period?,5,2))+2 <=12 and

(
([Q_Join12].[Mois]=to_char(to_number(?p_Year?)-1) || '0'|| to_char(to_number(substr(?p_Period?,5,2))+2)) OR  ([Q_Join12].[Mois]=to_char(to_number(?p_Year?)-1) ||  to_char(to_number(substr(?p_Period?,5,2))+2))
)
  ) THEN

    ( [Q_Join12].[COUNCS] )

ELSE IF ([Q_Join12].[Mois]=?p_Year? || '0' ||   to_char((to_number(substr(?p_Period?,5,2))+2)-12)   )
THEN
    ( [Q_Join12].[COUNCS] )

ELSE (0)

but it always return 0

Yuk!!! That looks horribly complex!

Can you explain what data type and format the Mois item is? What are you prompting for in p_Year and p_Period (ie what formats). What exactly do you mean by "previous yearMonth" and "yearMonth" - ie what result are you seeking? If you can give us these details we can try to figure out a simpler solution...

MF.

[Robl]

It looks like your prompt p_period is in the format yyyymm
so, 201406
I'm assuming the 'mois' data item is the same format.
Your filter could easily be

mois < ?P_period?
and
mois > (?p_period? - 100)