Editor PHP: Incorrect syntax error when using SQL function

binexline

I'm getting below error when I try to use COALESCE(NULLIF(TB_INTG_BL.BL_STS_CD, ''), 'PO Created') function..

{fieldErrors: [],…}
cancelled: []
data: []
debug: [{,…}]
0: {,…}
bindings: [{name: ":where_0", value: "T28011645", type: null}, {name: ":where_1", value: 20201113, type: null},…]
0: {name: ":where_0", value: "T28011645", type: null}
1: {name: ":where_1", value: 20201113, type: null}
2: {name: ":where_2", value: 20201225, type: null}
query: "SELECT  [TB_PO].[PO_SYS_NO] as 'TB_PO.PO_SYS_NO', UPPER(TB_PO.VNDR_TRDP_NM) as 'UPPER(TB_PO.VNDR_TRDP_NM)', UPPER(TB_PO.ORG_LOC_NM) as 'UPPER(TB_PO.ORG_LOC_NM)', [TB_PO].[SHPWIN_FR_DT] as 'TB_PO.SHPWIN_FR_DT', [TB_PO].[SHPWIN_TO_DT] as 'TB_PO.SHPWIN_TO_DT', [TB_PO].[CUST_PO_NO] as 'TB_PO.CUST_PO_NO', [TB_PO].[DEPT_CD] as 'TB_PO.DEPT_CD', [TB_INTG_BL].[ETD_POR_TM] as 'TB_INTG_BL.ETD_POR_TM', COALESCE(NULLIF(TB_INTG_BL.BL_STS_CD, ''), 'PO Created') as 'COALESCE(NULLIF(TB_INTG_BL.BL_STS_CD, ''), 'PO Created')', DATEDIFF(DAY, TB_PO.SHPWIN_TO_DT, TB_INTG_BL.ETD_POR_TM) as 'DATEDIFF(DAY, TB_PO.SHPWIN_TO_DT, TB_INTG_BL.ETD_POR_TM)', [TB_INTG_BL].[MODI_TMS] as 'TB_INTG_BL.MODI_TMS', [TB_PO].[PO_RMK] as 'TB_PO.PO_RMK' FROM  [TB_PO] LEFT JOIN [TB_INTG_BL] ON [TB_PO].[CUST_PO_NO] = [TB_INTG_BL].[PO_NO] WHERE [TB_PO].[CUST_TRDP_CD] = :where_0 AND  [TB_PO].[SHPWIN_FR_DT] >= :where_1 AND  [TB_PO].[SHPWIN_TO_DT] <= :where_2 "
error: "SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'PO'."
fieldErrors: []
ipOpts: []

Below is my code:

Editor::inst($db, 'TB_PO', 'PO_SYS_NO')
    ->field(
        Field::inst('UPPER(TB_PO.VNDR_TRDP_NM)', 'VNDR_TRDP_NM'), // Vendor
        Field::inst('UPPER(TB_PO.ORG_LOC_NM)', 'ORG_LOC_NM'), // Origin
        Field::inst('TB_PO.SHPWIN_FR_DT') // Ship Win From
            ->getFormatter(Format::dateSqlToFormat('m-d-Y'))
            ->setFormatter(Format::dateFormatToSql('Ymd')),
        Field::inst('TB_PO.SHPWIN_TO_DT') // Ship Win To
            ->getFormatter(Format::dateSqlToFormat('m-d-Y'))
            ->setFormatter(Format::dateFormatToSql('Ymd')),
        Field::inst('TB_PO.CUST_PO_NO'), // PO#
        Field::inst('TB_PO.DEPT_CD'), // Dept#
        Field::inst('TB_INTG_BL.ETD_POR_TM') // Pick up Date
            ->getFormatter(Format::dateSqlToFormat('m-d-Y'))
            ->setFormatter(Format::dateFormatToSql('Ymd')),
        Field::inst("COALESCE(NULLIF(TB_INTG_BL.BL_STS_CD, ''), 'PO Created')", 'BL_STS_CD') // Status
            ->set(false),
        // Field::inst('TB_INTG_BL.BL_STS_CD'),
        Field::inst('DATEDIFF(DAY, TB_PO.SHPWIN_TO_DT, TB_INTG_BL.ETD_POR_TM)', 'Delay') // Delay
            ->set(false),
        Field::inst('TB_INTG_BL.MODI_TMS') // Last Updated
            ->getFormatter(Format::dateSqlToFormat('m-d-Y H:i'))
            ->setFormatter(Format::dateFormatToSql('Y-m-d H:i:s.u')),
        Field::inst('TB_PO.PO_RMK') // Remark
    )
    ->leftJoin('TB_INTG_BL', 'TB_PO.CUST_PO_NO', '=', 'TB_INTG_BL.PO_NO')
    ->where('TB_PO.CUST_TRDP_CD', 'T28011645')
    ->where('TB_PO.SHPWIN_FR_DT', $threeWeeksBefore->format('Ymd'), '>=')
    ->where('TB_PO.SHPWIN_TO_DT', $threeWeeksAfter->format('Ymd'), '<=')
    ->debug(true)
    ->process($_POST)
    ->json();

Is there any way for the function to work? I think it's because of the apostrophe...
Please help..:'(

allan

Hi,

It is because of the aliasing that the Editor libraries are performing. I think we are going to have to change that because you aren't the first to run into this.

Rather than using SQL to do the caolesce you could use a get formatter to check the value - if null then return your PO Created string, otherwise return the value).

Regards,
Allan