How can I avoid this redundancy? [Sybase tSQL]

I'm kind of a newbie to Sybase tSQL, so I can't seem to figure this out.

Unfortunately, I have to use a bunch of nested queries to get data from a database I didn't create. My stored procedure is become rather huge because of the requirements my client is giving me which involves pulling bits of data from all sorts of random tables. Anyway...

Is there a way to simplify this?

...<snip>...
'varSomeVariable1' =
CASE WHEN CARD_FILE.company_nm = THEN
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 1)
ELSE
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 2)
END,
'varSomeVariable2' =
CASE WHEN CARD_FILE.company_nm = THEN
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 2)
ELSE
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 3)
END,
...<snip>...

Note that this is greatly simplified so as to not make it too confusing for everyone.

As you can see, the only difference between the nested SQL statements is the value of the 'borrower_no' in the WHERE clause... is there a better way to do this so I can avoid writing two complete SQL statements for each value I'm trying to get? I.e., can I have a conditional statment in a WHERE clause?See if the COALESCE keyword is in sybase and see if that will help you re-write the code. Using COALESCE along with LEFT JOINs have help me make query simpler.

Note without out the from and where clause, people can only guess on what could help. I mean that info is needed to know if the two tables are related to one another. If they are not related to one another I see no way to help you. And, I think that you are in trouble because a database poor design is harder to fix than stored proc bad design.

IN this case and most cases, knowing the Primary and Unique Keys would be great help to helping you.

Tim S|||Hi,
You can use decode in your where clause..

i.e
SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE
BORROWER.borrower_no = DECODE(varSomeVariable1,CARD_FILE.company_nm , 1,2)

Hope this helps you.|||Hi Shelva;

Isn't Decode() an Oracle function? I can't find it in the Sybase tSQL documentation :( I sure which I could use it 'cause I think that would solve my problem!|||DECODE is the same thing as a flattened-out case.

You can do the same thing with case, e.g. (I don't know if this solves the problem, but here is how to re-write the Oracle SQL):
SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE
BORROWER.borrower_no = CASE varSomeVariable1 WHEN CARD_FILE.company_nm THEN 1 ELSE 2 END|||Thanks MattR! That worked swell :cool: I didn't know you could use CASE in that way (i.e., in the WHERE clause.)sql

how are these queries evaluted differently by sql server?

I'm looking for the minimum date of an entry into a history table. The table contains multiple entries for the customer and the item with an activation and deactivation date for each entry.

I could use the following:

select customerId, item, min(activationDate) from history group by customerId, item

or a sub query

select customerId, item, activationDate

from history h1

where activationDate=(select min(activationDate) from history h2 where h2.customerId=h1.customerId and h2.item=h1.item)

How are these two queries parsed differently by SQL.

They return a different number of results.

Thanks,

karen

I'm guessing that customerId and item do not uniquely define a record in the table, is this true? If so, that would explain the difference in the amount of rows between the two result sets.

-The first query finds the minimum activationDate for each unique customerId and item.

-The second query finds the minimum activationDate for each customerId and item.

-If you add a group by customerId, item to the second query you should have matching result sets.

The inner query of the correlated subquery is processed once per record of the outer query.

|||

If you have the following data in the table:

CustomerID - Item - ActivationDate

1 1 1/1/2007

1 1 1/1/2007

The first query will return one record while the second query will return two. This is because (as the above post indicates), that the first query is returning a true grouping while the second will return duplicate records if there is duplicate data in the table.

|||

Bcs there are duplicate entries available on your database..

You can force DISTINCT class to fix this..

Sample..

Create Table #samplehistory (

[customerId] int ,

[item] int ,

[activationDate] datetime

);

Insert Into #samplehistory Values('1','1','1/1/2007');

Insert Into #samplehistory Values('1','1','1/2/2006');

Insert Into #samplehistory Values('1','1','1/3/2006');

Insert Into #samplehistory Values('1','2','1/11/2007');

Insert Into #samplehistory Values('1','2','1/11/2003');

Insert Into #samplehistory Values('1','2','1/11/2002');

select

customerId,

item,

min(activationDate) activationDate

from

#samplehistory

group by

customerId, item

select

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

After duplicating one of the value.. You are result is correct but there are dupicate data in the result

Insert Into #samplehistory Values('1','2','1/11/2002');

select

customerId,

item,

min(activationDate)

from

#samplehistory

group by

customerId, item

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

select

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

122002-01-11 00:00:00.000

*/

The group by class force the First query to avoid the duplicates (already distincted values are return).

After Distinct on second query,

Code Snippet

select distinct

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

|||

Thank you very much for your help. I really appreciate the time.

Would you recommend a book for dealing with these kinds of sublties in SQL?

Karen

|||

Inside SQL Server 2005 T-SQL Querying by Itzik Ben-Gan is very good.

|||Ken Henderson's "The Guru's Guide to Transact-SQL" is also very good.