Home » SQL & PL/SQL » SQL & PL/SQL » column (oracle10g)
column [message #427338] Thu, 22 October 2009 02:27 Go to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
I have to display a value i am passing as a single column which is not existing in the table.
For eg:
  
select 'ABC',
      'XYZ',
      '134'
 from dual;

output should be
  ABC
  XYZ
  134


Many Thanks for your help
Re: column [message #427341 is a reply to message #427338] Thu, 22 October 2009 02:35 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

I have to display a value i am passing as a single column which is not existing in the table.



You already have answer to your question.

SQL> Select SYSDATE from dual;

SYSDATE
---------
22-OCT-09

select 'a' from dual; 

etc etc..


Thanks
Re: column [message #427343 is a reply to message #427338] Thu, 22 October 2009 02:51 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
SQL>r
  1  select 'abc' expr from dual
  2  union
  3  select 'def' from dual
  4  union
  5* select 'xyz' from dual

EXP
---
abc
def
xyz

3 rows selected.


regards,
Delna
Re: column [message #427346 is a reply to message #427343] Thu, 22 October 2009 02:57 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The easiest solution to get multiple rows is to select multiple records from dual by using a 'union all' construction:
SQL> select 'row 1' thecolumn from dual union all
  2  select 'row 2' thecolumn from dual union all
  3  select 'row 3' thecolumn from dual union all
  4  select 'row 4' thecolumn from dual union all
  5  select 'row 5' thecolumn from dual;

THECO
-----
row 1
row 2
row 3
row 4
row 5

SQL>
But you could use a string splitting mechanism:
SQL> COL x FORMAT A30
SQL> VAR thestring VARCHAR2(50)
SQL> VAR thedelim  VARCHAR2(1)
SQL>
SQL> EXEC :thestring := '12,13,155'

PL/SQL procedure successfully completed.

SQL> EXEC :thedelim  := ','

PL/SQL procedure successfully completed.

SQL>
SQL> WITH theselect AS (
  2  SELECT SUBSTR(thestring, startpos, thelength) x
  3  FROM  ( SELECT thestring
  4               , startpos
  5               , NVL(
  6                      LEAD(startpos) OVER ( ORDER BY lv ) - 1
  7                    , LENGTH(thestring) + 1
  8                    ) endpos
  9               , NVL(
 10                      LEAD(startpos) OVER ( ORDER BY lv ) - 1
 11                    , LENGTH(thestring) +1
 12                    ) - startpos thelength
 13          FROM   ( SELECT lv
 14                        , thestring
 15                        , DECODE(lv
 16                                ,1, 1
 17                                ,INSTR(thestring,thedelim,1,lv-1)+1
 18                                ) startpos
 19                   FROM ( SELECT LEVEL lv
 20                               , REPLACE(:thestring,' ') thestring
 21                               , :thedelim thedelim
 22                          FROM   dual
 23                          CONNECT BY LEVEL <=   LENGTH(REPLACE(:thestring,' '))
 24                                              - LENGTH(REPLACE(REPLACE(:thestring,' '),:thedelim)) + 1
 25                        )
 26                 )
 27          ORDER  BY lv
 28        )
 29  )
 30  SELECT *
 31  FROM   theselect
 32  /

X
------------------------------
12
13
155

SQL>
SQL> exec :thestring := 'row 1, row 2, row 3, row 4, row 5, row 6'

PL/SQL procedure successfully completed.

SQL> /

X
------------------------------
row1
row2
row3
row4
row5
row6

6 rows selected.

SQL>
The code above is a copy of the code in this old thread.

The advantage of this last solution is that you can change the delimiter and the number of records without having to change the select.

MHE
Re: column [message #427353 is a reply to message #427338] Thu, 22 October 2009 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> column nl fold_after
SQL> set head off
SQL> set feed off
SQL> select 'ABC' nl, 'XYZ' nl, '134' nl from dual;
ABC
XYZ
134

Regards
Michel
Re: column [message #427357 is a reply to message #427353] Thu, 22 October 2009 03:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Nice trick, Michel. But:
1. you're displaying a single row. Granted, we don't know whether convey05 wants one or more rows.
2. you're making the client application (SQL*Plus) do the formatting.

SQL> select 'ABC' n1, 'XYZ' n1, '134' n1 from dual;

ABC
XYZ
134


1 row selected.

SQL>
Note the '1 row selected'.

You could, as well do this:
SQL> select replace('ABC,XYZ,134', ',', chr(10)) x
  2  from dual
  3  /

X
-----------
ABC
XYZ
134
That displays the same result and is independent from the client.

MHE

[Updated on: Thu, 22 October 2009 03:49]

Report message to a moderator

Re: column [message #427358 is a reply to message #427357] Thu, 22 October 2009 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I just give an answer that fit OP's requirement with the elements it give assuming others are free. It was just to add an option to all that have already been mentioned.

Regards
Michel
Re: column [message #427361 is a reply to message #427358] Thu, 22 October 2009 04:01 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I know, Michel. I wasn't criticizing your reply, but rather clarifying your solution (pure formatting, no extra rows) and pointing out a limit (SQL*Plus dependency). Wink

It would be nice if 'convey05' would clarify his requirements: one row for each 'element' or a single row with carriage returns in the column value. After all these years, people still have problems in writing down their requirements.

Wait a minute...I'll check: ./fa/1659/0/

MHE
Previous Topic: How to pass sql statement between procedure?
Next Topic: Primary and Foreign key relationship
Goto Forum:
  


Current Time: Sat Dec 10 09:25:31 CST 2016

Total time taken to generate the page: 0.07516 seconds