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
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
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
Quote from: andoid95 on 21 Jan 2015 06:55:22 AM
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.
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)