macOS What Conditions are in SQL Queries?

[LtRammstein]

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

 

[robbieduncan]

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)

declare @myVar int
...
if (@myVar=0)
begin
 ...
end
else
begin
 ...
end

You can also use Case statements in selects (and updates) like this:

select case when (columnA=value)
                 then 'yes'
                 else 'no'
                 end
...

 

[savar]

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

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?

 

[LtRammstein]

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

 

[PatrickF]

MS SQL Server (which was based of a version os Sybase some time back) probably supports Transact-SQL syntax as well.

Yup it does. The IF statement is exactly the same on MSSQL. Never used the while construct before.

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.

 

[LtRammstein]

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

 

[maxvamp]

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.

 

[LtRammstein]

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

 

[robbieduncan]

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.

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!