TOAD throws warning, code executes in SQL PLUS

Toad for Oracle & DevOps Toolkit
1

Hi,

I have a strange situation. I am running below mentioned code from 2 TOAD versions:

SELECT LEAD (dummy IGNORE NULLS)OVER(ORDER BY dummy DESC) from dual where dummy = :var;

a. Toad for Oracle 12.6.0.53

b. Toad for Oracle Xpert Trial 11.0.0.116

In version a I get warning and when i try to run code it does not show bind variable in pop up. I have attached screenshot for same.

In version b, it shows warning but shows bind variable in pop up and runs fine.

Why am I getting this warning even if code is working fine from SQL plus?

Thanks,

Anjali

Anjali.

0458.Screenshot.png
0458.Screenshot.png1357×1600 64.5 KB

2

I tried 11.0 and I see that the variable is prompted for (as you mentioned), however, I get an error when actually trying to run it - same for in SQL Plus.
The problem is the ‘IGNORE’ - it’s expecting an offset there. I’m not sure how this worked in SQL Plus for you when you ran it - at least, I wasn’t able to replicate it.

The only way I could make it work was to remove the IGNORE and replace it with the offset, which is the what the LEAD function expects. When you do this
in Toad, then the bind variable shows up correctly, because it’s valid syntax.

SELECT LEAD
(dummy,1)OVER(ORDER
BY dummy
DESC)
from dual
where dummy

:var;

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions074.htm

From: Anjali_Sharma [mailto:bounce-Anjali_Sharma@toadworld.com]

Sent: Wednesday, January 21, 2015 7:21 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] TOAD throws warning, code executes in SQL PLUS

TOAD throws warning, code executes in SQL PLUS

Thread created by Anjali_Sharma

Hi,

I have a strange situation. I am running below mentioned code from 2 TOAD versions:

SELECT LEAD (dummy IGNORE NULLS)OVER(ORDER BY dummy DESC) from dual where dummy = :var;

a. Toad for Oracle 12.6.0.53

b. Toad for Oracle Xpert Trial 11.0.0.116

In version a I get warning and when i try to run code it does not show bind variable in pop up. I have attached screenshot for same.

In version b, it shows warning but shows bind variable in pop up and runs fine.

Why am I getting this warning even if code is working fine from SQL plus?

Thanks,

Anjali

Anjali.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

3

Hey Dennis,

The offset doesn’t give me the desired result. I ran this from SQL plus and works fine for me

SELECT LEAD (dummy,1)OVER(ORDER BY dummy DESC) from dual where dummy = ‘X’;

4

Hey,

Any suggestions anyone.

5

Anjali - as mentioned before, the syntax you’re using is incorrect. You mentioned that using the offset doesn’t give you the desired result, but from the
example you provided, it’s impossible to know what the desired result is.

Create a simple example using the SCOTT schema like on the Oracle page I sent you and we can try to help you further, however, this wouldn’t be a Toad question
as much as it is a SQL or SQL*Plus question. My suggestion would be to post this to an Oracle forum, however, we can try to help you as much as we can.

SELECT last_name, hire_date,

LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS “NextHired”

FROM employees WHERE department_id = 30;

or

select
ename, hiredate,

lead(hiredate,1)OVER(order
by hiredate)
as
“nexthired”

from
scott.emp

where deptno

30;

From: Anjali_Sharma [mailto:bounce-Anjali_Sharma@toadworld.com]

Sent: Thursday, January 22, 2015 6:16 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] TOAD throws warning, code executes in SQL PLUS

RE: TOAD throws warning, code executes in SQL PLUS

Reply by Anjali_Sharma

Hey,

Any suggestions anyone.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

6

Hi Dennis,

My data is something like this:

Dummy

NULL

XXX

NULL

NULL

YYY

NULL

NULL

NULL

ZZZ

NULL

AAA

I want output as:

Dummy

XXX

YYY

ZZZ

AAA

Now I can’t fix the offset for LEAD functions as 1 or 2 or any other number as I can’t determine where next NOT NULL value is available. That’s the reason I used IGNORE NULLS in LEAD.

Also, it’s not a query error I am concerned with. My only issue is that since query is working fine and returning me data in way I want, then why is it showing warning in TOAD?

Thanks,

Anjali

7

This looks like a parser bug. If you replace the bind variable with a value it works.

On 01/22/2015 08:55 AM, Anjali_Sharma wrote:

RE: TOAD throws warning, code executes in SQL PLUS

Reply by Anjali_Sharma
Hi Dennis,

My data is something like this:

Dummy

NULL

XXX

NULL

NULL

YYY

NULL

NULL

NULL

ZZZ

NULL

AAA

I want output as:

Dummy

8

It seems to be TOAD issue. It works fine with sqldeveloper.

9

It is and has been reported to the developer.

10

Ok great. Please do let me know once we get resolution for it.