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