Solved

Select Case on Subset?

Posted on 2011-02-11
13
634 Views
Last Modified: 2013-12-07
Hello,

I am very experienced on SQL Server, but am very new to Oracle.

I had a package with a function that returned one row.  However, there were rare occasions when it would need to return more than one row, so I have updated the function to return multiple rows as needed.

However, this has now caused the query that used my function to fail, because it was matching on the one returned row and now there are sometimes multiple returned rows.  So, my question is, is it possible to update the following case statement to look through a subset instead?  If so, what would the syntax be?

Something like: CASE WHEN Field4 IN package.function(Field3) THEN...

Thanks,
-Torrwin
SELECT Field1, Field2, (CASE WHEN package.function(Field3) = Field4 THEN 'True' ELSE 'False' END)
FROM Table1

Open in new window

0
Comment
Question by:Torrwin
  • 10
  • 3
13 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 34872545
How does your function return multiple values?


 A SQL collection type?  A PL/SQL collection type? XMLTYPE? a concatenated string? etc?
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 34872577
assuming your function returns a SQL collection type

select case when field4 in (select * from table(your_function(field3))) then 'TRUE' else 'FALSE' end
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34872591
if pl/sql collection type, then you can't use your function in SQL

if XML, you will need to use an XPATH query to search for field4 in the resulting XML nodes

if concatenated string, you could try using INSTR, but you have the potential of false positives unless you're very careful.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Author Comment

by:Torrwin
ID: 34872665
Actually, now that you mention it, that's got to be a problem too.  I need to update the return type as well, it was originally just NUMBER.

Here's the basic function:
FUNCTION Function1(parameter1 NUMBER)
 RETURN NUMBER IS

 variable1 NUMBER;

 BEGIN
  SELECT Field1
  INTO variable1
  FROM ...
  WHERE Field2 = paramater1
   AND ROWNUM >= 1;
 END;

 RETURN (variable1);
END;

Open in new window

0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34872709
First, create your SQL collection type...

CREATE OR REPLACE TYPE number_table AS TABLE OF NUMBER;

Then create your function as a pipelined function

FUNCTION Function1(parameter1 NUMBER)
 RETURN number_table PIPELINED
IS

 variable1 NUMBER;

 BEGIN
     for x in (
  SELECT Field1
  INTO variable1
  FROM ...
  WHERE Field2 = paramater1
   AND ROWNUM >= 1)
loop
        pipe row (x.field1);
end loop;
 END;

return;  --- with no value, the pipe row does the actual "returning"

EXCEPTION
   WHEN NO_DATA_NEEDED THEN
           null; -- if your function does anything that needs cleanup, do it here
END;


then use the sql collection example above
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34872732
if your collection will be small, you can also do it as a non-pipelined function.
doing that can be marginally faster, but will consume more resources, particularly memory
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 34872758
the non-pipelined version might look something like this...
(you still need to create the sql collection type from above)


FUNCTION Function1(parameter1 NUMBER)
 RETURN number_table
IS
v_nums number_table := number_table(NULL);

BEGIN
     SELECT Field1
  BULK COLLECT INTO v_nums
  FROM ...
  WHERE Field2 = paramater1
   AND ROWNUM >= 1;

return v_nums;

END;

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34872765
as a side note...  

AND ROWNUM >= 1  

isn't necessary, or helpful
0
 
LVL 13

Author Comment

by:Torrwin
ID: 34873943
Thanks, i'm testing it now, it looks pretty solid.

The ROWNUM >= 1 exists because I have several of this query chained together with a EXCEPTION WHEN NO DATA FOUND clause.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34873970
how does that help?

ROWNUM is a pseudo-column generated by the existence of data.

so ROWNUM >= 1 only does anything when there is at least one row

in other words,  your filter is saying,  "only return rows when there are rows"

which is legal, but doesn't offer any functionality
0
 
LVL 13

Author Comment

by:Torrwin
ID: 34875126
> so ROWNUM >= 1 only does anything when there is at least one row

Correct, which is what i'm looking to do.  I didn't explain it fully because the details aren't really important, and obviously the query is scaled down to where I can get the point across but still give enough information to get an informed answer.  

Basically, we have a situation where we need to return the "most correct" record from a hierarchy.  So, the business logic looks something like "If Type A exists, then use this record.  If type A doesn't exist, then check for type B.  If type B exists ...etc..."
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34875267
>>>> so ROWNUM >= 1 only does anything when there is at least one row


You're missing the point if you're counting on that criteria to "DO" anything.

"return rows if there are rows" you could just as easily say where rownum != 0
or where rownum is not null

or best of all:  Don't use that condition at all.



rownum only exists if there is a row,
So, if you have a row, it will be at least 1

if you don't have a row, then you won't have a rownum to compare to 1

You should remove the condition,  

Right now, that query is sort of the SQL version of the old joke

"Raise your hand if you're not here"






0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34875282
your if A then A, else B logic will NOT be enforced by a


 ROWNUM >= 1 condition

If you think you have an example that demonstrates it,  please open a new question for discussion
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

775 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question