Home » SQL & PL/SQL » SQL & PL/SQL » Single ROW Query - but not all columns (Oracle 10g)
icon5.gif  Single ROW Query - but not all columns [message #358659] Wed, 12 November 2008 01:43 Go to next message
Silver
Messages: 14
Registered: November 2008
Location: Middle of Germany
Junior Member
Greetings Dudes!

I´ve got a little problem here.
I have a table with these columns:

|IDENTNB | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
|        |   |   |   |   |   |   |   |   |   |
| 123    | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 |

As you can see, I have a field with an unique ID and 9 fields (NUMBER) with values of 0 or 1.

Is there a SELECT query, which returns just the columns with the value of 1 like this:

|IDENTNB | 3 |  5 | 7 | 
|        |   |    |   | 
| 123    | 1 |  1 | 1 | 


I really appreciate your help!
Re: Single ROW Query - but not all columns [message #358662 is a reply to message #358659] Wed, 12 November 2008 01:52 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
is IDENTNB and 123 are your column name?
Re: Single ROW Query - but not all columns [message #358663 is a reply to message #358659] Wed, 12 November 2008 01:54 Go to previous messageGo to next message
Silver
Messages: 14
Registered: November 2008
Location: Middle of Germany
Junior Member
IDENTNB is the name of the column, 123 is the unique identifier for this row.
Re: Single ROW Query - but not all columns [message #358666 is a reply to message #358663] Wed, 12 November 2008 02:01 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
means your table has 10 columns and 1 row right?
Re: Single ROW Query - but not all columns [message #358667 is a reply to message #358659] Wed, 12 November 2008 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Single ROW Query - but not all columns [message #358668 is a reply to message #358659] Wed, 12 November 2008 02:07 Go to previous messageGo to next message
Silver
Messages: 14
Registered: November 2008
Location: Middle of Germany
Junior Member
Quote:
means your table has 10 columns and 1 row right?


Exactly!

Explanation: I´m working with Aplication Express (APEX) and want to create a list of values (LOV), that means a list which shows specified values in dependency of the given values in a SELECT Query Smile
Re: Single ROW Query - but not all columns [message #358672 is a reply to message #358659] Wed, 12 November 2008 02:16 Go to previous messageGo to next message
Silver
Messages: 14
Registered: November 2008
Location: Middle of Germany
Junior Member
CREATE TABLE test (
IDENTNB NUMBER not null,
1       NUMBER,
2       NUMBER,
3       NUMBER,
4       NUMBER,
5       NUMBER,
6       NUMBER,
7       NUMBER,
8       NUMBER,
9       NUMBER
);

ISNERT INTO test (
IDENTNB,
1,
2,
3,
4,
5,
6,
7,
8,
9)

VALUES

(
'123',
'0',
'0',
'1',
'0',
'1',
'0',
'1',
'0',
'0'
);



If you write a normal SQl Query like:

SELECT * FROM test WHERE test.IDENTNB LIKE '123'


you´re getting one row of all columns with their values.

BUT: I want just these columns, in which the value is 1.
In the example above, these are just the case of column 3, 5 and 7.
Re: Single ROW Query - but not all columns [message #358674 is a reply to message #358659] Wed, 12 November 2008 02:22 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You cannot do directly in SQL. you have to use DYnamic SQL . Search for it.

Smile
Rajuvan.
Re: Single ROW Query - but not all columns [message #358679 is a reply to message #358672] Wed, 12 November 2008 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE TABLE test (
  2  IDENTNB NUMBER not null,
  3  1       NUMBER,
  4  2       NUMBER,
  5  3       NUMBER,
  6  4       NUMBER,
  7  5       NUMBER,
  8  6       NUMBER,
  9  7       NUMBER,
 10  8       NUMBER,
 11  9       NUMBER
 12  );
1       NUMBER,
*
ERROR at line 3:
ORA-00904: : invalid identifier

Please post a VALID test case.

Regards
Michel
Re: Single ROW Query - but not all columns [message #358680 is a reply to message #358674] Wed, 12 November 2008 02:30 Go to previous messageGo to next message
Silver
Messages: 14
Registered: November 2008
Location: Middle of Germany
Junior Member
rajavu1 wrote on Wed, 12 November 2008 09:22
You cannot do directly in SQL. you have to use DYnamic SQL . Search for it.

Smile
Rajuvan.


Just thought so :-/ then I have to use some loopings and lists...
Re: Single ROW Query - but not all columns [message #358681 is a reply to message #358679] Wed, 12 November 2008 02:35 Go to previous messageGo to next message
Silver
Messages: 14
Registered: November 2008
Location: Middle of Germany
Junior Member
Michel Cadot wrote on Wed, 12 November 2008 09:28
SQL> CREATE TABLE test (
  2  IDENTNB NUMBER not null,
  3  1       NUMBER,
  4  2       NUMBER,
  5  3       NUMBER,
  6  4       NUMBER,
  7  5       NUMBER,
  8  6       NUMBER,
  9  7       NUMBER,
 10  8       NUMBER,
 11  9       NUMBER
 12  );
1       NUMBER,
*
ERROR at line 3:
ORA-00904: : invalid identifier

Please post a VALID test case.

Regards
Michel




Sorry, my fault. This should be working:

CREATE TABLE test (
IDENTNB NUMBER not null,
  "1"       NUMBER,
  "2"       NUMBER,
  "3"       NUMBER,
  "4"       NUMBER,
  "5"       NUMBER,
  "6"       NUMBER,
  "7"       NUMBER,
  "8"       NUMBER,
  "9"       NUMBER
  );

INSERT INTO test (
IDENTNB,
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9")

VALUES

(
'123',
'0',
'0',
'1',
'0',
'1',
'0',
'1',
'0',
'0'
);
Re: Single ROW Query - but not all columns [message #358708 is a reply to message #358681] Wed, 12 November 2008 04:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I really wouldn't create a table like that.
You're making a world of pain for the developers that come after you
Re: Single ROW Query - but not all columns [message #358710 is a reply to message #358659] Wed, 12 November 2008 04:35 Go to previous messageGo to next message
Silver
Messages: 14
Registered: November 2008
Location: Middle of Germany
Junior Member
@JRowBottom: I´m always open for new suggestions Smile

I´m working on the dynamic SQL Code; guess I must use a temporary table for saving the values...I´m trying to create this temporary table dynamicly in dependence of my values..I´m on it Cool (but its really creepy)
Re: Single ROW Query - but not all columns [message #358724 is a reply to message #358710] Wed, 12 November 2008 05:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
One step further:
Since you don't know upfront which columns will be returned, how are you going to process the result of the query?

I think you'd better create records containing your current unique ID, a column containing 1, 2, 3 or whatever and a column containing the 0/1 value.
Re: Single ROW Query - but not all columns [message #358751 is a reply to message #358659] Wed, 12 November 2008 06:42 Go to previous messageGo to next message
Silver
Messages: 14
Registered: November 2008
Location: Middle of Germany
Junior Member
@Frank: You´re right, at first sight, you don´t know the number of columns.

But my plan is the following:
- Open a cursor on the column in my table (see example)
- writing the values in local variables
- create a string with these variables, so the result is something like 0:1:0:1:0:0:0
- convert it to a table (APEX_UTIL.STRING_TO_TABLE)

Now you have a table with index 1..10 and in every position the value of your original table.

- Create a new String with every position in list, which value is = 1.

Now you´ve got a string with the original Columns, in which the value is = 1.

Based on this string, I dynamically create the temporary table and filling it with values (just the value '1').

At the very end, I creating a SELECT Query from this temporary table Smile

Re: Single ROW Query - but not all columns [message #358770 is a reply to message #358659] Wed, 12 November 2008 07:50 Go to previous messageGo to next message
Silver
Messages: 14
Registered: November 2008
Location: Middle of Germany
Junior Member
....i´ve got a solution!

As I said, I´m working with APEX and my final issue was a multiple select list based on a list of values (LOV).

The Code is really simple:

select 'Position 1' display,1 return
from tablename
where identnb=XXX
and column1=1
union all
select 'Position 2' display,2 return
from tablename
where identnb=XXX
and column2=1
union all
select 'Position 3' display,3 return
from tablename
where identnb=XXX
and column3=1
etc....
Re: Single ROW Query - but not all columns [message #358793 is a reply to message #358770] Wed, 12 November 2008 09:03 Go to previous messageGo to next message
leonardh
Messages: 10
Registered: November 2008
Junior Member
Why don't you use DECODE or CASE?
Re: Single ROW Query - but not all columns [message #358963 is a reply to message #358751] Thu, 13 November 2008 01:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Silver wrote on Wed, 12 November 2008 13:42
Based on this string, I dynamically create the temporary table and filling it with values (just the value '1').

At the very end, I creating a SELECT Query from this temporary table Smile



I hope your new solution does not include these steps.. Creating objects at runtime is a big NO in Oracle. You shouldn't even consider it.
Re: Single ROW Query - but not all columns [message #358995 is a reply to message #358659] Thu, 13 November 2008 03:40 Go to previous messageGo to next message
Silver
Messages: 14
Registered: November 2008
Location: Middle of Germany
Junior Member
@Leonardh:
Can you show me, how you would do it with a decode?

@Frank: I´ve dropped the plan with the dynamically creating of the table Smile
Re: Single ROW Query - but not all columns [message #359011 is a reply to message #358659] Thu, 13 November 2008 05:30 Go to previous messageGo to next message
Silver
Messages: 14
Registered: November 2008
Location: Middle of Germany
Junior Member
Can anyone help me to change the following code in a code that doesnt need so much time for a result?
(I have 25 Querys, the time for the result is ~ 25 sec)

select 'Position 1' display,1 return_value
from customer
where customer.identnb='Smith'
and customer.column1=1
union all
select 'Position 2' display,2 return_value
from customer
where customer.identnb='Smith'
and customer.column2=1
union all
select 'Position 3' display,3 return_value
from customer
where customer.identnb='Smith'
and customer.column3=1
...


When the table looks as follow

identnb | column1 | column2 | column3 | column4 | ... | column25
----------------------------------------------------------------
Smith   |     1   |    1    |   0     |    1    | ... | 0
Miller  |     1   |    0    |   1     |    0    | ... | 0
Brown   |     0   |    0    |   0     |    0    | ... | 1


My result should be (in Case auf the Name Smith):

display    |   return_value 
----------------------------
Position 1 |         1
Position 2 |         2
Position 4 |         4
....
Re: Single ROW Query - but not all columns [message #359013 is a reply to message #359011] Thu, 13 November 2008 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "columns to rows" or "unpivot" or "row generator".

Regards
Michel
Re: Single ROW Query - but not all columns [message #359021 is a reply to message #358659] Thu, 13 November 2008 06:00 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Again Why are you going for the temperory tables ?

This example may help you .

SQL> CREATE OR REPLACE PROCEDURE SCOTT.SELECT_NON_ZERO_COL
  2  ( TABLE_NAME_V IN VARCHAR2 ,
  3    EXCEPT_COL_V IN VARCHAR2 ,
  4    refcur       OUT SYS_REFCURSOR,
  5    VAL_TO_FIND  IN NUMBER DEFAULT 0 )
  6  AS
  7  result_n Number (10) ;
  8  sql_str1  VARCHAR2(300);
  9  sql_str2  VARCHAR2(300);
 10  BEGIN
 11
 12  sql_str1 := 'SELECT :1 FROM '||UPPER(TABLE_NAME_V);
 13
 14  sql_str2 := 'SELECT '|| CASE WHEN EXCEPT_COL_V is NOT NULL THEN EXCEPT_COL_V||','
 15                               WHEN EXCEPT_COL_V is null THEN NULL
 16                          END;
 17
 18  FOR dat_dic IN ( SELECT COLUMN_NAME
 19                   FROM   USER_TAB_columns
 20                   WHERE  TABLE_NAME = UPPER(TABLE_NAME_V)
 21                   and    COLUMN_NAME<>UPPER(EXCEPT_COL_V))
 22  LOOP
 23
 24      sql_str1 := 'SELECT :1 FROM '||UPPER(TABLE_NAME_V);
 25
 26      sql_str1 := REPLACE ( sql_str1 , ':1',dat_dic.COLUMN_NAME) ;
 27
 28       EXECUTE IMMEDIATE sql_str1 INTO result_n ;
 29
 30  IF result_n = VAL_TO_FIND THEN
 31
 32      sql_str2 := sql_str2 || dat_dic.COLUMN_NAME||',';
 33
 34  END IF;
 35
 36  END LOOP;
 37
 38     sql_str2 := rtrim(sql_str2,',')||' From '||UPPER(TABLE_NAME_V);
 39
 40  Open refcur for sql_str2;
 41
 42  END;
 43  /

Procedure created.

SQL> select * from TEST;

   IDENTNB ONE TWO THR FOU FIV SIX SEV EIG NIN
---------- --- --- --- --- --- --- --- --- ---
       123   0   0   1   0   1   0   1   0   0

SQL> var refcur refcursor;
SQL> DECLARE
  2    TABLE_NAME_V VARCHAR2(200);
  3    EXCEPT_COL_V VARCHAR2(200);
  4    VAL_TO_FIND NUMBER;
  5
  6  BEGIN
  7    TABLE_NAME_V := 'TEST';
  8    EXCEPT_COL_V := 'IDENTNB';
  9    VAL_TO_FIND := 1;
 10
 11    SCOTT.SELECT_NON_ZERO_COL ( TABLE_NAME_V, EXCEPT_COL_V,:REFCUR, VAL_TO_FIND );
 12
 13  END;
 14  /

PL/SQL procedure successfully completed.


   IDENTNB THR FIV SEV
---------- --- --- ---
       123   1   1   1

SQL>


Its checks for the value and form string to open the cursor .

Smile
Rajuvan.
Re: Single ROW Query - but not all columns [message #359048 is a reply to message #358659] Thu, 13 November 2008 08:23 Go to previous messageGo to next message
Silver
Messages: 14
Registered: November 2008
Location: Middle of Germany
Junior Member
Thanks for your example Smile

I´ve created the procedure and the query, but where do I declare the refcursor? Sorry for my naive question...I´m just a Newbie in this field..
Re: Single ROW Query - but not all columns [message #359151 is a reply to message #358659] Thu, 13 November 2008 21:27 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You can define Ref cursor as a part of parent(calling) procedure and pass to the child(called) procedure. Also make sure that ref cursor is not closed in the child procedure so that you can process in the parent procedure from the OUT parameter.

Every starts as newbie. Check this for more details

Enjoy learning !!!

Smile
Rajuvan.

[Updated on: Thu, 13 November 2008 21:30]

Report message to a moderator

Re: Single ROW Query - but not all columns [message #359197 is a reply to message #358659] Fri, 14 November 2008 01:40 Go to previous messageGo to next message
Silver
Messages: 14
Registered: November 2008
Location: Middle of Germany
Junior Member
Thanks a lot for your help rajavu1 Smile

I´ve defined the cursor, so the parent looks like this:

 DECLARE
      TABLE_NAME_V VARCHAR2(200);
      EXCEPT_COL_V VARCHAR2(200);
      VAL_TO_FIND NUMBER;
      TYPE refcur1 IS REF CURSOR;
      refcur refcur1;
      
  BEGIN
  
  
      TABLE_NAME_V := 'TEST';
      EXCEPT_COL_V := 'IDENTNB';
      VAL_TO_FIND := 1;
 
      SELECT_NON_ZERO_COL ( TABLE_NAME_V, EXCEPT_COL_V, refcur, VAL_TO_FIND );
      
      END;


I´ve defined the procedure diretly in my database, it works great, too.

THe last problem: I can´t see any output (I´m using SQL Developer), only just a message that all was compiled.
Do I need some FETCH instruction to display the values?
Re: Single ROW Query - but not all columns [message #359220 is a reply to message #358659] Fri, 14 November 2008 03:23 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes . You have to fetch and display the records from the opened cursor . What I mentioned is for SQL*Plus . I am not sure about what to do in SQL Developer for Auto display of ref cursor.

Smile
Rjauvan.
Previous Topic: need a help in SQL
Next Topic: Variable in select "IN" clause (merged)
Goto Forum:
  


Current Time: Tue Dec 06 10:32:38 CST 2016

Total time taken to generate the page: 0.12155 seconds