Solved T-SQL Conversion to MySQL (1 Viewer)

[stanhook]

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:

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:

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

 

[Gasman]

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

 

[cheekybuddha]

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,
  -- .....

 

[stanhook]

@Gasman @cheekybuddha Awesome help and thanks for explaining it. That was exactly what I needed and the code shared got it working.

You all rock!