OraFAQ Forum: SQL & PL/SQL » hoe to select the concatenated column

Home » SQL & PL/SQL » SQL & PL/SQL » hoe to select the concatenated column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

hoe to select the concatenated column [message #267038]

Wed, 12 September 2007 05:54

niru.reddy
Messages: 2
Registered: September 2007
Location: MUMBAI

Junior Member

hi
I have got one table with columns
like WK1,WK2,Wk3------------------------WK37,wk38---WK52)
Those are week numbers
I want to pass date as a prarameter .now from that date i should calculate week number and that week number should match the column name as WK37 (if given date week number is 37) then it should retrieve that column's data
I am trying this way

select 'WK'||TO_CHAR(sysdate,'WW') from table name

i am finding week number from TO_CHAR(sysdate,'WW')

but i am not able to concatinate with'WK'
pls give me the solution

Report message to a moderator

Re: hoe to select the concatenated column [message #267041 is a reply to message #267038]

Wed, 12 September 2007 06:05

Cthulhu
Messages: 381
Registered: September 2006
Location: UK

Senior Member

select 'WK'||TO_CHAR(sysdate,'WW') from table name

will give you the literal string WK37 rather than the contents of the column WK37.

You will need to use dynamic SQL to format the statement you want e.g.

v_sql := 'select wk'||TO_CHAR(sysdate,'WW')||' from table';
open v_curs for v_sql;
fetch v_curs into v_result;

Report message to a moderator

Re: hoe to select the concatenated column [message #267043 is a reply to message #267038]

Wed, 12 September 2007 06:12

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Of course, you realize this is a bad design.

Regards
Michel

Report message to a moderator

Re: hoe to select the concatenated column [message #267051 is a reply to message #267043]

Wed, 12 September 2007 06:30

Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata

Senior Member

Otherwise you can use execute immediate also Cool

Cheers
Soumen

Report message to a moderator

Re: hoe to select the concatenated column [message #267055 is a reply to message #267051]

Wed, 12 September 2007 06:40

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

If you are going to be accessing this table a lot, I'd give serious consideration to building a view on top of it to split the WK01-52 columns down into seperate rows.

Report message to a moderator

Re: hoe to select the concatenated column [message #267060 is a reply to message #267055]

Wed, 12 September 2007 06:46

niru.reddy
Messages: 2
Registered: September 2007
Location: MUMBAI

Junior Member

yes sir i am going to use this table a lot pls help me
and all of my work on this table only

Report message to a moderator

Re: hoe to select the concatenated column [message #267063 is a reply to message #267060]

Wed, 12 September 2007 06:53

Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata

Senior Member

Please do not use IM Speak here.

You can retrive the data using execute immediate.
For example:

declare
vSql varchar2(200);
vValue varchar2(20);
begin
vSql := 'SELECT to_char(to_date('''||sysdate||'''),''WW'') from dual';
execute immediate  vSql into vValue;
dbms_output.put_line(vSql||'  '||vValue);
end;

Cheers
Soumen

Report message to a moderator

Re: hoe to select the concatenated column [message #267064 is a reply to message #267060]

Wed, 12 September 2007 06:55

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So please change the table.
Use only a week column to store the week number and a value column to store the associated value.
Otherwise you WILL suffer many performances problems (note I use WILL and not would as this is sure).

Regards
Michel

Report message to a moderator

Re: hoe to select the concatenated column [message #267110 is a reply to message #267064]

Wed, 12 September 2007 09:05

MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA

Senior Member

Michel Cadot wrote on Wed, 12 September 2007 06:55

Michel is 100% correct. If you do not alter this table design you will regret it for the whole time that you need to work with this application . Those that maintain it after you have moved on will curse you each and every day.

Report message to a moderator

Re: hoe to select the concatenated column [message #267277 is a reply to message #267038]

Wed, 12 September 2007 23:17

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Atomic is a word that is taught at the beginning while learning SQL.

Report message to a moderator

Previous Topic:	viewing parameters
Next Topic:	Calling Function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 15:43:21 CST 2024