ACCESS IF IN SQL 1

[hbez]

I am using the SQL equivalent of IF in MS Access in a ADOQuery:

IIf(Flights.TOWeight>=5000,80,30) AS TAX FROM...

I believe this is for backward compatibility. How would one write this in 'pure' SQL, disregarding the need for backward compatibility?

Hannes

 

[Glenn9999]

IIF is a Microsoft Access specific extension and has no value to real "pure" SQL.

What it does is the effect of an If/Then phrase, from what I understand researching it as it relates to a specific field.

If Flights.ToWeight >= 5000 then
  Tax := 80
else
  Tax := 30;

To emulate this with pure SQL would be a nightmare for a number of reasons, so it would be best to put this into programming code and call it a day.

Measurement is not management.

 

[softhemc]

What is wrong with the SQL CASE Statement??

CASE Flights.ToWeight 
  WHEN >= 5000 THEN 80
-- include as many WHENs as you need
  ELSE 30 
END AS Tax

 

[lespaul]

Access doesn't recognize the CASE statement...only IIF



Leslie

Come join me at New Mexico Linux Fest!

 

[hbez]

All I could find was also the If/begin/end but I could not figure out how to put that into SQL. Is IIF not perhaps ADO specific rather than Access specific, since the statement
SELECT Surname,Title, TOWeight, ' +
'IIf(Flights.TOWeight>=5000,80,30) AS TAX FROM Flights
works just fine in a Delphi app using ADO?
The same question applies to Datediff.
Guess the question is if these SQL statements will work if Access is not installed, but with MDAC installed, on a PC?

 

[whosrdaddy]

it depends on the database you are targetting.

your statement will only work on ms access databases (ms access doesn't have to be installed, since ADO comes with MDAC)

I have several applications that can target MySQL or MsSQL databases. 90% of the SQL statements are the same on those 2 platforms, but you have to cover the other 10% (datetime functions for example).

so if you want to make your code cross-db platform compatible, restrict your SQL command set and code the db specific implementations in Delphi.

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[roo0047]

@daddy - slightly of topic, but have wondered if you've encountered this problem with some MySQL functions.

I am sometimes sloppy in queries with table and field names regarding case. However, I've found that some MySQL functions are picky when it comes to case and will fail if not exact. Function COUNT() is one in particular that gave me fits recently. The error returned was misleading, saying the 1st field was not found, when the first field was not part of the query.

I should also note that I use ZeosLib exclusively.

Roo
Delphi Rules!

 

[whosrdaddy]

Hi Roo,

zeos uses the MySQL lib driver (=direct access) right?
I looked at zeos but ADO stays my fav platform (most of my tools/units/utilities are built upon ADO)
I only use MySQL with ADO, had some problems with 3.51 ODBC early driver versions doing strange things but latest versions are stable.

Can you give a small query example concerning your problem? I want to test this via ADO.
Since I respect casing on all my projects I don't have encountered this problem yet.
Remember that customers can decide to have a case sensitive database (yes they DO exist ).

/Daddy



-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[roo0047]

Yes, as long as my apps can find libmySQL.dll it will run. I've had nightmares over the years, installing ODBC drivers on client PCs, only to have them call back a day later and want me to do it again for another computer. Afterwards, it's all my fault when an unrelated app or database fails.

With:

    SQL.Add('CREATE TABLE  "qci"."invoice" (');
    SQL.Add('  "Rec" varchar(10) NOT NULL default '',');
    SQL.Add('  "InvTot" decimal(8,2) default NULL,');
    SQL.Add('  PRIMARY KEY  ("Rec")');
    SQL.Add(') ENGINE=InnoDB DEFAULT CHARSET=latin1;');
    Open

This, though incorrect case, always works:

  ZQuery.SQL.Add('SELECT * FROM Invoice WHERE invtot > 0;');

Adding function Count() caused bizzar error code:

  ZQuery.SQL.Add('SELECT Count(*) as Cnt FROM Invoice WHERE invtot > 0;');

Error was something like ' Field "Rec" not found in TABLE "qci.invoice" '

Corrected query that works:

  ZQuery.SQL.Add('SELECT Count(*) as Cnt FROM invoice WHERE InvTot > 0;');

Roo
Delphi Rules!

 

[roo0047]

I should have noted that my ODBC expertise is dated. (Not dates back to...) W95/98

Has the distribution process changed any?

Roo
Delphi Rules!