T-SQL迁移至MariaDb后SQL查询报错：参数索引0无效

报错原因解析 #

• 标识符解析冲突：原T-SQL中用[]包裹的别名（如[QTY ORDERED]）在MariaDb的JDBC驱动环境下，会被误识别为参数占位符，导致驱动试图读取不存在的参数，触发"Parameter index 0 is invalid when only 0 parameters are defined"错误。
• 函数语法差异：MS SQL的STR()函数允许仅传入数值参数，而MariaDb的STR()要求必须指定长度参数（STR(数值, 长度)），原代码未指定会引发语法错误。

修复后的完整SQL代码 #

/*  Show the CURRENT LOCATIONS of  all the jobs
    Sorted by DueDates first, then by JobNo
*/
Select OrderDet.DueDate, Orders.CustDesc, Orders.PONum,
        CASE
            WHEN OrderDet.QtyOrdered > 0 THEN STR(OrderDet.QtyOrdered, 10)
            WHEN OrderDet.QtyToStock > 0 THEN 'For Stock (' + STR(OrderDet.QtyToStock, 10) +  ')'
        END AS `QTY ORDERED`,
        OrderRouting.JobNo, OrderRouting.PartNo,
        CASE
            WHEN OrderRouting.VendCode IS NOT NULL THEN '@ ' + OrderRouting.VendCode
            WHEN StepCount = 1 AND OrderRouting.Status = 'Finished' THEN 'SHIPPING'
            WHEN Online.JobNo IS NOT NULL AND Online.StepNo IS NOT NULL THEN WorkCntr.ShortName /*Display WrkCntr current employee is clocked into*/
            ELSE OrderRouting.WorkCntr
        END AS `Current Location`, OrderRouting.OperCode,
        CASE
            WHEN Online.JobNo IS NOT NULL AND Online.StepNo IS NOT NULL THEN 'Clocked In By - ' + Online.EmplCode
            Else ''
        END AS `Current Status`,
        CASE
            WHEN OrderDet.JobOnHold = 'Y' THEN 'On Hold'
            WHEN TimeTicketDetails.Comments IS NOT NULL THEN TimeTicketDetails.Comments
            ELSE ''
        END AS `On Hold? / Comments`, OrderRouting.StepNo
From OrderRouting
/* Create an intermediate Table for 'StepCount' to be used in main query */
JOIN (  Select ROW_NUMBER() OVER (PARTITION BY OrderRouting.JobNo ORDER BY OrderRouting.StepNo DESC) AS `StepCount`, OrderRouting.JobNo, OrderRouting.StepNo
        From OrderRouting
        Where OrderRouting.JobNo IS NOT NULL AND OrderRouting.JobNo != '' ) Step_Count
        ON Step_Count.JobNo = OrderRouting.JobNo AND Step_Count.StepNo = OrderRouting.StepNo
INNER JOIN OrderDet on OrderDet.JobNo = OrderRouting.JobNo
INNER JOIN Orders ON Orders.OrderNo = OrderRouting.OrderNo
LEFT OUTER JOIN Online ON Online.JobNo = OrderRouting.JobNo
LEFT OUTER JOIN WorkCntr ON WorkCntr.WorkCntr = Online.WorkCntr
LEFT OUTER JOIN  (
                    Select TimeTicketDet.* From TimeTicketDet
                    JOIN  (
                            Select JobNo, StepNo, MAX(TimeTicketDet_ID) as `LastID` From TimeTicketDet
                            GROUP BY JobNo, StepNo
                        ) tt1 ON tt1.JobNo = TimeTicketDet.JobNo AND tt1.LastID = TimeTicketDet.TimeTicketDet_ID
                ) TimeTicketDetails ON TimeTicketDetails.JobNo = OrderRouting.JobNo AND TimeTicketDetails.StepNo = OrderRouting.StepNo
WHERE   OrderDet.Status = 'Open' AND
        (OrderRouting.Status = 'Current'  OR (OrderRouting.Status = 'Finished' AND Step_Count.StepCount = 1))
Order By    DueDate,
            OrderRouting.OrderNo,
            OrderRouting.JobNo,
            OrderRouting.StepNo

关键修改点说明 #

• 所有[]包裹的别名/标识符替换为MariaDb支持的`反引号，避免驱动误解析为参数。
• 给STR()函数补充长度参数（示例用10，可根据实际数值范围调整），匹配MariaDb的函数语法要求。

内容的提问来源于stack exchange，提问作者changedsoul