Solved

Access query in MySQL - IIF and SUM function

Posted on 2007-03-23
5
5,404 Views
Last Modified: 2007-11-27
Hi,
I'm planning to have MySQL instead of Access 2k3 as back-end.
I'll keep the Access-DB as front-end.

I have mainly 3 types of queries in Access. Would like to see how they look like as a MySQL view.
(MySQL 5.0.27 / MyODBC 3.x and 5.0.11)

1.) with Access IIf function and some calculation:
========================================
SELECT
tblL5LAT.tblL5CAUTOID,
IIf(([LengteAfgekort]),[LengteAfgekort],[REALLengte]) AS BerLengte,
([BerLengte]*[REALStuks]) AS StrekkendeMetersPerLengteInPak
FROM tblL5LAT;

2.) with Access SUM-function (probably the same as Qry 1 ?!?)
====================================================
SELECT
tblL4LAT.LATPakID,
Sum(qryLATSubBer_A.StrekkendeMetersPerLengteInPak) AS SumOfStrekkendeMetersPerLengteInPak
FROM tblL4LAT LEFT JOIN (tblL5LAT LEFT JOIN qryLATSubBer_A ON tblL5LAT.tblL5CAUTOID = qryLATSubBer_A.tblL5CAUTOID) ON tblL4LAT.tbl4LATAUTO = tblL5LAT.tblL4LATID
GROUP BY tblL4LAT.LATPakID;

3.) Filter on a certain Access form-field. For report purposes.
==================================================
SELECT qryJaarOverzichtPRINT.*
FROM qryJaarOverzichtPRINT
WHERE (((qryJaarOverzichtPRINT.VerkoopcontractID)=[Forms]![frmVerkoopcontractOverzicht]![VerkoopcontractID]));
0
Comment
Question by:joop123456
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 

Author Comment

by:joop123456
ID: 18780822
I can split the question in 3 parts...
Just translate the first query and I will post 2 questions more.... (and link to them)
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 18780996
2 and 3 are the same as in access

the IIF translates to the use of the case statement

e.g.

case when  [LengteAfgekort] > 0 then [LengteAfgekort] else REALLengte end as ....
0
 

Author Comment

by:joop123456
ID: 18781181
Hi!

How would no.3 work in practice then.. I mean It's a form field from Access to filter on.
Since I would like to have it to work in a view.. the view can't read this certain Access form field...
How to deal with this matter...
0
 

Author Comment

by:joop123456
ID: 18782149
/* ALGORITHM=UNDEFINED */ select `db_230`.`tbll5lat`.`tblL5CAUTOID` AS `tblL5CAUTOID`,((case when (`db_230`.`tbll5lat`.`LengteAfgekort` > 0) then `db_230`.`tbll5lat`.`LengteAfgekort` else `db_230`.`tbll5lat`.`REALLengte` end) * `db_230`.`tbll5lat`.`REALStuks`) AS `StrekkendeMetersPerLengteInPak` from `db_230`.`tbll5lat`

It works, but I doubt it is ok.. why does it write the db_name all the time?
This CASE-line in Navicat shows as well a bunch of DOS-tabs/enters..

((case
(.`tbll5lat`
.`LengteAfgekort`
>0).`tbll5lat`
.`LengteAfgekort`
.`tbll5lat`
.`REALLengte`
)*.`tbll5lat`
.`REALStuks`
)
0
 

Author Comment

by:joop123456
ID: 18782189
Maybe I understand it better with an example of realworld code (instead of syntax-examples) of a MySQL view.
(I've found lot of stored procedures with CASE and IF etc. but that seems to be slightly different...)
0

Featured Post

Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question