can insert the derived table in Between clause?

Database
Enthusiast

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 below

select * from date_exmp where
req_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 window

FYI: the same query is running when i m giving the req_dte using mathematical operators as:-

where
req_dte >= (select date-10) and
req_dte <= date;

IF any one know the solution kindly reply as early as possible

Thanks
5 REPLIES
Enthusiast

Re: can insert the derived table in Between clause?

The following query will work:

select * from date_exmp where
req_dte between date-10 and date;
Enthusiast

Re: can insert the derived table in Between clause?

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

Enthusiast

Re: can insert the derived table in Between clause?

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.

Enthusiast

Re: can insert the derived table in Between clause?

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.
Enthusiast

Re: can insert the derived table in Between clause?

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) b
where 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