regarding paramterised query in tm1

[angel1234]

hi,
i have created a parametrised query in ti process which is connecting to oracle .
i defined a parameter called year_num in TI.my query is


select
t1.account_category as account_category,
from
t1
where
t1.fiscal_year_number = @Year_num;

but the query is not working it shows a failure status in ti while i am clicking preview


thanks and regards
anjali.

[angel1234]

the query is working fine with the hard coded value for year_number=2010.but i have to implement it with a variable
which dynamically accepts the year

[Michel Zijlema]

Hi Anjali,

The right notation for using a TI parameter in the query text is either ?year_num? of '?year_num?', depending on data type.

Michel

[angel1234]

hi,
when i am using the following query

select
t1.account_category as account_category,
from
t1
where
t1.fiscal_year_number = ?Year_num?;

i am getting query failed error in tm1 while cilcking the preview .Can any suggest a solution for this?
thanks and regards,
angel

[Alan Kirk]

hi,
when i am using the following query

select
t1.account_category as account_category,
from
t1
where
t1.fiscal_year_number = ?Year_num?;

i am getting query failed error in tm1 while cilcking the preview .Can any suggest a solution for this?
thanks and regards,
angel

The query that's used to drive the the preview will use the default parameter(s) that you've specified in the Parameters tab.

Make sure that you have a valid default specified for the Year_Num parameter.

In fact, also make sure that you have specified Year_Num as a parameter in that tab, and haven't just used it in the query without having done so.

[angel1234]

hi,
still its not working

query is :
select * from table1 where field1=?P0?;
in advanced tab-->parameter tab
i have given parameter as P0
type as numeric
default value 1.0000
prompt question as P0

Kindly let me know any thing i have missed out


regards
Angel

[Alan Kirk]

hi,
still its not working

query is :
select * from table1 where field1=?P0?;
in advanced tab-->parameter tab
i have given parameter as P0
type as numeric
default value 1.0000
prompt question as P0

What's the exact data type of field1 in the database?

[MarioRubbo]

angel1234:
I have only been able to get this to work for me by treating everything as string. I had a similar circumstance and I had to cast my Year column as a string and then compare that to my parameter in TM1 which I defined as a string.

e.g.
where str(year_num,4) = '?year_num?'

[angel1234]

hi,
i am using ms acess db.The datatype for the column field1 is 'number'.



i modified the query like
select * from table1 where str(Field1,1)='?P0?' (still sql is getting failed)....

now i changed the default value to 1
and type of P0 to string.

Can any one give any other suggestions ????

regards
angel.

[MarioRubbo]

angel1234:
The str() function is the example I sent you is specific to the Sybase database that I am using. You need to substitute it with the appropriate datatype conversation function for Access.

[angel1234]

hiiiiiiiiiiiiiii
got it workedddddddddd.thanks for alllllllllllllllllllllllllllllllllllllllllll


regards
angel