ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
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.
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...)
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:
WHEN year='97' THEN 0
WHEN year='02' THEN 1
WHEN year='98' THEN 2
END As OrderValue
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.
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.
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:
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.
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 ...
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.
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.
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...
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*/
where H.contratc# = D.contract#
group by H.contract#;