What Conditions are in SQL Queries?

Discussion in 'Mac Programming' started by LtRammstein, Jun 28, 2006.

  1. macrumors 6502a

    LtRammstein

    Joined:
    Jun 20, 2006
    Location:
    Denver, CO
    #1
    Hey all! Sadly to say, I use MS Access at work and I'd love to know if SQL supports conditional statements like the IF statement. If so, how is it implemented? I know MySQL can use it, that's why I'm wondering.

    Steve
     
  2. Moderator emeritus

    robbieduncan

    Joined:
    Jul 24, 2002
    Location:
    London
    #2
    Sybase Transact SQL certainly does. Not sure about Oracle. MS SQL Server (which was based of a version os Sybase some time back) probably supports Transact-SQL syntax as well.

    You can say (in a stored procedure)
    Code:
    declare @myVar int
    ...
    if (@myVar=0)
    begin
     ...
    end
    else
    begin
     ...
    end
    
    You can also use Case statements in selects (and updates) like this:
    Code:
    select case when (columnA=value)
                     then 'yes'
                     else 'no'
                     end
    ...
    
     
  3. macrumors 68000

    savar

    Joined:
    Jun 6, 2003
    Location:
    District of Columbia
    #3
    I don't think ANSI SQL (the baseline standard) supports it, but some variants do. Try googling for Access and see if you can find a syntax for it.

    There's probably a better way to do what you're trying to do though. Maybe if you told us more about your problem somebody could suggest a better way to solve it?
     
  4. thread starter macrumors 6502a

    LtRammstein

    Joined:
    Jun 20, 2006
    Location:
    Denver, CO
    #4
    The problem is bigger than you think. Having a form with a few subforms. One of the subform is a signature form. In that has the initials of a programmer, and their name. I was told by my boss that what I originally had as the "Programmer Dept." is actually the pre-emptive initials of the programmer. This is on the main form.

    So what I want it to do is besides having it be the Master Field to the Child field in the signature form, I want the signature form to automatically put the programmer's name in the text field next to it. Mind you the data cannot be duplicated.

    I've been pounding my head on this problem for a few days now, and I really want to destroy the database they gave me and rebuild it from scratch.

    Plus, is there a way that I can do this stuff without using VBA?

    Steve
     
  5. macrumors 6502

    Joined:
    Feb 16, 2006
    Location:
    Blighty
    #5
    Yup it does. The IF statement is exactly the same on MSSQL. Never used the while construct before.

    Code:
    SELECT ISNULL(Col, "default value") FROM x
    
    The statement above is quite useful too. Basically selects the column "Col" unless it's null, in which case it selects whatever is in the second parameter.
     
  6. thread starter macrumors 6502a

    LtRammstein

    Joined:
    Jun 20, 2006
    Location:
    Denver, CO
    #6
    Thanks for the help! That's all I needed to know. I'll make a quick query and see if it works. (I bet it does). Man, I hate MS Access...

    Steve
     
  7. macrumors 6502a

    maxvamp

    Joined:
    Sep 26, 2002
    Location:
    Somewhere out there
    #7
    MSSQL DOES make available IF statements, but I thought I saw someone mention a WHILE LOOP.

    A Major performance warning.... Do your best not to use cursors ( often used with While...If statements ) when possible, and benchmark anything that looks like it might.

    The concept of a cursor or While Loops in T-SQL is really slow, as you are traversing every row of a result set, and Indexes become moot.

    Max.
     
  8. thread starter macrumors 6502a

    LtRammstein

    Joined:
    Jun 20, 2006
    Location:
    Denver, CO
    #8
    Hey all, thanks for those who posted! I tried them out, but it seems MS Access 2003 doesn't do a lot of that stuff. What I ended up do is creating a specialized query for the table I wanted and it worked!

    Again, thanks for the inputs!

    Steve
     
  9. Moderator emeritus

    robbieduncan

    Joined:
    Jul 24, 2002
    Location:
    London
    #9
    The solution to this is to select the data you want to loop over into a temporary table with an idenity column. The create an index on the identity column. Loop over the identity column values doing a select inside the while loop to get the data. This is fast!
     

Share This Page