Passing parameters into a subquery

Database
Fan

Passing parameters into a subquery

Hello experts,

I am a new to TeraData, coming from mostly MS SQL (some Oracle and DB2 as well) exp. b.g.

While working on my current project I am trying to write a TD query that has a subquery where data is to be pulled from a reference table.

That reference table is being created every Friday and its name contains a date in "YYYYMMDD" format (for instance  "CustRating_20160311_ref" is a table name for one of Fridays in March).

If I hardcode a table name in a subquery like:

SEL CustID, CustFN, CustLN

FROM someDBname.CustTbl

WHERE CustID IN (SEL UserID FROM CustRating_20160311_ref) then everything seems to be working

But I need to pass a last Fryday date as a part of a tble name in that subquery to get the latest user ratings so that every time the query gets executed it would get Cust ratings from the latest Ratings reference table - something like "SEL UserID FROM CustRating_||(some-function-that-gets-Last-Friday-date-converted-to-CHAR-string-and-reversed)||_ref".

And this is where I am having really hard time bc it seems to be very different from SQL or Oracle, I am totally stuck.

Dear experts could you please help me ?

Any help would be greatly appreciated.

Best Regards,

Elsie

6 REPLIES
Enthusiast

Re: Passing parameters into a subquery

SEL 'CUSTRATING_'||CAST((TD_FRIDAY(DATE,NULL,NULL)(FORMAT 'YYYYMMDD')) AS VARCHAR(10))||'_REF';

Fan

Re: Passing parameters into a subquery

 SELECT Failed. 3707:  Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword between ')' and ';'.

Thank you very much yavaevergreen, it helped mefigure the concatenation part of the problem - execution of your script would corectly return a table name.

But now when I try to test that script of yours in the subquery like this :

SELECT TOP 10 UserID FROM (SEL 'CUSTRATING_'||CAST((TD_FRIDAY(DATE,NULL,NULL)(FORMAT 'YYYYMMDD')) AS VARCHAR(10))||'_REF');

I am getting Error 3707 as shown above in the top line

Could you please help?

Re: Passing parameters into a subquery

test

Enthusiast

Re: Passing parameters into a subquery

Since the table name is dynamical...Try the procedure...as shown below.

REPLACE PROCEDURE dbname.TEST1( )

BEGIN

DECLARE SQL1 VARCHAR(1000);

SET SQL1='INSERT INTO dbname.tablename SEL * from '||'dbname.'||'CUSTRATING_'||CAST((TD_FRIDAY(DATE,NULL,NULL)(FORMAT 'YYYYMMDD')) AS VARCHAR(10))||'_REF';

EXECUTE IMMEDIATE SQL1;

END

CALL dbname.TEST1( )

---Put your statements between BEGIN and END.

Fan

Re: Passing parameters into a subquery

Thank you yuvaevergreen, but I am sorry I think I am getting confused here ... I actually needed to execute SELECT using SUBSELECT from a table with dynamically created name ... rather than INSERT as your procedure shows. Am I not getting something here?

Enthusiast

Re: Passing parameters into a subquery

The reason I gave that suggestion is..you can plug in your entire query inside the chunk.But if it is just the select...Try this..Just the SQL needs to be modified according to your needs..

REPLACE PROCEDURE dbname.TEST1()

DYNAMIC RESULT SETS 1

BEGIN

DECLARE SQL1 VARCHAR(1000);

DECLARE S1 VARCHAR(1000);

DECLARE  C1 CURSOR WITH RETURN ONLY FOR S1;

SET SQL1='SEL * from '||'dbname.'||'CUSTRATING_'||CAST((TD_FRIDAY(DATE,NULL,NULL)(FORMAT 'YYYYMMDD')) AS VARCHAR(10))||'_REF';

PREPARE S1 FROM SQL1;

OPEN C1 ;

END

CALL dbname.TEST1( )