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 and where should I create a mdf file if I installed a sql server 2005(Developer EditioN)

hi:

I am a newbie to sql server 2005 (developer edition). Now I installed both sql server and VS 2005 professional edition on my machine.

I just what to know how and where should I create a mdf file on my server or .net window project because I try to combine to it from a window application. Should I use the sql server management studio or the Visual Studio 2005 ? What is the generate steps to create one?

I am completely confused. Please help me, I cannot find related on line source.

Thank you, thank you

bigheadjj

That depends wheter you want to use a user instance with a SQL Server database or a SQL Server hosted instance. The Visual Studio Database can support both, whether a user or a server instance. Did you have the concept of a user instance or a server instance ? If not, I would suggest you reading the principles of that in the web.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Even if VS 2005 can connect to the server and create database files, I recommend that you use the SQL Server client tools (SSMS, sqlcmd, etc.) This gives you a good understanding of what is happening on the database side. Plus, using the SQL Server client tools, you can configure other database options which cannot be done using VS 2005|||

Jens

Thank you very much. I will start to read related principles as you suggested.

|||

bass_player

thank you for helping me. Your recommendation is very helpful.