All Forums Database
08 Feb 2006
can insert the derived table in Between clause?

Hi All, I needed to get the dates for report at run time, the dates should be in some range, hence the "between" clause can be used. but I am unable to give the select statement in the "between" clause. as explained the query belowselect * from date_exmp wherereq_dte between (select date-10) and date;is not working, giving the following error:Code = 3706.3706: Syntax error: expected something between '(' and the 'select' keyword. Output directed to Answer set windowFYI: the same query is running when i m giving the req_dte using mathematical operators as:-wherereq_dte >= (select date-10) and req_dte <= date;IF any one know the solution kindly reply as early as possibleThanks

TD_Arch 35 posts Joined 07/05
08 Feb 2006

The following query will work:select * from date_exmp wherereq_dte between date-10 and date;

09 Feb 2006

Hi TD_ARch, Thanks for your reply, but my Actual requirement is that I wanted to put select statement in between clause, whether it is possible, please let me know, if not their is any specific reason for it?please let me know

09 Feb 2006

Manoj,I am not sure why it does not allow an select in the between clause, but it does not allow a select. The option that you had "where date_val >= (select .....) and date_val <= (select .....)" Infact this is what a between clause should resolve to. and the select query should retreive only one value or else you get a error : 3669 : More than one value was returned by a subquery.

Sunil Agrawal 12 posts Joined 07/04
17 Feb 2006

Why BETWEEN not working and comparison operator working is probably because of their syntax. Given below is thier syntax as given in Teradata manuals.1. Syntax of BETWEEN is: -expression1 BETWEEN expression2 AND expression 3 Here it does not expect subquery.2. Syntax of comparison operator is: -expression1 operator (subquery)Here the subquery is allowed at the right side of the comparison operator. You can alter your query "where (select date-10) <= req_dte and req_dte <= date;" it will not work as the subquery should be on the right of the operator.Hope this help. Anyway, you have already found the alternative to achive the final result.

Barry-1604 176 posts Joined 07/05
20 Feb 2006

You can code this as a derived table, rather than putting a subquery in the WHERE clause.Try this:select * from date_exmp a ,(select max(date - 10) derived_dt from some_table) bwhere req_dte between derived_dt and date;You can put your subquery in the derived table "b" above. Just be sure that your derived table returns only one row; otherwise you won't get the answer that you want.Thanks,Barry

You must sign in to leave a comment.