All Forums Database
JosieP 3 posts Joined 04/16
24 Apr 2016
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
 

yuvaevergreen 93 posts Joined 07/09
24 Apr 2016

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

JosieP 3 posts Joined 04/16
25 Apr 2016
 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?
 

srikanthdola 1 post Joined 03/10
25 Apr 2016

test

yuvaevergreen 93 posts Joined 07/09
25 Apr 2016

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.

JosieP 3 posts Joined 04/16
25 Apr 2016

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?

yuvaevergreen 93 posts Joined 07/09
26 Apr 2016

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( )

You must sign in to leave a comment.