SQL & PL/SQL

Hi, Anjali,

That doesn;t look like an Oracle error message.  Oracle error messages always include a 5-digit number, usually with a 3-character prefix, e.g. ORA-00907.

Oracle 11.2 allows IGNORE NULLS in LEAD and LAG.  I suspect you have some front-end tool that checks syntax before giving the code to Oracle.

If you need to use that front end, you can use FIRST_VALUE or LAST_VALUE instead of LEAD or LAG.

Hi Frank,

Thank for your reply. It's not an oracle error as I mentioned earlier. It's a warning basically. I have attached the screenshot for your reference.

Thanks in advance.

Anjali.

Does it run? If so, ignore the error as displayed by Toad (I assume you've done some sort of code checking to get that warning message).

Alternative answers could be that your Oracle client is too old to support the new 11.2 features, or Toad just doesn't recognise this functionality. (I've just checked on v12.6, and I get the squiggly red line underlining "ignore" in the following statement: select lag(dummy ignore nulls) over (order by dummy) from dual;   so I'm guessing it's a Toad-ism. It still runs ok when I execute the statement, though.)

Use another tool e.g. sqlplus to run your query.

There's a performance issue with this feature in 11.2.  You can use LAST_VALUE...IGNORE NULLS instead.

I ran using SQLDEVELOPER. I still see the warning there.

That's the thing. It works fine but this warning is little annoying. Now when my code is getting reviewed people are not happy why this warning is shown even though it works fine. I need functionality of LEAD so can't replace it with FIRST_VALUE or LAST_VALUE. It's not returning correct result.

Show an example which is not returnig correct result.

basically it's something like:

last_value(... ignore nulls) over(partition by ... order by ... ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

first_value(... ignore nulls) over(partition by ... order by ... ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)

Hi,

Anjali Sharma wrote:

... I need functionality of LEAD so can't replace it with FIRST_VALUE or LAST_VALUE. It's not returning correct result.

What functionality do you mean?  Anything that LEAD can do, you can also do using FIRST_VALUE or LAST_VALUE.  If you want to ignore NULLs, then it's probably simpler to use FIRST_VALUE or LAST_VALUE anyway.

If you tried FIRST_VALUE or LAST_VALUE and got the wrong result, then you weren't doing it right.  Post your code, along with CREATE TABLE and INSERT statements for the sample data, and the desired results.

My version (4.0.2.15) of sqldeveloper doesn't object to it at all.

My version of Toad (10.6) connected to an 11.2.0.3 database doesn't complain either (using Boneist's statement.  I get a red line under "lag" but that's nothing of any concern, and it runs fine.

Also works fine in SQL*Plus.

Anjali,

What Oracle client version do you have installed?  It could be that your client Oracle net software is an old version.

Hi,

How to check for Oracle client version?

Easiest way to get an idea is from the command line:

c:\>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 21 10:31:08 2015

Once I've logged in:

Enter user-name: scott/tiger@test

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

So, although my Oracle Database version on the server is 11.2.0.3.0, I can see my SQL*Plus release on my client computer is 11.2.0.1.0 from the first step, and that's indicative of the Oracle client software I have installed.

It's quite common for people to find that the DBA's/Server administrators have ugraded the Oracle database, but people have not updated their client software (as in my case I'm not quite up to the latest client release to match the database).

Sometimes people find things don't work that they expect to work because they are still using things like Oracle client for 9i or 10g even though they're connecting to an 11g or 12c database.  The client needs to be up to date to know of the new features in the languages, otherwise it can throw errors.

Hi All,

I even tried it with TOAD 12.6.0.53 version. There it throws an error - [Error] Syntax check.

I have bind variable in query but while running it it doesn't even ask for input parameters. Totally strange.

I have no ides if it is issue with setting of TOAD as it works fine with SQL Plus. Please advise what change in TOAD settings is required?

Thanks.

Hi,

This is what I have. Can this be causing issue?

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 21 16:14:46 2015

Enter user-name: dummy/dummy@ipdexqa.world

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

Thanks.

looks like it has something to do with toad setting or oracle client. But before getting into this, try it on sql plus or oracle sql developer. If it is working there then i think you should not be concerned about this message.

check oracle client of the terminal from where you are running this query. Most probably it is because of having old version of oracle client which does not support this feature. Just for testing purpose if you have access to server terminal you can run this query and check. This query works fine with even old version of third party tool like pl/sql developer.

Hi Raj,

It throws a warning in sqldeveloper as well but works fine. Also works with sql plus. I know its not a concern but our QA team is adamant that they will test it via TOAD as they have been doing so for everything. Now when they are saying code is incorrect.

So, I just wanted to understand if i can change TOAD setting somewhere so that it works fine.

Thanks.

Hi Anjali, Can you tell me the oracle client version which is installed on the terminal where QA team is doing testing.

Hi Raj,

This is what they have:

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 21 16:14:46 2015

Enter user-name: dummy/dummy@ipdexqa.world

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

when you are running this query on sql plus then also you are getting warning? On your terminal do you have mutiple oracle client installed?

It's the Toad developers who would need to change something, as Toad itself doesn't recognise the syntax as being correct. However, it's not an issue, since the code is, in fact, correct. The QA team should only be using the Toad code analyse function under advisement - it is not always going to be correct 100% of the time. If the statement Toad is complaining about compiles ok and runs ok (and, of course, does what it's supposed to!), then it's ok, and the QA team should be able to say "ok, we know about this issue, it's a Toad problem, not a code problem - let's ignore", given that you'll have told them that!

Hi Raj,

No I am not getting warning in SQLplus. We just have one oracle client installed.

Thanks.

But as you mentioned this problem is there in both sql developer and toad and error shows as if it is something to do with the tool which is creating issues. Talk to your qa team and convince them as anyways toad is not something which will validate the data or query. May be the best way to debug warning using some other forum, you might refer toad forum also.

Yes,

I have put my question in TOAD community. Let's see if I get some response.

Thanks.

good. All the best.

Hi Raj,

Here is the SQL Developer screenshot.

Anjali

here it is not showing warning in output, in query only it is showing warning as hints, am i right?

Yes Raj, Everywhere error or warning is in query only and not in output.

Hi All,

I found resolution to above mentioned problem. Below query works fine:

LEAD(COLUMN1) IGNORE NULLS OVER(ORDER BY DATE_COLUMN DESC)

I think most stupid error ever;) Anyways thanks all for your help.

Regards,

Anjali

View -> Toad Options -> Code Analysis -> General -> Rules

Depending on your licening you may be able to edit it or not.

Anjali Sharma wrote:
I found resolution to above mentioned problem. Below query works fine:

LEAD(COLUMN1) IGNORE NULLS OVER(ORDER BY DATE_COLUMN DESC)

Congrats, though it was sufficient just to read the answers given to you already.