排序出错

查查

将justup.xml中<db-paging>true</db-paging>由false-->true后，非本表的排序就出错，请试一下，应该是生成SQL语句时（）位置不对.

jishuang

平台默认的配置本来就是false

把sql语句输出看看具体执行的sql语句是什么？

查查

应该平台（）写错了地方

LJF

jishuang 发表于 2021-8-2 19:22
平台默认的配置本来就是false

把sql语句输出看看具体执行的sql语句是什么？ ...

Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150039; 提示: 执行sql: SELECT * FROM (SELECT   TOP 100 PERCENT  ROW_NUMBER() OVER( ORDER BY HPO_NO ASC) AS JUSTEP_RN__,   POH.PAY_CODE AS POHPAY_CODE, POD.ITEM_DESC AS ITEM_DESC, POD.UNIT_PRICE AS UNIT_PRICE, POD.PPB1 AS PPB1, POD.PACKQTY_F AS PACKQTY_F, POD.MAX_QTY_BAL AS MAX_QTY_BAL, POD.FTD_REMARK AS FTD_REMARK, ITEMMSTR.LEAD_TIME2 AS LEAD_TIME2, POD.PPB3 AS PPB3, POD.BOL_DESC AS BOL_DESC, POD.PPB2 AS PPB2, POD.QTY_ADD AS QTY_ADD, POD.JOB_NO AS JOB_NO, POD.DCURR_CODE AS DCURR_CODE, POD.LAST_REC_DATE AS LAST_REC_DATE, POD.fID AS POD, POD.UNITRATE AS UNITRATE, POD.BOL_CODE1 AS BOL_CODE1, POH.ORD_ID AS ORD_ID, POD.P_TYPE AS P_TYPE, POD.VERSION AS version, POD.STATUS_CODE AS Status_Code, ITEMMSTR.MZLIFE AS ITEMMZLIFE, POD.FTD AS FTD, POD.FRMTABLE AS FRMTABLE, POD.PARA18 AS PARA18, POD.QTYTYPEOV AS QTYTYPEOV, POD.QTY_F AS QTY_F, POD.PPC AS PPC, POD.QTFID AS QTFID, POD.PPB AS PPB, POD.PPF AS PPF, POD.QTY_PR AS QTY_PR, POD.FREE AS FREE, PARA.S_DESC AS STATUS_DESC, ITEMMSTR.SPEC01 AS ITEMSPEC01, ITEMMSTR.SPEC02 AS ITEMSPEC02, ITEMMSTR.INT_CODE AS INT_CODE, ITEMMSTR.SPEC03 AS ITEMSPEC03, ITEMMSTR.SPEC04 AS ITEMSPEC04, ITEMMSTR.SPEC05 AS ITEMSPEC05, ( ITEMMSTR.MZLIFE * POD.QTY_BAL ) AS MZLIFE, ITEMMSTR.SPEC06 AS ITEMSPEC06, POD.SEQ_NO AS SEQ_NO, ITEMMSTR.SPEC07 AS ITEMSPEC07, POD.BOL_DESC1 AS BOL_DESC1, ITEMMSTR.SPEC08 AS ITEMSPEC08, POD.W AS W, POH.SYSDATE AS HSysDate, POD.REMARK AS REMARK, POD.W_F AS W_F, POD.OURMODEL AS OURMODEL, POD.REC_LAST_DATE AS REC_LAST_DATE, POD.HID AS HID, POD.SODFID AS SODFID, ITEMTYPE.MFLAG AS MFLAG, POD.PACKUNIT AS PACKUNIT, POD.PACKQTY AS PACKQTY, POD.CUST_PO AS CUST_PO, POD.SYSDATE AS SysDate, POD.SPEC1 AS Spec1, POD.SPEC2 AS Spec2, POH.TAX_RATE AS Tax_Rate, POH.VDR_NAMEC AS Vdr_Namec, PARAPKUNIT.S_DESC AS PKUNIT_DESC, VENDOR.QTY1 AS QTY1, POH.VDR_CODE AS Vdr_Code, PARAPAYCODE.S_DESC AS POHPAY_DESC, POD.ITEM_NO AS ITEM_NO, POH.APPR_FLAG AS APPR_FLAG, POD.SYSUSER AS SysUser, POD.QTY_BAL AS QTY_BAL, POD.WK_DESC AS WK_DESC, POD.PCFLAG AS PCFLAG, POD.YOURCOLOR AS YOURCOLOR, POD.PO_NO AS PO_NO, POD.PR_NO AS PR_NO, ITEMMSTR.MZ_FLAG AS MZ_FLAG, POD.SYSREMARK AS SYSREMARK, POD.QTY AS QTY, POD.DAY_DIFF AS DAY_DIFF, POD.BOLD AS BOLD, POD.FTD1 AS FTD1, ? AS PODTABLE, POD.FTD2 AS FTD2, POD.UNIT AS UNIT, POD.BOL_CODE AS BOL_CODE, POH.B_CODE AS POHB_CODE, POD.YOURMODEL AS YOURMODEL, POD.B_NO AS B_NO, PARAUNIT.S_DESC AS UNIT_DESC, POD.TCURR_CODE AS TCURR_CODE, PARACURR.S_DESC AS CURR_DESC, POD.PACKQTY_BAL AS PACKQTY_BAL, POH.PO_NO AS HPO_NO, POD.ACC_UNIT_PRICE AS ACC_UNIT_PRICE, POD.QTY_TEMP AS QTY_TEMP, POD.FFID AS Ffid, ITEMMSTR.POS_DESC AS POS_DESC, POD.P1 AS P1, ? AS OP, POD.P2 AS P2, POD.P3 AS P3, POD.P4 AS P4, POD.PUR_FLAG AS Pur_Flag, POD.P5 AS P5, POH.TAXRATE_FLAG AS TaxRate_Flag, POH.CURR_CODE AS Curr_Code, POD.CTD AS CTD, POD.BOL_TYPE AS BOL_TYPE, POD.CUST_DESC AS CUST_DESC FROM POD POD LEFT JOIN POH POH ON (POD.HID = POH.fID)  AND (POH.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  LEFT JOIN ITEMMSTR ITEMMSTR ON (POD.ITEM_NO = ITEMMSTR.ITEM_NO)  AND (ITEMMSTR.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  LEFT JOIN ITEMTYPE ITEMTYPE ON (ITEMMSTR.ITEM_TYPE = ITEMTYPE.ITEM_TYPE)  AND (ITEMTYPE.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  LEFT JOIN PARA PARA ON ( (POD.STATUS_CODE = PARA.S) AND (PARA.P = ?) )  AND (PARA.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  LEFT JOIN PARA PARAUNIT ON ( (POD.UNIT = PARAUNIT.S) AND (PARAUNIT.P = ?) )  AND (PARAUNIT.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  LEFT JOIN PARA PARAPKUNIT ON ( (POD.PACKUNIT = PARAPKUNIT.S) AND (PARAPKUNIT.P = ?) )  AND (PARAPKUNIT.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  LEFT JOIN VENDOR VENDOR ON (POH.VDR_CODE = VENDOR.VDR_CODE)  AND (VENDOR.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  LEFT JOIN PARA PARACURR ON ( (POD.TCURR_CODE = PARACURR.S) AND (PARACURR.P = ?) )  AND (PARACURR.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  LEFT JOIN PARA PARAPAYCODE ON ( (POH.PAY_CODE = PARAPAYCODE.S) AND (PARAPAYCODE.P = ?) )  AND (PARAPAYCODE.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')   WHERE ( (( ISNULL(POD.QTY, ?) - ISNULL(POD.QTY_F, ?) ) > ?) AND (ISNULL(POD.STATUS_CODE, ?) = ?) AND (ISNULL(POH.APPR_FLAG, ?) = ?) ) AND (POD.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  ORDER BY HPO_NO ASC) AS JUSTEP_TABLE__ WHERE JUSTEP_RN__ >= 1 AND JUSTEP_RN__<21, binds: [POD, 0.00000, 381, 300, 300, 200, 201, 0, 0, 0, 0, 0, , V]出错
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.ksql.KSQLUtils.execSqlSelect(Unknown Source)
        ... 45 more
Caused by: java.sql.SQLException: 列名 'HPO_NO' 无效。

jishuang

数据库中没有HPO_NO字段

LJF

jishuang 发表于 2021-8-5 09:29
数据库中没有HPO_NO字段

是通过action表连接 增加了这个字段的。  
当 false 是可以了 ，改成true以后 就有这个问题了

查查

技术，你好，
这是另外一个表中HPO_NO字段，ACTION 中是有的，但是当justup.xml中改为TRUE时，发现你们生成SQL时，（）位置不对，就找不到、
其它表的FIELDS,请你们看一下SQL.以下 ROW_NUMBER() OVER( ORDER BY HPO_NO ASC  是平台自动生成的，但是HPO_NO,从界面排序属性中取的，应该  是POH.PO_NO,
但是排序属性中又不能填POH.PO_NO,这样讲明白么？


SELECT * FROM
(SELECT   TOP 100 PERCENT  ROW_NUMBER() OVER( ORDER BY HPO_NO ASC) AS JUSTEP_RN__,   POH.PAY_CODE AS POHPAY_CODE
   FROM POD POD LEFT JOIN POH POH ON (POD.HID = POH.fID)  AND (POH.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  

   LEFT JOIN ITEMMSTR ITEMMSTR ON (POD.ITEM_NO = ITEMMSTR.ITEM_NO)  AND (ITEMMSTR.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  
   LEFT JOIN ITEMTYPE ITEMTYPE ON (ITEMMSTR.ITEM_TYPE = ITEMTYPE.ITEM_TYPE)  AND (ITEMTYPE.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  
   LEFT JOIN PARA PARA ON ( (POD.STATUS_CODE = PARA.S) AND (PARA.P = ?) )  AND (PARA.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  
   LEFT JOIN PARA PARAUNIT ON ( (POD.UNIT = PARAUNIT.S) AND (PARAUNIT.P = ?) )  AND (PARAUNIT.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  
   LEFT JOIN PARA PARAPKUNIT ON ( (POD.PACKUNIT = PARAPKUNIT.S) AND (PARAPKUNIT.P = ?) )  AND (PARAPKUNIT.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  
   LEFT JOIN VENDOR VENDOR ON (POH.VDR_CODE = VENDOR.VDR_CODE)  AND (VENDOR.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  
   LEFT JOIN PARA PARACURR ON ( (POD.TCURR_CODE = PARACURR.S) AND (PARACURR.P = ?) )  AND (PARACURR.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  
   LEFT JOIN PARA PARAPAYCODE ON ( (POH.PAY_CODE = PARAPAYCODE.S) AND (PARAPAYCODE.P = ?) )  AND (PARAPAYCODE.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')   
   WHERE ( (( ISNULL(POD.QTY, ?) - ISNULL(POD.QTY_F, ?) ) > ?) AND (ISNULL(POD.STATUS_CODE, ?) = ?) AND (ISNULL(POH.APPR_FLAG, ?) = ?) )
   AND (POD.client_id = 'FF2A89265BA94E5DA1C75A01D953FB79')  ORDER BY HPO_NO ASC) AS JUSTEP_TABLE__ WHERE JUSTEP_RN__ >= 1 AND JUSTEP_RN__<21

jishuang

只要设置关联查询就会报错吗?

查查

是的，我再试一下