Parameterized query in Teradata stage

[Ansul29]

Hello All,

I have a to pull the delta data in Teradata. I have date field in my Teradata DB. But everytime I need to pass different date to my query to pull the data which is loaded after that particular date with timestamp. So I need to call that job every 5-10 mins and pull the delta data loaded within 5-10 mins.
I am able to run the query in teradata
select * from edf_billing_unit where chg_ts < timestamp '2016-10-24 16:30:04.100000';

so I want '2016-10-24 16:30:04.100000' this to be parameterized.
Any suggestion will help a lot.

[chulett]

So make it a job parameter and bind it into the source SQL by surrounding it with hash signs:

select [columns] from edf_billing_unit where chg_ts < timestamp '#p_TIMESTAMP#'

[Ansul29]

Hello Craig

Thanks for the inputs

I have used following query:
select lead_buid as value1, ' as value2, ' as value3, CHG_IND, 'BILLING_UNIT_VW1' AS TABLE_NAME, cast(cast(CHG_TS as date) as timestamp(0)) + (cast(CHG_TS as time(6)) - TIME '00:00:00' hour to second) as CHG_TS from edf_billing_unit where trim(lead_buid) is not null and chg_ts > timestamp '#Timestamp#'

Tried passing parameter:
1) 10/24/2016 16:30:03.100000
2) 10/24/2016 16:30:03

Getting following error
BillingUnitVw1: RDBMS code 3706: Syntax error: Invalid TimeStamp Literal. SQL statement: select lead_buid as value1, ' as value2, ' as value3, CHG_IND, 'BILLING_UNIT_VW1' AS TABLE_NAME, cast(cast(CHG_TS as date) as timestamp(0)) + (cast(CHG_TS as time(6)) - TIME '00:00:00' hour to second) as CHG_TS from edf_billing_unit where trim(lead_buid) is not null and chg_ts > timestamp '#Timestamp#' (CC_TeraDBStatement::prepare, file CC_TeraDBStatement.cpp, line 645)

I need to pass '10/24/2016 16:30:03.100000' as parameter.

[chulett]

The fact that it is not bound into your SQL implies to me that you either didn't create a job parameter with that name or they're not spelled exactly the same - case matters btw!

[Ansul29]

Looks like variable is ok (ie its exactly same i verified).. It has something to do with syntax. Timestamp I am passing is in correct format ?

[chulett]

Getting following error
BillingUnitVw1: RDBMS code 3706: Syntax error: Invalid TimeStamp Literal. SQL statement: select lead_buid as value1, ' as value2, ' as value3, CHG_IND, 'BILLING_UNIT_VW1' AS TABLE_NAME, cast(cast(CHG_TS as date) as timestamp(0)) + (cast(CHG_TS as time(6)) - TIME '00:00:00' hour to second) as CHG_TS from edf_billing_unit where trim(lead_buid) is not null and chg_ts > timestamp '#Timestamp#' (CC_TeraDBStatement::prepare, file CC_TeraDBStatement.cpp, line 645)

If the job parameter name matched, then it wouldn't still say '#Timestamp#' above in your error, it would have substituted it. Make sure you've defined it as a string and the name in-between the hashes matches the parameter name exactly. And for grins try something that isn't perhaps a reserved word, best practice is to use a "p_" prefix on your parameter names so perhaps try "p_Timestamp" as the name (in BOTH places) and see if that changes the behavior at all.

[Ansul29]

I tried changing the parameter name as well. But still the query shows parameter only 'P_timestamp'. do we need to use '#P_timestamp#' or #P_timestamp# ?

[chulett]

The quotes won't matter, it should work either way from what I recall. Did you try it? One thing you'll find is that DataStage loves to remove quotes from parameters so that could be an issue. However, there is an APT variable to stop that behavior if that's what you need to do - i.e. if you end up needing to pass the quotes in as part of the parameter value.

I suspect we're missing some critical piece of information that is preventing this from working, something needing "eyes on" to note perhaps. If no-one else has a suggestion you may need to involve your official support provider. I'd be curious what you find out. For all I know this is an issue with the Teradata stage itself, one I've never used. This really should be a very simple fundamental thing.

[Ansul29]

Hello,

I tried all the things and finally its worked the same way you advised. Only issue was parameter was in parameter set so it was expecting proper parameter same ie instead of 'P_timestamp#' it was expecting '#SaartToGdb.P_timestamp#' and it should be used with quotes without quotes its throwing some error.

[chulett]

Only issue was parameter was in parameter set

Ah... knew we were missing something. Kudos for figuring it out.