Syntax error in sql statement - CAST(NULL AS c(5) NOCPTRANS)

Feb 9, 2015 at 10:00 AM
Tom
We are trying to use Breeze and it can generate large Union sql statements
(Example below from Vfpclient.txt)
The problem is CAST(NULL AS c(5) NOCPTRANS) AS C41, ;
It does not like the NOCPTRANS.



SELECT ;
U1.Id AS C1, ;
U1.C2, ;
U1.Id1 AS C3, ;
U1.Currency AS C4, ;
U1.Idcurrency AS C5, ;
U1.Defgoods AS C6, ;
U1.Defservice AS C7, ;
U1.Invsupply AS C8, ;
U1.Cnsupply AS C9, ;
U1.Payid AS C10, ;
U1.Idpayterm AS C11, ;
U1.Settid AS C12, ;
U1.Idsettterm AS C13, ;
U1.Listid AS C14, ;
U1.Specialid AS C15, ;
U1.Groupid AS C16, ;
U1.Idcusgrp AS C17, ;
U1.Acctid AS C18, ;
U1.Index AS C19, ;
U1.Creditlim AS C20, ;
U1.Opened AS C21, ;
U1.Turnover AS C22, ;
U1.Trade AS C23, ;
U1.Factored AS C24, ;
U1.Factref AS C25, ;
U1.Vatreg AS C26, ;
U1.Lastpay AS C27, ;
U1.Lastinv AS C28, ;
U1.Stop AS C29, ;
U1.Prtbatch AS C30, ;
U1.Prtserial AS C31, ;
U1.Transport AS C32, ;
U1.Delivterms AS C33, ;
U1.Nature AS C34, ;
U1.Obsolete AS C35, ;
U1.Theirindex AS C36, ;
U1.Altccyid AS C37, ;
U1.Altinv AS C38, ;
U1.Altst AS C39, ;
U1.Altqte AS C40, ;
U1.Altso AS C41, ;
U1.Bacsman AS C42, ;
U1.Bacsname AS C43, ;
U1.Bacsno AS C44, ;
U1.Bacssort AS C45, ;
U1.Bacstype AS C46, ;
U1.Largeord AS C47, ;
U1.Prtbtchref AS C48, ;
U1.Os_Po AS C49, ;
U1.Cs_Raise AS C50, ;
U1.Webstore_Id AS C51, ;
U1.Webfreeship AS C52, ;
U1._Exportoff AS C53, ;
U1.Idpost AS C54, ;
U1.Lastchange AS C55, ;
U1.M_Disc AS C56, ;
U1.M_Invadd AS C57, ;
U1.M_Notes AS C58, ;
U1.Id2 AS C59, ;
U1.C3 AS C60, ;
U1.Id3 AS C61, ;
U1.C4 AS C62, ;
U1.C1 AS C63, ;
U1.Id4 AS C64, ;
U1.C5 AS C65, ;
U1.Id5 AS C66, ;
U1.Acctid1 AS C67, ;
U1.Idaccount AS C68, ;
U1.Idpayterm1 AS C69, ;
U1.Idsettterm1 AS C70, ;
U1.Idlocation AS C71, ;
U1.Orderno AS C72, ;
U1.Date AS C73, ;
U1.Custref AS C74, ;
U1.Trade1 AS C75, ;
U1.Notes AS C76, ;
U1.Special AS C77, ;
U1.Deliver AS C78, ;
U1.Status AS C79, ;
U1.Inclusive AS C80, ;
U1.Dnopt AS C81, ;
U1.Ackdate AS C82, ;
U1.Ackcnt AS C83, ;
U1.Held AS C84, ;
U1.Reason AS C85, ;
U1.Delterms AS C86, ;
U1.Quoteid AS C87, ;
U1.Delopt AS C88, ;
U1.Marginscme AS C89, ;
U1.Dnbopt AS C90, ;
U1.Dnaopt AS C91, ;
U1.Jobid AS C92, ;
U1.Pooveride AS C93, ;
U1.Projid AS C94, ;
U1.Prospect AS C95, ;
U1.Weborderid AS C96, ;
U1.Webstore_Id1 AS C97, ;
U1.Copyfree AS C98, ;
U1.Layaway AS C99, ;
U1.Mktid AS C100, ;
U1._Exportoff1 AS C101, ;
U1.M_Over AS C102, ;
U1.M_Special AS C103, ;
U1.Prefquote AS C104, ;
U1.C6 AS C105, ;
U1.C7 AS C106, ;
U1.C8 AS C107, ;
U1.C9 AS C108, ;
U1.C10 AS C109, ;
U1.C11 AS C110, ;
U1.C12 AS C111, ;
U1.C13 AS C112, ;
U1.C14 AS C113 ;
FROM (SELECT ;
 CAST( ICASE(ISNULL(E2.Id),CAST(NULL AS i),1) AS i) AS C1,  ;
E1.Id,  ;
__vfpClient___C2P__1 AS C2,  ;
E1.Id AS Id1,  ;
E1.Currency,  ;
E1.Idcurrency,  ;
E1.Defgoods,  ;
E1.Defservice,  ;
E1.Invsupply,  ;
E1.Cnsupply,  ;
E1.Payid,  ;
E1.Idpayterm,  ;
E1.Settid,  ;
E1.Idsettterm,  ;
E1.Listid,  ;
E1.Specialid,  ;
E1.Groupid,  ;
E1.Idcusgrp,  ;
E1.Acctid,  ;
E1.Index,  ;
 CAST( E1.Creditlim AS n(20,2)) AS Creditlim,  ;
E1.Opened,  ;
E1.Turnover,  ;
 CAST( E1.Trade AS n(20,2)) AS Trade,  ;
E1.Factored,  ;
E1.Factref,  ;
E1.Vatreg,  ;
E1.Lastpay,  ;
E1.Lastinv,  ;
E1.Stop,  ;
E1.Prtbatch,  ;
E1.Prtserial,  ;
E1.Transport,  ;
E1.Delivterms,  ;
E1.Nature,  ;
E1.Obsolete,  ;
E1.Theirindex,  ;
E1.Altccyid,  ;
E1.Altinv,  ;
E1.Altst,  ;
E1.Altqte,  ;
E1.Altso,  ;
E1.Bacsman,  ;
E1.Bacsname,  ;
E1.Bacsno,  ;
E1.Bacssort,  ;
 CAST( E1.Bacstype AS n(20,2)) AS Bacstype,  ;
E1.Largeord,  ;
E1.Prtbtchref,  ;
E1.Os_Po,  ;
 CAST( E1.Cs_Raise AS n(20,2)) AS Cs_Raise,  ;
E1.Webstore_Id,  ;
E1.Webfreeship,  ;
 CAST( E1._Exportoff AS n(20,2)) AS _Exportoff,  ;
E1.Idpost,  ;
E1.Lastchange,  ;
E1.M_Disc,  ;
E1.M_Invadd,  ;
E1.M_Notes,  ;
E1.Id AS Id2,  ;
__vfpClient___C2P__2 AS C3,  ;
E1.Id AS Id3,  ;
__vfpClient___C2P__3 AS C4,  ;
E2.Id AS Id4,  ;
 CAST( ICASE(ISNULL(E2.Id),CAST(NULL AS v(254)),__vfpClient___C2P__4) AS v(254)) AS C5,  ;
E2.Id AS Id5,  ;
E2.Acctid AS Acctid1,  ;
E2.Idaccount,  ;
E2.Idpayterm AS Idpayterm1,  ;
E2.Idsettterm AS Idsettterm1,  ;
E2.Idlocation,  ;
E2.Orderno,  ;
E2.Date,  ;
E2.Custref,  ;
 CAST( E2.Trade AS n(20,2)) AS Trade1,  ;
E2.Notes,  ;
E2.Special,  ;
E2.Deliver,  ;
E2.Status,  ;
E2.Inclusive,  ;
 CAST( E2.Dnopt AS n(20,2)) AS Dnopt,  ;
E2.Ackdate,  ;
E2.Ackcnt,  ;
E2.Held,  ;
E2.Reason,  ;
E2.Delterms,  ;
E2.Quoteid,  ;
 CAST( E2.Delopt AS n(20,2)) AS Delopt,  ;
E2.Marginscme,  ;
 CAST( E2.Dnbopt AS n(20,2)) AS Dnbopt,  ;
 CAST( E2.Dnaopt AS n(20,2)) AS Dnaopt,  ;
E2.Jobid,  ;
E2.Pooveride,  ;
E2.Projid,  ;
E2.Prospect,  ;
E2.Weborderid,  ;
E2.Webstore_Id AS Webstore_Id1,  ;
E2.Copyfree,  ;
E2.Layaway,  ;
E2.Mktid,  ;
 CAST( E2._Exportoff AS n(20,2)) AS _Exportoff1,  ;
 CAST( E2.M_Over AS n(20,2)) AS M_Over,  ;
 CAST( E2.M_Special AS n(20,2)) AS M_Special,  ;
E2.Prefquote,  ;
CAST(NULL AS i) AS C6,  ;
CAST(NULL AS v(254)) AS C7,  ;
CAST(NULL AS i) AS C8,  ;
CAST(NULL AS i) AS C9,  ;
CAST(NULL AS i) AS C10,  ;
CAST(NULL AS i) AS C11,  ;
CAST(NULL AS n(18,2)) AS C12,  ;
CAST(NULL AS n(18,2)) AS C13,  ;
CAST(NULL AS c(15) NOCPTRANS) AS C14 ;
FROM  Account E1 ;
LEFT JOIN Sorder E2 ON E1.Id = E2.Idaccount ;
UNION ALL ;
SELECT  ;
2 AS C1,  ;
E3.Id,  ;
__vfpClient___C2P__5 AS C2,  ;
E3.Id AS Id1,  ;
E3.Currency,  ;
E3.Idcurrency,  ;
E3.Defgoods,  ;
E3.Defservice,  ;
E3.Invsupply,  ;
E3.Cnsupply,  ;
E3.Payid,  ;
E3.Idpayterm,  ;
E3.Settid,  ;
E3.Idsettterm,  ;
E3.Listid,  ;
E3.Specialid,  ;
E3.Groupid,  ;
E3.Idcusgrp,  ;
E3.Acctid,  ;
E3.Index,  ;
 CAST( E3.Creditlim AS n(20,2)) AS Creditlim,  ;
E3.Opened,  ;
E3.Turnover,  ;
 CAST( E3.Trade AS n(20,2)) AS Trade,  ;
E3.Factored,  ;
E3.Factref,  ;
E3.Vatreg,  ;
E3.Lastpay,  ;
E3.Lastinv,  ;
E3.Stop,  ;
E3.Prtbatch,  ;
E3.Prtserial,  ;
E3.Transport,  ;
E3.Delivterms,  ;
E3.Nature,  ;
E3.Obsolete,  ;
E3.Theirindex,  ;
E3.Altccyid,  ;
E3.Altinv,  ;
E3.Altst,  ;
E3.Altqte,  ;
E3.Altso,  ;
E3.Bacsman,  ;
E3.Bacsname,  ;
E3.Bacsno,  ;
E3.Bacssort,  ;
 CAST( E3.Bacstype AS n(20,2)) AS Bacstype,  ;
E3.Largeord,  ;
E3.Prtbtchref,  ;
E3.Os_Po,  ;
 CAST( E3.Cs_Raise AS n(20,2)) AS Cs_Raise,  ;
E3.Webstore_Id,  ;
E3.Webfreeship,  ;
 CAST( E3._Exportoff AS n(20,2)) AS _Exportoff,  ;
E3.Idpost,  ;
E3.Lastchange,  ;
E3.M_Disc,  ;
E3.M_Invadd,  ;
E3.M_Notes,  ;
E3.Id AS Id2,  ;
__vfpClient___C2P__6 AS C3,  ;
E3.Id AS Id3,  ;
__vfpClient___C2P__7 AS C4,  ;
CAST(NULL AS i) AS C5,  ;
CAST(NULL AS v(254)) AS C6,  ;
CAST(NULL AS i) AS C7,  ;
CAST(NULL AS v(254) NOCPTRANS) AS C8,  ;
CAST(NULL AS i) AS C9,  ;
CAST(NULL AS i) AS C10,  ;
CAST(NULL AS i) AS C11,  ;
CAST(NULL AS i) AS C12,  ;
CAST(NULL AS c(10) NOCPTRANS) AS C13,  ;
CAST(NULL AS t) AS C14,  ;
CAST(NULL AS c(20) NOCPTRANS) AS C15,  ;
CAST(NULL AS n(18,2)) AS C16,  ;
CAST(NULL AS v(254) NOCPTRANS) AS C17,  ;
CAST(NULL AS v(254) NOCPTRANS) AS C18,  ;
CAST(NULL AS v(254) NOCPTRANS) AS C19,  ;
CAST(NULL AS v(254) NOCPTRANS) AS C20,  ;
CAST(NULL AS l) AS C21,  ;
CAST(NULL AS n(18,2)) AS C22,  ;
CAST(NULL AS t) AS C23,  ;
CAST(NULL AS c(2) NOCPTRANS) AS C24,  ;
CAST(NULL AS l) AS C25,  ;
CAST(NULL AS c(35) NOCPTRANS) AS C26,  ;
CAST(NULL AS c(3) NOCPTRANS) AS C27,  ;
CAST(NULL AS c(4) NOCPTRANS) AS C28,  ;
CAST(NULL AS n(18,2)) AS C29,  ;
CAST(NULL AS l) AS C30,  ;
CAST(NULL AS n(18,2)) AS C31,  ;
CAST(NULL AS n(18,2)) AS C32,  ;
CAST(NULL AS c(4) NOCPTRANS) AS C33,  ;
CAST(NULL AS l) AS C34,  ;
CAST(NULL AS c(4) NOCPTRANS) AS C35,  ;
CAST(NULL AS l) AS C36,  ;
CAST(NULL AS i) AS C37,  ;
CAST(NULL AS i) AS C38,  ;
CAST(NULL AS l) AS C39,  ;
CAST(NULL AS l) AS C40,  ;
CAST(NULL AS c(5) NOCPTRANS) AS C41,  ;
CAST(NULL AS n(18,2)) AS C42,  ;
CAST(NULL AS n(18,2)) AS C43,  ;
CAST(NULL AS n(18,2)) AS C44,  ;
CAST(NULL AS l) AS C45,  ;
E4.Id AS Id4,  ;
__vfpClient___C2P__8 AS C46,  ;
E4.Id AS Id5,  ;
E4.Idaccount,  ;
E4.Idaddress,  ;
E4.Mainuse,  ;
 CAST( E4.Subuse AS n(20,2)) AS Subuse,  ;
 CAST( E4._Exportoff AS n(20,2)) AS _Exportoff1,  ;
E4.Vatreg AS Vatreg1 ;
FROM  Account E3 ;
INNER JOIN Addrlink E4 ON E3.Id = E4.Idaccount) U1 ;
ORDER BY U1.Id1, U1.C1