Home » SQL & PL/SQL » SQL & PL/SQL » selecting the column in subquery based on date
selecting the column in subquery based on date [message #300935] Mon, 18 February 2008 14:50 Go to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
i have a table scores with columns


year serial period1 period2 period3 period4 period5 period6
2001 12342 34 80 76 67 90 12
... .... .. .. .. .. .. ..

2008 23435 80 0 0 0 0 0

2008 only period 1 data is availble .
period 1 is for jan - feb , period 2 is for march - apr and so on.

I need to load the only column for that period.
that is suppose its 2006 and period is march-april ,
i need to load only those rows for year 2006 and column period2.
so it needs to be dynamic, not hardcoded, while using it in online view.

insert into final_salary
select ( select period1 from scores s where s.serial=t.serial
and year = extract (year from sysdate ) )

from registar t




i need help regarding this.. please let me know if you need more information.

[Updated on: Mon, 18 February 2008 15:18]

Report message to a moderator

Re: selecting the column in subquery based on date [message #300939 is a reply to message #300935] Mon, 18 February 2008 15:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you think your requirements are clear?
Quote:
I need to load the only column for that period.

which one?
Quote:
i have a table scores

Quote:
insert into final_salary
select ( select period1 from scores s where s.serial=t.serial
and year = extract (year from sysdate ) )
from registar t

2 statements, 3 tables, the second statement with tables that are not explained.

This is clear as mud.

In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel


Re: selecting the column in subquery based on date [message #300978 is a reply to message #300939] Mon, 18 February 2008 21:55 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
i have a table called SCORES with below columns and data.

Quote:

year serial period1 period2 period3 period4 period5 period6
2001 12342 34 80 76 67 90 12
... .. .. .. .. .. .. .. ..

2008 23435 80 0 0 0 0 0


I need to load the colmns depending for that period.
for jan-feb period1
mar-apr period2
May-Jun period3
Jul-aug period4
sep-oct period5
nov-dec period6




What i have tried till now
--------------------------
select ( select period1 from scores s where s.serial=t.serial
and year = extract (year from sysdate ) )
from registar t


so it needs to be dynamic, not hardcoded, while using it in online view.How can use a lookup table for taking the dates here.

[Updated on: Mon, 18 February 2008 21:56]

Report message to a moderator

Re: selecting the column in subquery based on date [message #300980 is a reply to message #300935] Mon, 18 February 2008 22:03 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
IMO, the table design is flawed.
The period columns should be changed to be rows:
PERIOD_ID
PERIOD_VALUE
Re: selecting the column in subquery based on date [message #300990 is a reply to message #300935] Mon, 18 February 2008 22:47 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
The desing is not good, making big troubles when inserting/updating the SCORES table.
Querying is easier using DECODE or CASE to get required column:
SELECT DECODE( <period>, 'jan-feb', period1,
                         'mar-apr', period2,
                         <up to 'nov-dec' and period6>
             ) period
FROM scores
WHERE year = <given_year>;

Not sure about <period> type and format: is it really those 6 string you posted? Use LOWER, if mixed case is possible. If it is different, change it regarding its real values.
Re: selecting the column in subquery based on date [message #301007 is a reply to message #300978] Tue, 19 February 2008 00:08 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel

Do you think your example is formatted?

Previous Topic: Function Access Problem
Next Topic: sequential string generation
Goto Forum:
  


Current Time: Sun Dec 04 06:21:47 CST 2016

Total time taken to generate the page: 0.05264 seconds