|
|
|
Forum Member
 
Group: Forum Members
Last Login: Yesterday @ 1:11:10 AM
Posts: 34,
Visits: 7
|
|
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
Manoj kp
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/24/2008 8:31:03 AM
Posts: 109,
Visits: 22
|
|
The following query will work:
select * from date_exmp where
req_dte between date-10 and date;
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: Yesterday @ 1:11:10 AM
Posts: 34,
Visits: 7
|
|
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
Manoj kp
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/7/2008 12:54:51 PM
Posts: 116,
Visits: 20
|
|
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.
Feroz Shaik
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 5/11/2006 7:20:00 PM
Posts: 24,
Visits: 1
|
|
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.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/9/2008 2:55:55 PM
Posts: 185,
Visits: 2
|
|
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
|
|
|
|