CODEWITHSUNDEEP
sqloracle-databaseoracle10g
hradac
hradac

Reputation: 2491

Selecting multiple columns conditionally in Oracle10g

I have a table with something like a dozen different columns. Is there a way to conditionally select columns based on a certain condition?

For example we'll say this a user table with username, password, all that stuff, and a field that holds a count of how many times that user has failed a login. (Wrong password, right username). So instead of having a query that returns nothing when either username or password are wrong, or a query that returns everything when just the username is correct, I would like a query that returns a subset when the username is correct but the password is wrong.

Pseudo SQL:

SELECT column1,
       column2,
       column3,
       column4,
       column5,
       column6,
   --If this condition is not met the following columns are not selected
   IF password = password  
   (
       conditional_column1,
       conditional_column2,
       conditional_column3
   )
FROM source_table
WHERE username = username

I have tried a CASE statement and DECODE but each of those only returns one value for each condition. I want to select several columns for a single condition.

I do not have access to PL/SQL and I am trying to keep this all in one query. Anyone know if this is possible?

EDIT:

Just to clarify, in my case I do not want to check if I'm asking for username, I want to check if there are any rows where username = x. If all I had to do was pass username to a function and build a query string this wouldn't be a problem.

So the flow goes:
Get some columns where username is 'x' then, if password is 'y' get these other columns.

Upvotes: 3

Views: 1769

Answers (3)

steve
steve

Reputation: 6020

It sounds like you want to have a look at the virtual private database (VPD) feature of oracle

Upvotes: 2

orbfish
orbfish

Reputation: 7741

Justin is right that there is no way (other than SELECT * or derivatives like PIVOT) to get back a variable number of columns from a static query. My 2 suggestions are:

  1. Dynamic SQL, if you have control over how the query gets created.

  2. Determine a maximum number of columns, and throw a view over your source_table using CASE/DECODE like you have tried. You can add an indicator column for each conditional column if a null check is not enough. I have used this to get around the "can't query unknown domain problem" successfully.

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231861

When a query is submitted to the SQL engine, the number of columns that will be selected has to be known. The SQL engine cannot let the number of columns vary at runtime.

You could, however, use dynamic SQL to assemble the query that would be submitted to the SQL engine dynamically. For example, I can create a PL/SQL procedure that will dynamically assemble a SQL statement that includes different columns and return a SYS_REFCURSOR to the caller. You can do something similar in whatever language your application is using.

SQL> create or replace procedure dyn_emp( p_include_empno in boolean,
  2                                       p_rc           out sys_refcursor )
  3  as
  4    l_sql varchar2(1000);
  5  begin
  6    l_sql := 'SELECT ename ';
  7    if( p_include_empno )
  8    then
  9      l_sql := l_sql || ', empno ';
 10    end if;
 11    l_sql := l_sql || ' from emp';
 12    open p_rc for l_sql;
 13  end;
 14  /

Procedure created.

SQL> variable rc refcursor;
SQL> exec dyn_emp( true, :rc );

PL/SQL procedure successfully completed.

SQL> print rc

ENAME           EMPNO
---------- ----------
SMITH            7369
ALLEN            7499
WARD             7521
JONES            7566
MARTIN           7654
BLAKE            7698
CLARK            7782
SCOTT            7788
KING             7839
TURNER           7844
ADAMS            7876
JAMES            7900
FORD             7902
MILLER           7934

14 rows selected.

SQL> exec dyn_emp( false, :rc );

PL/SQL procedure successfully completed.

SQL> print rc

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

Upvotes: 0

Related Questions