Home » SQL & PL/SQL » SQL & PL/SQL » help with select statement (oracle 8 database)
help with select statement [message #311158] Thu, 03 April 2008 10:44 Go to next message
pink
Messages: 23
Registered: July 2003
Junior Member
My select statement returns multiple rows and I want it to retrieve the one with the latest semester code. how do I do that?

select (something)
FROM RTATC,BTATC,TVSBGI
WHERE RTATC_SBGI_CODE = BTATC_SBGI_CODE
AND RTATC_SBGI_CODE = TVSBGI_CODE
AND RTATC_CRSE_NUMB_TRNS = BTATC_CRSE_NUMB_TRNS
AND RTATC_SUBJ_CODE_TRNS = BTATC_SUBJ_CODE_TRNS
and RTATC_CRSE_NUMB_TRNS ='M109'
and BTATC_TERM_CODE_EFF_TRNS = (I want the one with the greatest semester here which is 200540 from below results)

RTATC_CRSE_NUMB_TRNS RTATC_TERM_CODE_EFF_TRNS
M109 200540
M109 200210

Can you please help
Re: help with select statement [message #311160 is a reply to message #311158] Thu, 03 April 2008 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How do you define a semester?

Regards
Michel
Re: help with select statement [message #311162 is a reply to message #311160] Thu, 03 April 2008 11:01 Go to previous messageGo to next message
pink
Messages: 23
Registered: July 2003
Junior Member
year= 2005 + semester code (can be 10,20,30)
Just need the greatest number basically.
Re: help with select statement [message #311164 is a reply to message #311158] Thu, 03 April 2008 11:02 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
You have to select the highest value of BTATC_TERM_CODE_EFF_TRNS don't you?
If so yo have to get with this query:

select max(BTATC_TERM_CODE_EFF_TRNS)
from (the table it belongs)


And the complete code looks like these:

select (something)
FROM RTATC,BTATC,TVSBGI
WHERE RTATC_SBGI_CODE = BTATC_SBGI_CODE
AND RTATC_SBGI_CODE = TVSBGI_CODE
AND RTATC_CRSE_NUMB_TRNS = BTATC_CRSE_NUMB_TRNS
AND RTATC_SUBJ_CODE_TRNS = BTATC_SUBJ_CODE_TRNS
and RTATC_CRSE_NUMB_TRNS ='M109'
and BTATC_TERM_CODE_EFF_TRNS =(
select max(BTATC_TERM_CODE_EFF_TRNS)
from (the table it belongs));

I believe this should work

Joaquin
Re: help with select statement [message #311166 is a reply to message #311164] Thu, 03 April 2008 11:04 Go to previous messageGo to next message
pink
Messages: 23
Registered: July 2003
Junior Member
That will return the greatest semester from the whole table not from the results of the query...

I am trying to narrow the query down to 1 row from 2 rows... The difference in both queries is that the semester number is different...so I need the one with the greater semester number
Re: help with select statement [message #311169 is a reply to message #311158] Thu, 03 April 2008 11:09 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
Ok. I misunderstood you.
You could try this:
select (something)
FROM RTATC,BTATC,TVSBGI
WHERE RTATC_SBGI_CODE = BTATC_SBGI_CODE
AND RTATC_SBGI_CODE = TVSBGI_CODE
AND RTATC_CRSE_NUMB_TRNS = BTATC_CRSE_NUMB_TRNS
AND RTATC_SUBJ_CODE_TRNS = BTATC_SUBJ_CODE_TRNS
and RTATC_CRSE_NUMB_TRNS ='M109'
and BTATC_TERM_CODE_EFF_TRNS = max(BTATC_TERM_CODE_EFF_TRNS);


or you could try this one which I'm pretty sure will work:
select (something)
from(
    select (something)
    FROM RTATC,BTATC,TVSBGI
    WHERE RTATC_SBGI_CODE = BTATC_SBGI_CODE
    AND RTATC_SBGI_CODE = TVSBGI_CODE
    AND RTATC_CRSE_NUMB_TRNS = BTATC_CRSE_NUMB_TRNS
    AND RTATC_SUBJ_CODE_TRNS = BTATC_SUBJ_CODE_TRNS
    and RTATC_CRSE_NUMB_TRNS ='M109'
    order by BTATC_TERM_CODE_EFF_TRNS desc
)
where rownum <= 1;
Re: help with select statement [message #311171 is a reply to message #311169] Thu, 03 April 2008 11:13 Go to previous messageGo to next message
pink
Messages: 23
Registered: July 2003
Junior Member
I need to ultimately remote the RTATC_CRSE_NUMB_TRNS ='M109' and it should work for all course numbers... so this will work?
Re: help with select statement [message #311172 is a reply to message #311158] Thu, 03 April 2008 11:15 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
What do you mean with this:
Quote:
I need to ultimately remote the RTATC_CRSE_NUMB_TRNS ='M109'

Especifically the ultimately remote thing. Can't you try the SQL statements now?
Edit:
The query selects the same you use to select before but it returns it order by the BTATC_TERM_CODE_EFF_TRNS column, after that it selects the top row from that select so in the end it selects the row with the highest semester value.
Joaquin

[Updated on: Thu, 03 April 2008 11:18]

Report message to a moderator

Re: help with select statement [message #311179 is a reply to message #311172] Thu, 03 April 2008 11:33 Go to previous messageGo to next message
pink
Messages: 23
Registered: July 2003
Junior Member
Oops remove not remote

select (something)
FROM RTATC,BTATC,TVSBGI
WHERE RTATC_SBGI_CODE = BTATC_SBGI_CODE
AND RTATC_SBGI_CODE = TVSBGI_CODE
AND RTATC_CRSE_NUMB_TRNS = BTATC_CRSE_NUMB_TRNS
AND RTATC_SUBJ_CODE_TRNS = BTATC_SUBJ_CODE_TRNS
and RTATC_CRSE_NUMB_TRNS ='M109'---GET RID OF THIS
and BTATC_TERM_CODE_EFF_TRNS = (I want the one with the greatest semester here )


So I will have many more course numbers and the query will return all course numbers with the latest semester....

[Updated on: Thu, 03 April 2008 11:34]

Report message to a moderator

Re: help with select statement [message #311183 is a reply to message #311179] Thu, 03 April 2008 11:42 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
So what is what you want?
Let me explain myself to see if i understood you.
You need the highest semester value from the query because you want to know the last semester that course took place.

So for a given course number you want to get the last semester the course took place.
Am I right?

If I am right you could use a stored procedure/function with a variable for the course name(the M109 value), also ,the query I wrote you should work but it will return one row only.

Joaquin
Re: help with select statement [message #311187 is a reply to message #311183] Thu, 03 April 2008 11:58 Go to previous messageGo to next message
pink
Messages: 23
Registered: July 2003
Junior Member
You are right... need the highest semester value from the query because you want to know the last semester any course took place.

I dont need a course name at all... I need to just remove RTATC_CRSE_NUMB_TRNS ='M109' and then I will get all courses... but all should have latest semester... thats the problem

Cant I do this with SQL?
Re: help with select statement [message #311197 is a reply to message #311187] Thu, 03 April 2008 12:21 Go to previous message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
So each course should have its latest semester?
It will be very difficult to do this with sql alone.
Can you try the queries I'm writing?
Maybe this. I'm not sure if it will work or compile.
    select (something)
    FROM RTATC r1 ,BTATC b,TVSBGI tv
    WHERE RTATC_SBGI_CODE = BTATC_SBGI_CODE
    AND RTATC_SBGI_CODE = TVSBGI_CODE
    AND RTATC_CRSE_NUMB_TRNS = BTATC_CRSE_NUMB_TRNS
    AND RTATC_SUBJ_CODE_TRNS = BTATC_SUBJ_CODE_TRNS
    AND BTATC_TERM_CODE_EFF_TRNS =
         (SELECT MAX(BTATC_TERM_CODE_EFF_TRNS) 
          FROM BTATC,RTATC r,any_table_neccesary 
          where r.RTATC_CRSE_NUMB_TRNS = r1.RTATC_CRSE_NUMB_TRNS)
    order by BTATC_TERM_CODE_EFF_TRNS desc;

Why can't you use PL/SQL?
Joaquin
Previous Topic: how to get A.M./ P.M. using to_char function?
Next Topic: SUBSTR with just numeric characters
Goto Forum:
  


Current Time: Sat Dec 03 20:40:35 CST 2016

Total time taken to generate the page: 0.18811 seconds