When using "Send to query builder" option with an IN Clause, the "Global Where Clause" has been modified
Steps to reproduce the issue:
1)Run script below:
create table scott.test_visual_querys (code number, date1 date);
insert into scott.test_visual_querys (code , date1) values (1, trunc(sysdate) -30);
insert into scott.test_visual_querys (code , date1) values (2, trunc(sysdate) -25);
insert into scott.test_visual_querys (code , date1) values (3, trunc(sysdate) -20);
insert into scott.test_visual_querys (code , date1) values (4, trunc(sysdate) -15);
insert into scott.test_visual_querys (code , date1) values (5, trunc(sysdate) -10);
insert into scott.test_visual_querys (code , date1) values (6, trunc(sysdate) -5);
commit;
2) open a new "SQL Editor" with this query:
Select *
from scott.test_visual_querys t
where t.date1 in (:date_parameter, to_date('21/03/2014','dd/mm/yyyy'));
3) Control+d or right click "Advanced, send to query builder":
4) Edit the Global Where Clause:
t.DATE1 IN (':date_parameter', 'to_date(''21/03/2014''', '''dd/mm/yyyy'')')
It has changed from :
to_date('21/03/2014','dd/mm/yyyy'));
to
'to_date(''21/03/2014''', '''dd/mm/yyyy'')')
Trying to run this throws the Lookup Error - Oracle Database "ORA-01858"
WORKAROUND
None
STATUS
Waiting for fix in a future release of Toad Data Point