Home » SQL & PL/SQL » SQL & PL/SQL » Quick Question
Quick Question [message #261996] Fri, 24 August 2007 05:47 Go to next message
CocoPop
Messages: 12
Registered: August 2007
Location: England
Junior Member
Hey I was just wondering whats the easiest way to check if a select statement has returned a value into a varaible

for example

select max(ID)
into v_ID
from StudentTable

is there anyways you can check if the v_ID actually holds a value?

thanks
Re: Quick Question [message #261998 is a reply to message #261996] Fri, 24 August 2007 05:48 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
IF v_id IS NOT NULL THEN
...

You can't use SQL%ROWCOUNT because MAX will always return one record (even if it is empty).

[Updated on: Fri, 24 August 2007 05:51]

Report message to a moderator

Re: Quick Question [message #262004 is a reply to message #261996] Fri, 24 August 2007 05:56 Go to previous messageGo to next message
CocoPop
Messages: 12
Registered: August 2007
Location: England
Junior Member
Thanks Very Happy
Re: Quick Question [message #262005 is a reply to message #261998] Fri, 24 August 2007 05:57 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
MAX will always return one record (even if it is empty).
But not with GROUP BY.

By
Vamsi
Re: Quick Question [message #262007 is a reply to message #262005] Fri, 24 August 2007 06:01 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right, but in OP's example such a thing would return TOO-MANY-ROWS and wouldn't work at all.
Re: Quick Question [message #262010 is a reply to message #262007] Fri, 24 August 2007 06:07 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Yes, Of course Razz

By
Vamsi
Re: Quick Question [message #262024 is a reply to message #262007] Fri, 24 August 2007 06:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Littlefoot wrote on Fri, 24 August 2007 13:01
Right, but in OP's example such a thing would return TOO-MANY-ROWS and wouldn't work at all.

No, not if you'd group by a constant:
SQL> select max(sal) from emp;

  MAX(SAL)
----------
      5000

SQL> select max(sal) from emp group by 1;

  MAX(SAL)
----------
      5000

SQL> select max(sal) from emp group by .5;

  MAX(SAL)
----------
      5000
Re: Quick Question [message #262029 is a reply to message #262024] Fri, 24 August 2007 06:35 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I admit, I was wrong.

But, what's the purpose of grouping by a constant? It looks like adding zeros in front of a number.

You know, it isn't 7 but 07.
No, it isn't 07 but 007.
Bond. James Bond.
Damme. Van Damme. Claude Van Damme. Jean-Claude Van Damme.
Re: Quick Question [message #262032 is a reply to message #262029] Fri, 24 August 2007 06:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Littlefoot wrote on Fri, 24 August 2007 13:35
I admit, I was wrong.

But, what's the purpose of grouping by a constant?

nothing, but proving you wrong Cool
Re: Quick Question [message #262034 is a reply to message #262029] Fri, 24 August 2007 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what's the purpose of grouping by a constant?

I prefer:
SCOTT> select max(sal) from emp group by null;
  MAX(SAL)
----------
      5000

1 row selected.

This matches more with my mind. ./fa/917/0/

Regards
Michel
Re: Quick Question [message #262035 is a reply to message #262032] Fri, 24 August 2007 06:41 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1606/0/ That's how it is when you're little and unprotected ./fa/1693/0/
Re: Quick Question [message #262038 is a reply to message #262029] Fri, 24 August 2007 06:45 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Bond won't agree, if you call him as 7. also he has licenced to kill / thrill.

We can find some silly scenarios.
If I want to print maximum salary, if there are more than 3 employees.
select max(sal) 
  from scott.emp
 group by 1
 having count(0) > 3;
By
Vamsi
Previous Topic: Number of days in current month
Next Topic: co-related sub queries
Goto Forum:
  


Current Time: Sun Dec 11 00:11:57 CST 2016

Total time taken to generate the page: 0.13291 seconds