LinuxQuestions.org
View the Most Wanted LQ Wiki articles.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 10-29-2004, 08:45 AM   #1
smaida
Member
 
Registered: Apr 2004
Location: Richmond, VA - USA
Distribution: Debian
Posts: 62

Rep: Reputation: 15
Sql + Oracle Select If ????


Hello All,

I need some assistance and I am not sure where to begin. I have a list of contracts #s that had contracts in 2004. (result of a simple select statement on three tables) I need to find history against each one of those contracts from an oracle db.

I basically need to do this...

For each contract# in list
select contract from table where date is 1996 and contract = contract#
if no result
select contract from table where date is 1997 and contract = contract#
if no result
select contract from table where date is 1998 and contract = contract#

My goal is to select a contract from 2004 and find a matching contract from a previous year with a preference as to what year. I am searching 20 years of contract history and the dates I prefer to choose from jump around.

I have to write the query to work from SQL+.

Thanks for the help.
-Shawn
 
Old 10-29-2004, 08:53 AM   #2
acid_kewpie
Moderator
 
Registered: Jun 2001
Location: UK
Distribution: Gentoo, RHEL, Fedora, Centos
Posts: 43,415

Rep: Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968
looks like you want to move towards aggregate functions. if you use a min(date) function then you can get the oldest year for each contract number:
Code:
select contract, min(year) from table where contract = &contract contract
is that what you want? to find the oldest year a contract existed?? you've not been particuarly clear with what you want...
 
Old 10-29-2004, 09:23 AM   #3
smaida
Member
 
Registered: Apr 2004
Location: Richmond, VA - USA
Distribution: Debian
Posts: 62

Original Poster
Rep: Reputation: 15
I appologize for not being clear.

The problem is that the dates I prefer are not in order so I don't beleive Min() would work.

The dates are as follows:

96
97
98
95
99
94
93
00
92
01
02
03
04

I am looking for the first time the contract appears given the dates in the order above.

Meaning that if I am looking for contract A, I would prefer history from 97 over 92. Or I would prefer 01 over 04.

Thanks for the reply.
-Shawn
 
Old 10-29-2004, 02:31 PM   #4
deiussum
Member
 
Registered: Aug 2003
Location: Santa Clara, CA
Distribution: Slackware
Posts: 895

Rep: Reputation: 32
Why not just select for all of the dates you want to search, and order by the date time... Then you get all potential contracts in one query result, and the first row should be the oldest (or newest if you sort descending...)

Edit:
Maybe I should have read your description closer... doesn't sound like you necessarily want either the newest or oldest in a set... Hmmmm... Have to think about that one a bit more.

Ok... how about using a CASE in your select, combined with an order by to do something like so:

Code:
SELECT  *, 
   CASE  
      WHEN year='97' THEN 0
      WHEN year='02' THEN 1
      WHEN year='98' THEN 2
       -- ETC
   END As OrderValue
FROM contracts
WHERE year in ('97', '02', '98') and ContractID='id'
ORDER BY OrderValue
You'll probably have to make sure your DB of choice supports the CASE statement, though. I know DB2 and MS SQL Server both do, but not sure about Oracle, MySql, etc.

Last edited by deiussum; 10-29-2004 at 02:46 PM.
 
Old 10-29-2004, 04:22 PM   #5
smaida
Member
 
Registered: Apr 2004
Location: Richmond, VA - USA
Distribution: Debian
Posts: 62

Original Poster
Rep: Reputation: 15
Thanks for the reply. Wouldn't the Case Statement still return multiple records for each contract. If the contract shows up in 97,98 and 99 would the CASE statement return all results? I will have to look in to the use of CASE statements.

Thanks for the help. I will go do some reading.

-Shawn
 
Old 10-29-2004, 04:34 PM   #6
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,004
Blog Entries: 11

Rep: Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903
Quote:
Originally posted by smaida
I appologize for not being clear.

The problem is that the dates I prefer are not in order so I don't beleive Min() would work.

The dates are as follows:

96
97
98
95
99
94
93
00
92
01
02
03
04

I am looking for the first time the contract appears given the dates in the order above.

Meaning that if I am looking for contract A, I would prefer history from 97 over 92. Or I would prefer 01 over 04.

Thanks for the reply.
-Shawn
So you're not actually using a date field for the years?
That's very poor style mate ... at least you could have
used 4 digit years, which will give you another 8000
years before you run into this problem again ...



Cheers,
Tink
 
Old 10-29-2004, 04:48 PM   #7
smaida
Member
 
Registered: Apr 2004
Location: Richmond, VA - USA
Distribution: Debian
Posts: 62

Original Poster
Rep: Reputation: 15
It's not my DB, I am just pulling data from it. The reason for the random dates has something to do with the validity of the contracts during each one of those years. Due to the business some years contracts are more reliable than others. I am matching each current contract to a contract in the past, looking for a specific pattern in the data. I already pulled the data using multiple queries and some temporary tables, but I would like to learn how to roll the comparison in to one query.

-Thanks
Shawn
 
Old 10-30-2004, 12:18 AM   #8
deiussum
Member
 
Registered: Aug 2003
Location: Santa Clara, CA
Distribution: Slackware
Posts: 895

Rep: Reputation: 32
Quote:
Originally posted by smaida
Thanks for the reply. Wouldn't the Case Statement still return multiple records for each contract. If the contract shows up in 97,98 and 99 would the CASE statement return all results? I will have to look in to the use of CASE statements.

Thanks for the help. I will go do some reading.

-Shawn
Yes, if you had contracts for multiple years, you would still get multiple rows, but since you order them based on the years you are most interested in, you really only need to look at the first row. You could also get back just one row by using Oracle's equivalent to the MS SQL Server TOP 1 statement...

Also, my skills with PL/SQL for Oracle are severely lacking, but maybe there is something in there you could use as another alternative...

Last edited by deiussum; 10-30-2004 at 12:23 AM.
 
Old 10-30-2004, 01:18 AM   #9
smaida
Member
 
Registered: Apr 2004
Location: Richmond, VA - USA
Distribution: Debian
Posts: 62

Original Poster
Rep: Reputation: 15
deiussum

Thanks again,

I'll see what I can figure out monday when I get back to work.

-Shawn
 
Old 10-30-2004, 04:53 AM   #10
adanali
LQ Newbie
 
Registered: Oct 2004
Posts: 1

Rep: Reputation: 0
Hi All,
this is weird, I was somehow browsing for ALSA support for Cretive Labs's Audigy Sound card and I ended up here, anyways, I think a dynamic table feature of oracle should be a good help, here how to do it, please let me know if it woks out, I'm kinda rusty so please check the syntax, or I most likely misunderstood the problem so ignore it.

for given table H , historical table_H to get all the needed records
and the results from the contract# to be of three tables table_A, table_B,table_C


The actual SQL block below can be run from SQL+ or PL/SQL , if you cut and paste it should work with small modification to the actual table names


Select H.column_list,MIN(to_char(H.date_column,'YYYY-MM-DD') ) -- the format of to_char and to_date can be changed please check the online manuals.Also I assumed that the date is -- stored as date in your oracle table if it's a numerical value or a vharchar[?] then you could still use to_char with proper format.
from table_H H,
( Select A.contract# -- I assume that the contract# is selected from tale_A
from table_A A, table_B B, table_C C
where /* all the join stuff goes in here*/
) D
where H.contratc# = D.contract#
group by H.contract#;
 
Old 10-31-2004, 09:35 AM   #11
vasudevadas
Member
 
Registered: Jul 2003
Location: Bedford, UK
Distribution: Slackware 11.0, LFS 6.1
Posts: 519

Rep: Reputation: 30
The solution is to use the DECODE statement. You should order your query like this:

Code:
ORDER BY DECODE(TRUNC(contract_date,'YYYY'),
'01-JAN-1996',1,
'01-JAN-1997',2,
'01-JAN-1998',3,
'01-JAN-1999',4,
'01-JAN-1995',5,
'01-JAN-1994',6,
'01-JAN-1993',7,
'01-JAN-2000',8,
'01-JAN-1992',9,
'01-JAN-2001',10,
'01-JAN-2002',11,
'01-JAN-2003',12,
'01-JAN-2004',13,
14)
On edit:

a better solution would be to create a new table that contains your arbitrary date ordering:

Code:
CREATE TABLE date_order (year DATE NOT NULL, date_order NUMBER NOT NULL)
and make the year column the primary key. Then populate it with the dates and the respective orders:

'01-JAN-1996',1,
'01-JAN-1997',2,
'01-JAN-1998',3,
'01-JAN-1999',4,
'01-JAN-1995',5,
'01-JAN-1994',6,
'01-JAN-1993',7,
'01-JAN-2000',8,
'01-JAN-1992',9,
'01-JAN-2001',10,
'01-JAN-2002',11,
'01-JAN-2003',12,
'01-JAN-2004',13

like that. Then rewrite your other query to join to this new table and order by the date_order column, like this:

Code:
SELECT foo
FROM contract_table c, date_order o
WHERE bar
AND TRUNC(c.date) = o.year(+)
ORDER BY NVL(o.year, 99999)
This method has the advantage that you can add new years into the ordering without having to rewrite your code.

Last edited by vasudevadas; 10-31-2004 at 09:41 AM.
 
Old 12-15-2004, 09:13 PM   #12
smaida
Member
 
Registered: Apr 2004
Location: Richmond, VA - USA
Distribution: Debian
Posts: 62

Original Poster
Rep: Reputation: 15
Thanks vasudevadas, that works well.
-Shawn
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Some help with oracle select statements please! genderbender Programming 5 04-16-2005 10:08 AM
Oracle SQL+ darkRoom Programming 2 05-20-2004 10:01 PM
Bizarre SQL select query acid_kewpie Programming 6 01-20-2004 01:47 PM
Oracle SQL IDE grayswander Linux - Software 4 12-05-2003 12:17 PM
SQL and Oracle Rutnut Linux - General 3 04-07-2002 01:39 AM


All times are GMT -5. The time now is 01:01 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration