Solved T-SQL Conversion to MySQL (1 Viewer)

stanhook

New member
Joined
Dec 6, 2022
Messages
16
Hi,

I am converting what I believe is T-SQL into MySQL. I have done pretty good so far but I ran into this and needed some help explaining this part of the query:

Code:
SELECT tblStratProfile.ProfileNum, tlkpProfileView.ProfViewDesc, IIf((IsNumeric([tblStrat]![StratNum])),[tlkpSUTyp]![SUTyp] & ' ' & [tblStrat]![SUNum] & ' (' & [tlkpSUDesc]![SUDesc] & '), Surface ' & [tblStrat]![SUSurfNum],[tblStrat]![StratNum]) AS Stratum, tblStrat.SUNum, .....

I understand the SELECT statement. The part I am having trouble with is:

Code:
IIf((IsNumeric([tblStrat]![StratNum])),[tlkpSUTyp]![SUTyp] & ' ' & [tblStrat]![SUNum] & ' (' & [tlkpSUDesc]![SUDesc] & '), Surface ' & [tblStrat]![SUSurfNum],[tblStrat]![StratNum]) AS Stratum

I think this is the equivalent to a CASE statement and CONCATE and it is saying (CASE) if the value in the table tblstrat column StratNum is a number then CONCAT these values as Startum. But I get a little lost with the multiple '&'.

I am not totally sure and what I have tried isn't working. Can someone explain that part and maybe know the MySQL equivalent to it?

Thanks,
Stan
 
I would put each part into a query and see what is produced.
The & are just concatenating what comes before with what comes after that symbol.
So if stratum is numeric you use the type, then a space then sunum and then sudesc, else 'surface' then susurfnum the stratum
 
SQL:
SELECT
  tblStratProfile.ProfileNum,
  tlkpProfileView.ProfViewDesc,
  CASE
    WHEN tblStrat.StratNum REGEXP '^[+\-]?[0-9]+\\.?[0-9]*$'
      THEN CONCAT(tlkpSUTyp.SUTyp, ' ', tblStrat.SUNum, ' (', lkpSUDesc.SUDesc, '), Surface ', tblStrat.SUSurfNum)
    ELSE tblStrat.StratNum
  END AS Stratum,
  tblStrat.SUNum,
  -- .....
 

Users who are viewing this thread

Top Bottom