Home » SQL & PL/SQL » SQL & PL/SQL » hoe to select the concatenated column
icon5.gif  hoe to select the concatenated column [message #267038] Wed, 12 September 2007 05:54 Go to next message
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

Re: hoe to select the concatenated column [message #267041 is a reply to message #267038] Wed, 12 September 2007 06:05 Go to previous messageGo to next message
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;
Re: hoe to select the concatenated column [message #267043 is a reply to message #267038] Wed, 12 September 2007 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, you realize this is a bad design.

Regards
Michel
Re: hoe to select the concatenated column [message #267051 is a reply to message #267043] Wed, 12 September 2007 06:30 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Otherwise you can use execute immediate also Cool
Cheers
Soumen
Re: hoe to select the concatenated column [message #267055 is a reply to message #267051] Wed, 12 September 2007 06:40 Go to previous messageGo to next message
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.
Re: hoe to select the concatenated column [message #267060 is a reply to message #267055] Wed, 12 September 2007 06:46 Go to previous messageGo to next message
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
Re: hoe to select the concatenated column [message #267063 is a reply to message #267060] Wed, 12 September 2007 06:53 Go to previous messageGo to next message
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
Re: hoe to select the concatenated column [message #267064 is a reply to message #267060] Wed, 12 September 2007 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: hoe to select the concatenated column [message #267110 is a reply to message #267064] Wed, 12 September 2007 09:05 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Wed, 12 September 2007 06:55
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).



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.
Re: hoe to select the concatenated column [message #267277 is a reply to message #267038] Wed, 12 September 2007 23:17 Go to previous message
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.
Previous Topic: viewing parameters
Next Topic: Calling Function
Goto Forum:
  


Current Time: Sun Dec 04 20:48:51 CST 2016

Total time taken to generate the page: 0.09524 seconds