[EMAIL PROTECTED] wrote: > Hi! > > I need to execute a query that returns all debts of a client > over 180 days. > > I'm trying something like > > select * from MYTABLE > where clientid = 1 > and dueDate > addDate(select DATE from DUAL, 180) > > but I always get an error: > > General error;-7056 POS(81) Subquery not allowed.
A common problem, the subquery itself needs brackets around. The function needs brackets for its own, --> 2 brackets are needed > > and if I execute > > select * from MYTABLE > where clientid = 1 > and dueDate > addDate((select DATE from DUAL), 180) > > > or execute > > select * from MYTABLE > where clientid = 1 > and dueDate > addDate((select max(DATE) from DUAL), 180) > > I get > > Integrity constraint violation;-8006 POS(73) Data types must > be compatible. As we saw with another mail dueDate is a timestamp, therefore addDate ((select timestamp from dual), 180) had been ok, but as written in another answer addDate (timestamp, 180) is shorter and better (avoiding subquery) Please see http://www.sapdb.org/7.4/htmhelp/15/1a2bb4b44d11d2a97100a0c9449261/frameset.htm which 'values' can be used where any value can be given, even as parameter for functions. Elke SAP Labs Berlin > > If I try > > select * from MYTABLE, DUAL > where clientid = 1 > and dueDate > addDate(DUAL.DATE, 180) > > I get > > Column not found;-4005 POS(87) Unknown column name:DATE. > > > What should I do? > > Thanks for any tips. > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
