Home » SQL & PL/SQL » SQL & PL/SQL » SQL with CASE in Trigger (Forms 6i,Windows xp)
SQL with CASE in Trigger [message #354352] Sat, 18 October 2008 00:09 Go to next message
NoraizOraDev
Messages: 11
Registered: September 2008
Junior Member
Assalam-o-Alaikum
I want to use following SQL statement in Form Trigger.

SELECT TABLE_NAME,NUM_ROWS,
CASE
    WHEN NUM_ROWS <= 150 THEN 'SMALL'
    WHEN NUM_ROWS > 150 THEN 'LARGE'    
END AS TYPE_IS
INTO V_TABLE_NAME,V_NUM_ROWS,V_TYPE_IS
FROM USER_TABLES

But it shows error on compilation against CASE statement.
Please explain . . .
Re: SQL with CASE in Trigger [message #354354 is a reply to message #354352] Sat, 18 October 2008 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DECODE and SIGN instead.

Regards
Michel
Re: SQL with CASE in Trigger [message #354498 is a reply to message #354352] Mon, 20 October 2008 00:38 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

What is the error you are getting ?

Thumbs Up
Rajuvan.
Re: SQL with CASE in Trigger [message #354510 is a reply to message #354352] Mon, 20 October 2008 02:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I strongly suspect that the error you're getting is an ORA-01422: exact fetch returns more than requested number of rows.
The Sql you've got works fine and it works fine in pl/asq if the variables are correctly declared, and there is oly one row returned from user tables.

If you'd deign to share the actual error you got with us, and possibly even the code then perhaps we can be of more assistance.
Re: SQL with CASE in Trigger [message #354512 is a reply to message #354510] Mon, 20 October 2008 02:16 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I see "Forms 6i" and "Forms trigger". Wouldn't it be possible that Forms SQL doesn't know the CASE statement?

As suggested before, I'd rewrite your function with a DECODE.

MHE
Re: SQL with CASE in Trigger [message #354888 is a reply to message #354352] Tue, 21 October 2008 16:44 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. As JRowbottom wrote: you can NOT perform SELECT ... INTO when a number of rows will be returned by a query - use cursor instead.

2. Try :

...
CASE WHEN  WHEN NUM_ROWS <= 150 THEN 'SMALL'
     ELSE 'LARGE' END AS TYPE_IS ...


HTH.

Michael
Re: SQL with CASE in Trigger [message #354904 is a reply to message #354888] Tue, 21 October 2008 23:21 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

michael_bialik wrote on Wed, 22 October 2008 03:14
1. As JRowbottom wrote: you can NOT perform SELECT ... INTO when a number of rows will be returned by a query - use cursor instead.

2. Try :

...
CASE WHEN  NUM_ROWS <= 150 THEN 'SMALL'
     ELSE 'LARGE' END AS TYPE_IS ...


HTH.

Michael
How does it make a difference ? Both syntax are correct.

Thumbs Up
Rajuvan.
Re: SQL with CASE in Trigger [message #354935 is a reply to message #354904] Wed, 22 October 2008 00:34 Go to previous messageGo to next message
NoraizOraDev
Messages: 11
Registered: September 2008
Junior Member
The error in FORM 6i is
Encountered the symbol "CASE".


Its not working in Cursor also.

Friends who saying to use DECODE, Please tell me how to use expressions in decode statement?

Hope you understand!
Please check and then post reply with example.
Re: SQL with CASE in Trigger [message #354941 is a reply to message #354354] Wed, 22 October 2008 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 18 October 2008 07:29
Use DECODE and SIGN instead.

Regards
Michel

Did you read the SQL Reference about these 2 functions?

Regards
Michel

Re: SQL with CASE in Trigger [message #354942 is a reply to message #354935] Wed, 22 October 2008 00:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Search for a combination of DECODE and SIGN, like Michel told you in the very first reply

[Updated on: Wed, 22 October 2008 00:44]

Report message to a moderator

Re: SQL with CASE in Trigger [message #354951 is a reply to message #354942] Wed, 22 October 2008 01:11 Go to previous messageGo to next message
NoraizOraDev
Messages: 11
Registered: September 2008
Junior Member
If you know how to do then why u feel hesitation to send a single simple example.
Re: SQL with CASE in Trigger [message #354967 is a reply to message #354951] Wed, 22 October 2008 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because:
1/ It is better for you to find it by yourself, you will learn more
2/ There are examples in the documentation and it is better for you to read them then you will see and learn many correlated things.

Regards
Michel
Re: SQL with CASE in Trigger [message #354974 is a reply to message #354352] Wed, 22 October 2008 01:48 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Isn't it simple ?

Just think you have a figure . how will you prove it whether it is less than 150 or greater than 150 mathematically ? just convert this logic to DECODE and SIGN .

Smile
Rajuvan.

[Updated on: Wed, 22 October 2008 01:48]

Report message to a moderator

Re: SQL with CASE in Trigger [message #355082 is a reply to message #354935] Wed, 22 October 2008 08:59 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
NoraizOraDev wrote on Wed, 22 October 2008 01:34
The error in FORM 6i is
Encountered the symbol "CASE".


Its not working in Cursor also.

Friends who saying to use DECODE, Please tell me how to use expressions in decode statement?

Hope you understand!
Please check and then post reply with example.


As Maaher said, Forms6i does not know what CASE. It's based on a PL/SQL engine that did not have CASE in it's syntax.
Re: SQL with CASE in Trigger [message #355179 is a reply to message #354352] Thu, 23 October 2008 00:39 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Or you could try something really bizarre like:
SELECT TABLE_NAME,NUM_ROWS,
INTO V_TABLE_NAME,V_NUM_ROWS
FROM USER_TABLES;

IF NUM_ROWS <= 150 THEN
    V_TYPE_IS := 'SMALL';
ELSE
    V_TYPE_IS := 'LARGE';
END IF;
Re: SQL with CASE in Trigger [message #355338 is a reply to message #355179] Thu, 23 October 2008 14:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The following demonstrates what you get when you subtract 150 fro num_rows, what you get when you take the sign of that (+1 for a positive number, -1 for a negative number, or 0 for 0) and how you can use decode on the result of that.

SCOTT@orcl_11g> SELECT table_name,
  2         num_rows,
  3         num_rows - 150,
  4         SIGN (num_rows - 150),
  5         DECODE (SIGN (num_rows - 150), -1, 'SMALL', +1, 'LARGE', 0, 'LARGE')
  6  FROM   user_tables
  7  WHERE  table_name LIKE 'C%'
  8  /

TABLE_NAME                       NUM_ROWS NUM_ROWS-150 SIGN(NUM_ROWS-150) DECOD
------------------------------ ---------- ------------ ------------------ -----
CINFO                                   5         -145                 -1 SMALL
CUSTOMER                            68725        68575                  1 LARGE

SCOTT@orcl_11g>

Re: SQL with CASE in Trigger [message #355339 is a reply to message #354352] Thu, 23 October 2008 15:00 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Another way to user decode

 select table_name,
        num_rows,
        DECODE (least(num_rows,150), 150,'Large', 'SMALL')
 FROM   user_tables
 WHERE  table_name LIKE 'C%'
>/

TABLE_NAME                       NUM_ROWS DECOD
------------------------------ ---------- -----
CD_DEL_SERVICE2STORE                   21 SMALL
CD_DISPLAY_TRK                       4483 Large
CD_EMP_PAY                         278533 Large
CD_EMP_PAY_DET                     437717 Large
CD_EMP_PAY_QUERY                      618 Large
CD_EXEC_SUM                         80791 Large
CD_HR_READY                          4582 Large
CD_NEW                                 11 SMALL
CD_PAID_EXCEPTIONS                      0 SMALL
CD_PAY                             650554 Large
CD_PAY4DAY                          14271 Large
Previous Topic: Comparing similar strings in a table
Next Topic: how to get the date range (merged)
Goto Forum:
  


Current Time: Thu Dec 08 02:09:34 CST 2016

Total time taken to generate the page: 0.14976 seconds