|
| Why do the individual characters in ldtLowDate and ldtHighDate get put
into this query and how do I correct this? [C6.1, 9029, Clarion
templates]
PROBLEM: Error from MS SQL log running
08B0H(2) 09:56:35.406 Error Occurred: 37000 [Microsoft][ODBC SQL Server
Driver][SQL Server]Line 1: Incorrect syntax near ' AND A.DTOURSHIPDATE
<=3D ' + {fn CHAR(29)} + {fn CHAR(12)} + '.
>From Clarion Process template
Process:View{Prop:Filter} =3D |
'ShipDtl:dtOurShipDate >=3D ldtLowDate AND ShipDtl:dtOurShipDate <<=3D
ldtHi' & |
'ghDate AND (ShipDtl:sTradingPartner =3D ASNHdr:sTradingPartner AND
ShipD' & |
'tl:sShipTo =3D ASNHdr:sShipTo)'
Relevant part of SQL Command: (the entire command listed below)
A=2ESSUPPLIERCODE FROM dbo.ShipDtl A WHERE ( A.DTOURSHIPDATE >=3D '' + {fn
CHAR(29)} + {fn CHAR(12)} + '=D4' + {fn CHAR(7)} + {fn CHAR(0)} + {fn
CHAR(0)} + '6' + {fn CHAR(14)}' AND A.DTOURSHIPDATE <=3D '' + {fn
CHAR(29)} + {fn CHAR(12)} + '=D4' + {fn CHAR(7)} + {fn CHAR(0)} + {fn
CHAR(0)} + {fn CHAR(6)} + {fn CHAR(15)}' AND A.STRADINGPARTNER =3D
'HONDA ' AND A.SSHIPTO =3D 'AP1 '
DCT: for table ASNHDR (ShipDtl:dtOurShipDate is similar)
dtShipDate STRING(8)
dtShipDate_GROUP GROUP,OVER(dtShipDate)
dtShipDate_DATE DATE
tmShipTime_TIME TIME
END
Local Variables:
liLowTime LONG !
liHighTime LONG !
ldtLowDate STRING(8) !
ldtLowDate_GROUP GROUP,PRE(),OVER(ldtLowDate) !
ldtLowDate_DATE DATE !
ldtLowDate_Time TIME !
END !
ldtHighDate STRING(8) !
ldtHighDate_GROUP GROUP,PRE(),OVER(ldtHighDate) !
ldtHighDate_DATE DATE !
ldtHighDate_TIME TIME !
END !
Code:
Purpose: I need to get all records within 5 minutes of the the
requested ship time:
liLowTime =3D ASNHdr:tmShipTime_TIME - 36000
liHighTime =3D ASNHdr:tmShipTime_TIME + 36000
ldtLowDate_DATE =3D ASNHdr:dtShipDate_DATE
ldtLowDate_TIME =3D liLowTime
ldtHighDate_DATE =3D ASNHdr:dtShipDate_DATE
ldtHighDate_TIME =3D liHighTime
AddSysLog('1Low:'& liLowTIME & ' High:' & liHighTIME |
&' '& FORMAT(liLowTIME,@t4) & ' High:' &
Format(liHighTIME,@t4) )
AddSysLog('2ASN:'& ASNHdr:dtShipDate_DATE & ' '&
ASNHdr:tmShipTime_TIME)
AddSysLog('2ASN:'& FORMAT(ASNHdr:dtShipDate_DATE,@d12b) & ' '&
FORMAT(ASNHdr:tmShipTime_TIME,@T4b))
AddSysLog('3Low:'& ldtLowDate_TIME & ' High:' & ldtHighDate_TIME & '
ASN:'& ASNHdr:tmShipTime_TIME)
AddSysLog('3Low:'& FORMAT(ldtLowDate_TIME,@t4b) & ' High:' &
FORMAT(ldtHighDate_TIME,@t4b)& ' ASN:'&
FORMAT(ASNHdr:tmShipTime_TIME,@T4b))
AddSysLog('4Low:'& ldtLowDate_DATE & ' High:' & ldtHighDate_DATE)
AddSysLog('4Low:'& FORMAT(ldtLowDate_DATE,@d12b) & ' High:' &
FORMAT(ldtHighDate_DATE,@d12b))
..=2E...
! End of "After SET() issued" This is the template:
Process:View{Prop:Filter} =3D |
'ShipDtl:dtOurShipDate >=3D ldtLowDate AND ShipDtl:dtOurShipDate <<=3D
ldtHi' & |
'ghDate AND (ShipDtl:sTradingPartner =3D ASNHdr:sTradingPartner AND
ShipD' & |
'tl:sShipTo =3D ASNHdr:sShipTo)'
! Start of "Before Open View"
! [Priority 5000]
! standard filter
AddSysLog(Process:View{Prop:Filter})
Results in System Log [note this only records 120 characters]
1Low:5364001 High:5436001 14:54:00 High:15:06:00
2ASN:74511 5400001
2ASN:20041229 15:00:00
3Low:5364001 High:5436001 ASN:5400001
3Low:14:54:00 High:15:06:00 ASN:15:00:00
4Low:74511 High:74511
4Low:20041229 High:20041229
ShipDtl:dtOurShipDate >=3D ldtLowDate AND ShipDtl:dtOurShipDate <=3D
ldtHighDate AND (ShipDtl:sTradingPa
SQL Log:
08B0H(2) 09:56:35.406 Preparing Statement 0739618H : SELECT
A=2ESADDITIONAL, A.IINDOCSYSID, A.ISHIPCTNSYSID, A.ITRUCKSYSID,
A=2EISHIPDTLSYSID, A.IASNHDRSYSID, A.STRADINGPARTNER, A.SSHIPTO,
A=2ESPLANTID, A.SPROJECTNBR, A.SPARTNBR, A.SPARTTYPE, A.SUNITOFMEASURE,
A=2ERUNITPRICE, A.RQTYPERPACKAGE, A.RQTYSHIPPED, A.RQTYREQUESTED,
A=2ERORIGQTY, A.RWT, A.SREFNBR, A.SEXTERNALNBR, A.SORIGDOC, A.SSKU,
A=2ESTYPE, A.SDOCKNBR, A.DTSHIPDATE, A.DTOURSHIPDATE, A.DTDELIVERYDATE,
A=2EDTSTARTDATE, A.DTENDDATE, A.SFORECASTTYPE, A.SFORECASTTIMING,
A=2ESTD503, A.SJOBSEQUENCE, A.SPRODUCTID, A.SPURCHASEORDER,
A=2EBPRINTLABELS, A.BLABELSPRINTED, A.BSTAGED, A.BSHIPPED, A.BWARNING,
A=2EIFSTOFFSET, A.ILINOFFSET, A.ILIFECYCLE, A.BFIRM, A.SSTATUS,
A=2EBSPARE1, A.BSPARE2, A.ISPARE1, A.ISPARE2, A.SSPARE1, A.SSPARE2,
A=2ESDATETIMEQUAL, A.SREFQUAL, A.SLINEFEED, A.SRESERVELF,
A=2EDTORDERRECEIVED, A.SMODIFIEDBY, A.IINVOICEHDRSYSID, A.IPOHDRSYSID,
A=2ESASSIGNEDID, A.RQTYACKNOWLEDGED, A.SSHIPPEDPARTNBR,
A=2ESSUBSTITUTEPARTNBR, A.SRETURNMSG, A.SPACKAGECODE, A.RQTYINVOICED,
A=2ERAMTINVOICED, A.RAMTRECEIVED, A.BTAXABLE, A.STAXTYPE1,
A=2ERTAXPERCENT1, A.STAXTYPE2, A.RTAXPERCENT2, A.DTINVOICED, A.DTPICKUP,
A=2EDTRECEIVED, A.DTPODATE, A.SORDERSTATUS, A.SSUPPLIERCODE FROM
dbo.ShipDtl A WHERE ( A.DTOURSHIPDATE >=3D '' + {fn CHAR(29)} + {fn
CHAR(12)} + '=D4' + {fn CHAR(7)} + {fn CHAR(0)} + {fn CHAR(0)} + '6' +
{fn CHAR(14)}' AND A.DTOURSHIPDATE <=3D '' + {fn CHAR(29)} + {fn
CHAR(12)} + '=D4' + {fn CHAR(7)} + {fn CHAR(0)} + {fn CHAR(0)} + {fn
CHAR(6)} + {fn CHAR(15)}' AND A.STRADINGPARTNER =3D 'HONDA ' AND
A=2ESSHIPTO =3D 'AP1 ' ) ORDER BY A.DTOURSHIPDATE ASC,
A=2ESTRADINGPARTNER ASC, A.SPARTNBR ASC Time Taken:0.00 secs
08B0H(2) 09:56:35.406 Error Occurred: 37000 [Microsoft][ODBC SQL Server
Driver][SQL Server]Line 1: Incorrect syntax near ' AND A.DTOURSHIPDATE
<=3D ' + {fn CHAR(29)} + {fn CHAR(12)} + '.
08B0H(2) 09:56:35.406 Time Taken:0.00 secs
08B0H(2) 09:56:35.406 NEXT(VIEW:202DF4:dbo.ShipDtl:020E8F0H) Line 1:
Incorrect syntax near ' AND A.DTOURSHIPDATE <=3D ' + {fn CHAR(29)} + {fn
CHAR(12)} + '. Time Taken:0.00 secs
Why do the individual characters in ldtLowDate and ldtHighDate get put
into this query and how do I correct this?
Thanks in advance
Paul
|
|