Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    14

    Unanswered: 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?

    Code:
    ...<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?
    Sincerely,
    Todd M. Taylor
    http://www.toddtaylor.com/

  2. #2
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    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
    Last edited by TimS; 11-22-03 at 02:42.

  3. #3
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    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.

  4. #4
    Join Date
    Oct 2003
    Posts
    14
    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!
    Sincerely,
    Todd M. Taylor
    http://www.toddtaylor.com/

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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,

    Matt

  6. #6
    Join Date
    Oct 2003
    Posts
    14
    Thanks MattR! That worked swell I didn't know you could use CASE in that way (i.e., in the WHERE clause.)
    Sincerely,
    Todd M. Taylor
    http://www.toddtaylor.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •