Complex T-SQL Statement Conversion to MySQL

S

stanhook

New member
Local time
Today, 09:22
Joined
Dec 6, 2022
Messages
16
Hi,

I have gotten some help here before which has allowed me to convert a bunch of T-SQL statements to MySQL. But I am stumped on this one (this is just the IIF part):

Code: 
IIf([tlkpSUHorz]![SUHorz]='Segment',[tlkpSUHorz]![SUHorz] & ' ' & [tblPD]![SUHorzID],
(IIf([tlkpSUHorz]![SUHorz]='Half' Or [tlkpSUHorz]![SUHorz]='Quadrant' Or ([tlkpSUHorz]![SUHorz]='Wall' And [tblPD].[SUHorzID] Is Not Null),[tlkpCardinalDirection]![Dir] & ' ' & [tlkpSUHorz]![SUHorz],[tlkpSUHorz]![SUHorz]))) AS [SU Horizontal], 
IIf([tlkpSUVert].[SUVertESR2] Is Null,IIf([tblPD].[SUVertNum] Is Null,[tlkpSUvert].[SUVertESR1],[tlkpSUvert].[SUVertESR1] & ' ' & [tblPD].[SUVertNum]),IIf([tblPD].[SUVertNum] Is Null,[tlkpSUvert].[SUVertESR1],[tlkpSUvert].[SUVertESR1] & ' ' & [tblPD].[SUVertNum]) & ' ' & IIf([tblPD].[SUVertSubNum] Is Null,[tlkpSUvert].[SUVertESR2],[tlkpSUvert].[SUVertESR2] & ' ' & [tblPD].[SUVertSubNum])) AS [SU Vertical], [tlkpFAP].[FAPGen] & ': ' & [tlkpFAP].[FAPSpec] AS [Fill/Assemblage Position], [tlkpFAT].[FATGen] & ': ' & [tlkpFAT].[FATSpec] AS [Fill/Assemblage Type],

This looks like the CASE statements are nested in other CASE statements. This is what I came up with (the whole query):

Code: 
SELECT tblPD.Site, tblPD.SUNum, tblPDSCP.SCPOrder, tblPD.PD,
CASE
    WHEN tlkpSUHorz.SUHorz = 'Segment' THEN CONCAT(tlkpSUHorz.SUHorz, ' ', tblPD.SUHorzID)
    ELSE
        CASE 
            WHEN tlkpSUHorz.SUHorz = 'Half' OR tlkpSUHorz.SUHorz = 'Quadrant' OR tlkpSUHorz.SUHorz = 'Wall' AND tblPD.SUHorzID IS NOT NULL
            THEN CONCAT(tlkpCardinalDirection.Dir, ' ', tlkpSUHorz.SUHorz)
            ELSE tlkpSUHorz.SUHorz
        END 
END AS SUHorizontal,

CASE 
    WHEN tlkpSUVert.SUVertESR2 IS NULL
    THEN
        CASE
            WHEN tblPD.SUVertNum IS NULL THEN tlkpSUvert.SUVertESR1
            ELSE CONCAT(tlkpSUvert.SUVertESR1, ' ', tblPD.SUVertNum)
        END
    ELSE
        CASE
            WHEN tblPD.SUVertNum IS NULL THEN tlkpSUvert.SUVertESR1
            ELSE CONCAT(tlkpSUvert.SUVertESR1, ' ', tblPD.SUVertNum, ' ',
                CASE
                    WHEN tblPD.SUVertSubNum IS NULL THEN tlkpSUvert.SUVertESR2
                    ELSE CONCAT(tlkpSUvert.SUVertESR2,' ', tblPD.SUVertSubNum)
                END )
        END
END AS SUVertical,
CONCAT(tlkpFAP.FAPGen, ': ', tlkpFAP.FAPSpec) AS FillAssemblagePosition,
CONCAT(tlkpFAT.FATGen, ': ', tlkpFAT.FATSpec) AS FillAssemblageType,
tblPDSCP.SCPDesc

FROM tlkpFAT RIGHT JOIN (tlkpFAP RIGHT JOIN ((tlkpSUVert RIGHT JOIN (tlkpSUHorz RIGHT JOIN (tblPD LEFT JOIN tblPDSCP ON (tblPD.PD = tblPDSCP.PD) AND (tblPD.Site = tblPDSCP.Site)) ON tlkpSUHorz.SUHorzCode = tblPD.SUHorzCode) ON tlkpSUVert.SUVertCode = tblPD.SUVertCode) LEFT JOIN tlkpCardinalDirection ON tblPD.SUHorzID = tlkpCardinalDirection.DirCode) ON tlkpFAP.FAPCode = tblPD.FAPCode) ON tlkpFAT.FATCode = tblPD.FATCode
WHERE tblPD.Site = '5MT765' AND tblPD.SUNum = '1220' AND tblPD.FeNum IS NULL
ORDER BY tblPDSCP.SCPOrder, tblPD.PD

I don't thing I have converted the IIf to CASE statements correctly. I don't get any results and I should.

Hopefully @cheekybuddha can help here too.

Thanks for any help you all can provide.
 
You've started with a rats nest of Access SQL and want to convert it into a rat's nest of T-SQL. I think that's a bad idea.

You've got at least 4 IIFs in determining [SU Vertical]. A human just can't parse that much less convert it. I think you need to start from scratch and build a custom function in T-SQL and feed it all the fields it needs to determine the output.

Just from a syntax/logic perspective, you don't need to nest CASE statements. You just keep adding WHENs until you have covered all your cases.

Code: 
CASE 
  WHEN  A THEN 'ResultA'
  WHEN B OR C THEN 'ResultBorC'
  WHEN D AND F THEN 'ResultDF'
  ELSE 'ResultZ'
END



learn.microsoft.com

CASE (Transact-SQL) - SQL Server

Transact-SQL reference for the CASE expression. CASE evaluates a list of conditions to return specific results.
learn.microsoft.com
 
I had a look at this when you messaged me last night.

I reworked the query a bit, so may have mucked it up in the translation (especially in the FROM parts), but try this out and see if it helps:
SQL: 
SELECT
  pd.Site,
  pd.SUNum,
  scp.SCPOrder,
  pd.PD,
  CASE
    WHEN suh.SUHorz = 'Segment' THEN CONCAT(suh.SUHorz, ' ', pd.SUHorzID)
    WHEN suh.SUHorz IN ('Half', 'Quadrant') OR (suh.SUHorz = 'Wall' AND pd.SUHorzID IS NOT NULL)
      THEN CONCAT(cd.Dir, ' ', suh.SUHorz)
    ELSE suh.SUHorz
  END AS `SU Horizontal`,
  CONCAT_WS(
    ' ',
    suv.SUVertESR1,
    pd.SUVertNum,
    CONCAT(
      suv.SUVertESR2,
      CONCAT(' ', pd.SUVertSubNum)
    )
  ) AS `SU Vertical`,
  CONCAT(fap.FAPGen, ': ', fap.FAPSpec) AS `Fill/Assemblage Position`,
  CONCAT(fat.FATGen, ': ', fat.FATSpec) AS `Fill/Assemblage Type`,
  scp.SCPDesc
FROM tblPD pd
LEFT JOIN tblPDSCP scp
       ON (pd.PD = scp.PD)
      AND (pd.Site = scp.Site)
LEFT JOIN tlkpSUHorz suh
       ON pd.SUHorzCode = suh.SUHorzCode
LEFT JOIN tlkpSUVert suv
       ON pd.SUVertCode = suv.SUVertCode
LEFT JOIN tlkpFAP fap
       ON pd.FAPCode = fap.FAPCode
LEFT JOIN tlkpFAT fat
       ON pd.FATCode = fat.FATCode
LEFT JOIN tlkpCardinalDirection cd
       ON pd.SUHorzID = cd.DirCode
WHERE pd.Site = '5mt765'
  AND pd.SUNum = '262'
  AND pd.FeNum IS NULL
ORDER BY
  scp.SCPOrder,
  pd.PD
;
 
Last edited:
This is you original Access (T-SQL?) query re-formatted so I could try and understand what was going on:
SQL: 
SELECT
  tblPD.Site,
  tblPD.SUNum,
  tblPDSCP.SCPOrder,
  tblPD.PD,
  IIf(
    [tlkpSUHorz]![SUHorz]='Segment',
    [tlkpSUHorz]![SUHorz] & ' ' & [tblPD]![SUHorzID],
    (
      IIf(
        [tlkpSUHorz]![SUHorz]='Half' Or [tlkpSUHorz]![SUHorz]='Quadrant' Or ([tlkpSUHorz]![SUHorz]='Wall' And [tblPD].[SUHorzID] Is Not Null),
        [tlkpCardinalDirection]![Dir] & ' ' & [tlkpSUHorz]![SUHorz],
        [tlkpSUHorz]![SUHorz]
      )
    )
  ) AS [SU Horizontal],
  IIf(
    [tlkpSUVert].[SUVertESR2] Is Null,
    IIf(
      [tblPD].[SUVertNum] Is Null,
      [tlkpSUvert].[SUVertESR1],
      [tlkpSUvert].[SUVertESR1] & ' ' & [tblPD].[SUVertNum]
    ),
    IIf(
      [tblPD].[SUVertNum] Is Null,
      [tlkpSUvert].[SUVertESR1],
      [tlkpSUvert].[SUVertESR1] & ' ' & [tblPD].[SUVertNum]
    ) & ' ' &
    IIf(
      [tblPD].[SUVertSubNum] Is Null,
      [tlkpSUvert].[SUVertESR2],
      [tlkpSUvert].[SUVertESR2] & ' ' & [tblPD].[SUVertSubNum]
    )
  ) AS [SU Vertical],
  [tlkpFAP].[FAPGen] & ': ' & [tlkpFAP].[FAPSpec] AS [Fill/Assemblage Position],
  [tlkpFAT].[FATGen] & ': ' & [tlkpFAT].[FATSpec] AS [Fill/Assemblage Type],
  tblPDSCP.SCPDesc
FROM tlkpFAT
RIGHT JOIN (
  tlkpFAP
  RIGHT JOIN (
    (
      tlkpSUVert
      RIGHT JOIN (
        tlkpSUHorz
        RIGHT JOIN (
          tblPD
          LEFT JOIN tblPDSCP
                 ON (tblPD.PD = tblPDSCP.PD)
                AND (tblPD.Site = tblPDSCP.Site)
        )
              ON tlkpSUHorz.SUHorzCode = tblPD.SUHorzCode
      )
          ON tlkpSUVert.SUVertCode = tblPD.SUVertCode
    )
    LEFT JOIN tlkpCardinalDirection
           ON tblPD.SUHorzID = tlkpCardinalDirection.DirCode
  )
          ON tlkpFAP.FAPCode = tblPD.FAPCode
)
        ON tlkpFAT.FATCode = tblPD.FATCode
WHERE tblPD.Site = '5mt765'
  AND tblPD.SUNum = '262'
  AND tblPD.FeNum Is NULL
ORDER BY
  tblPDSCP.SCPOrder,
  tblPD.PD
;
 
Last edited:
Nested IIf()'s are a horrible abomination, and I wish T-SQL hadn't introduced support for them, as a Case Statement is so much cleaner and easier to write, and a million times easier to understand.

As others have alluded to, I would have built a function to do the IIf's in access, and use case statements in SQL.
 
Thank you all for your help and thought's!

@cheekybuddha I will try that in a bit and see if that works.

Just to note, I am converting the old query into MySQL. This is the old query copied directly from the code:

Code: 
SELECT tblPD.Site, tblPD.SUNum, tblPDSCP.SCPOrder, tblPD.PD, IIf([tlkpSUHorz]![SUHorz]='Segment',[tlkpSUHorz]![SUHorz] & ' ' & [tblPD]![SUHorzID],(IIf([tlkpSUHorz]![SUHorz]='Half' Or [tlkpSUHorz]![SUHorz]='Quadrant' Or ([tlkpSUHorz]![SUHorz]='Wall' And [tblPD].[SUHorzID] Is Not Null),[tlkpCardinalDirection]![Dir] & ' ' & [tlkpSUHorz]![SUHorz],[tlkpSUHorz]![SUHorz]))) AS [SU Horizontal], IIf([tlkpSUVert].[SUVertESR2] Is Null,IIf([tblPD].[SUVertNum] Is Null,[tlkpSUvert].[SUVertESR1],[tlkpSUvert].[SUVertESR1] & ' ' & [tblPD].[SUVertNum]),IIf([tblPD].[SUVertNum] Is Null,[tlkpSUvert].[SUVertESR1],[tlkpSUvert].[SUVertESR1] & ' ' & [tblPD].[SUVertNum]) & ' ' & IIf([tblPD].[SUVertSubNum] Is Null,[tlkpSUvert].[SUVertESR2],[tlkpSUvert].[SUVertESR2] & ' ' & [tblPD].[SUVertSubNum])) AS [SU Vertical], [tlkpFAP].[FAPGen] & ': ' & [tlkpFAP].[FAPSpec] AS [Fill/Assemblage Position], [tlkpFAT].[FATGen] & ': ' & [tlkpFAT].[FATSpec] AS [Fill/Assemblage Type], tblPDSCP.SCPDesc

FROM tlkpFAT RIGHT JOIN (tlkpFAP RIGHT JOIN ((tlkpSUVert RIGHT JOIN (tlkpSUHorz RIGHT JOIN (tblPD LEFT JOIN tblPDSCP ON (tblPD.PD = tblPDSCP.PD) AND (tblPD.Site = tblPDSCP.Site)) ON tlkpSUHorz.SUHorzCode = tblPD.SUHorzCode) ON tlkpSUVert.SUVertCode = tblPD.SUVertCode) LEFT JOIN tlkpCardinalDirection ON tblPD.SUHorzID = tlkpCardinalDirection.DirCode) ON tlkpFAP.FAPCode = tblPD.FAPCode) ON tlkpFAT.FATCode = tblPD.FATCode

WHERE (((tblPD.Site)='" & request("Site") & "') AND ((tblPD.SUNum)='" & request("SUNum") & "') AND ((tblPD.FeNum) Is Null))

ORDER BY tblPDSCP.SCPOrder, tblPD.PD

Thanks again!
 
@cheekybuddha

Unfortunately your code returned the same empty results. I know the query returns results because I get them on the old website. So the data is returned for the variables I added. I don't get any errors, just no results using the converted query. I just feel like there must be something in the converted IIf statements.

Thanks
 
Last edited:
OK, so you have to start breaking it down.

First run this simple query:
SQL: 
SELECT
  COUNT(*) AS RecCount1
FROM tblPD pd
LEFT JOIN tblPDSCP scp
       ON (pd.PD = scp.PD)
      AND (pd.Site = scp.Site)
LEFT JOIN tlkpSUHorz suh
       ON pd.SUHorzCode = suh.SUHorzCode
LEFT JOIN tlkpSUVert suv
       ON pd.SUVertCode = suv.SUVertCode
LEFT JOIN tlkpFAP fap
       ON pd.FAPCode = fap.FAPCode
LEFT JOIN tlkpFAT fat
       ON pd.FATCode = fat.FATCode
LEFT JOIN tlkpCardinalDirection cd
       ON pd.SUHorzID = cd.DirCode
WHERE pd.Site = '5mt765'
  AND pd.SUNum = '262'
  AND pd.FeNum IS NULL
ORDER BY
  scp.SCPOrder,
  pd.PD
;

Then, also try with the original nested FROM:
SQL: 
SELECT
  COUNT(*) AS RecCount2
FROM tlkpFAT
RIGHT JOIN (
  tlkpFAP
  RIGHT JOIN (
    (
      tlkpSUVert
      RIGHT JOIN (
        tlkpSUHorz
        RIGHT JOIN (
          tblPD
          LEFT JOIN tblPDSCP
                 ON (tblPD.PD = tblPDSCP.PD)
                AND (tblPD.Site = tblPDSCP.Site)
        )
                ON tlkpSUHorz.SUHorzCode = tblPD.SUHorzCode
      )
              ON tlkpSUVert.SUVertCode = tblPD.SUVertCode
    )
    LEFT JOIN tlkpCardinalDirection
           ON tblPD.SUHorzID = tlkpCardinalDirection.DirCode
  )
          ON tlkpFAP.FAPCode = tblPD.FAPCode
)
        ON tlkpFAT.FATCode = tblPD.FATCode
WHERE tblPD.Site = '5mt765'
  AND tblPD.SUNum = '262'
  AND tblPD.FeNum IS NULL
ORDER BY
  tblPDSCP.SCPOrder,
  tblPD.PD
;

What do they return?
 
@cheekybuddha

So I ran each of those and the count was 0. No errors at all.

In the WHERE statement if I leave the Site and change AND to OR and leave out the SUNum I get back 1700+ records. If I remove the site and leave in the SUNum I do not get any records back. I also tried this in the queries you helped with and got the same results. In the returned records where FeNum is NULL the SUNum is VOI and not a number. Those are the records that should be returned. I will have to check the tables and maybe I am missing something there.

So I am guessing I have some other kind of issue. No idea why at the moment. I will dig into it and see what I can find. If you have any other query thought's, feel free to share. BTW, thanks for the debug, I was so wrapped up I didn't even think about it.

Thanks for your continued help!
 
I think I have it fixed. For some reason the table does not have NULL for FeNum. It is empty. Not sure why or what happened. I will test more but this may be fixed.
 
@cheekybuddha
So the command shows FeNum should be NULL

Code: 
CREATE TABLE `tblPD` (
  `id` varchar(5) DEFAULT NULL,
  `site` varchar(15) DEFAULT NULL,
  `pd` varchar(6) DEFAULT NULL,
  `inputstamp` varchar(10) DEFAULT NULL,
  `inputinitials` varchar(13) DEFAULT NULL,
  `dateopened` varchar(255) DEFAULT NULL,
  `initialsopened` varchar(14) DEFAULT NULL,
  `dateclosed` varchar(255) DEFAULT NULL,
  `initialsclosed` varchar(14) DEFAULT NULL,
  `sunum` varchar(6) DEFAULT NULL,
  `suhorzcode` varchar(10) DEFAULT NULL,
  `suhorzid` varchar(10) DEFAULT NULL,
  `sunorth` varchar(7) DEFAULT NULL,
  `sueast` varchar(10) DEFAULT NULL,
  `suvertcode` varchar(10) DEFAULT NULL,
  `suvertnum` varchar(9) DEFAULT NULL,
  `suvertsubnum` varchar(12) DEFAULT NULL,
  `suelev` varchar(6) DEFAULT NULL,
  `fenum` varchar(5) DEFAULT NULL,
  `fetypcode` varchar(9) DEFAULT NULL,
  `fehorzcode` varchar(10) DEFAULT NULL,
  `fehorzid` varchar(8) DEFAULT NULL,
  `fenorth` varchar(7) DEFAULT NULL,
  `feeast` varchar(10) DEFAULT NULL,
  `fevertcode` varchar(10) DEFAULT NULL,
  `fevertnum` varchar(9) DEFAULT NULL,
  `fevertsubnum` varchar(12) DEFAULT NULL,
  `feelev` varchar(6) DEFAULT NULL,
  `excmethcode` varchar(11) DEFAULT NULL,
  `collmethcode` varchar(12) DEFAULT NULL,
  `probsquare` varchar(10) DEFAULT NULL,
  `sampstrat` varchar(9) DEFAULT NULL,
  `sampunit` varchar(8) DEFAULT NULL,
  `fapcode` varchar(7) DEFAULT NULL,
  `fatcode` varchar(7) DEFAULT NULL,
  `pddesc` varchar(1614) DEFAULT NULL,
  `auditversion` varchar(12) DEFAULT NULL,
  `void` varchar(5) DEFAULT NULL,
  `eunum` varchar(5) DEFAULT NULL,
  `labels` varchar(6) DEFAULT NULL,
  UNIQUE KEY `tblpd_id_idx` (`id`) USING BTREE,
  KEY `tblpd_fapcode_idx` (`fapcode`) USING BTREE,
  KEY `tblpd_suvertcode_idx` (`suvertcode`) USING BTREE,
  KEY `tblpd_pd_idx` (`pd`) USING BTREE,
  KEY `tblpd_site_idx` (`site`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Thought's?
 
stanhook said:
So the command shows FeNum should be NULL
Click to expand...
It should be NULL if no value was supplied in an Insert statement to that table. However, a default value of NULL does not prevent any other value being explicitly inserted into that column.
 
@sonic8 is right - your field only specifies the DEFAULT value will be NULL.

How do users interface with the db? If you use UPDATE statements you need to ensure ZLS values are passed as NULL if that is what you want.

Or you can add CHECK CONSTRAINTS to your field definitions, eg:
SQL: 
CREATE TABLE `tblPD` (
  `id` varchar(5) DEFAULT NULL,
  -- ...
  `fenum` varchar(5) DEFAULT NULL CONSTRAINT tblPD_fenum_no_zls CHECK (fenum <> ''),
  -- ...
NB. you must be using MySQL 8.0.16 or later to use CHECK CONSTRAINTs

Other unrelated issues:

1. Your table does not have a Primary Key!!!!! Even though you have a unique index on field `id`, this is not the same thing. MySQL will actually create a hidden PK for the table, though you can not access it.

2. You do not specify any foreign key constraints for your related tables. Also, you join your lookup tables on the actual values (eg `fapcode` and `fatcode`). Are these tables just a single field with the value as Primary Key?

3. Field `dateclosed` sounds like a date, but you are using a VARCHAR datatype. If it is a date, why not use a proper DATETIME or TIMESTAMP datatype? Otherwise you will more than likely hit issues further down the road.

Also, is your MySQL server running on Windows (as opposed to Linux)? If not, be careful with what case you use for your fieldnames in your queries.
 
@cheekybuddha

This is an existing table (all of them are) no records are being added or changed. I haven't changed anything with the structure. All I did was convert the tables from Access over to MySQL by exporting them as a CSV file. I have done about 100 of these and this is the first issue I ran into converting the query and having it work. All the other conversion works as they were when converted.

We are running on Linux and I am aware of the names.

By using tblPD.FeNum = '' Everything seems to work as expected in all our test so far.

Thanks for all the help!!
 
You must log in or register to reply here.

Similar threads

S
Solved T-SQL Conversion to MySQL
Replies
3
Views
388
stanhook
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom