SELECT
A.SalesOrderNo,
B.UDF_COMPLETE_PART,
B.UDF_CUST_PART,
E.ExtendedDescriptionText,
B.UDF_Serial,
A.CustomerPONo,
A.UDF_EXT_CUST_PO,
A.ARDivisionNo,
D.CustomerNo,
D.CustomerName,
A.ConfirmTo,
A.EmailAddress,
A.UDF_END_USER,
A.UDF_REG_MANAGER,
A.UDF_REP_ACCT,
A.BillToName,
A.BillToCity,
A.BillToState,
A.ShipToName,
A.ShipToCity,
A.ShipToState,
C.CountryName,
A.RMANo,
F.ReceiptDate
FROM
SO_SalesOrderHistoryHeader A
inner join SO_SalesOrderHistoryDetail B on A.SalesOrderNo = B.SalesOrderNo
inner join AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo
inner join SY_Country C on C.CountryCode = D.CountryCode
inner join CI_ExtendedDescription E on B.ExtendedDescriptionKey = E.ExtendedDescriptionKey
Left join RA_ReceiptsHistoryHeader F on A.RMANo = F.RMaNo
WHERE
A.orderstatus = 'A'
I found this:
might be your solution.
SELECT A.SalesOrderNo, B.UDF_COMPLETE_PART, B.UDF_CUST_PART, E.ExtendedDescriptionText, B.UDF_Serial, A.CustomerPONo, A.UDF_EXT_CUST_PO, A.ARDivisionNo, D.CustomerNo, D.CustomerName, A.ConfirmTo, A.EmailAddress, A.UDF_END_USER, A.UDF_REG_MANAGER, A.UDF_REP_ACCT, A.BillToName, A.BillToCity, A.BillToState, A.ShipToName, A.ShipToCity, A.ShipToState, C.CountryName, A.RMANo, F.ReceiptDate
FROM
{ SO_SalesOrderHistoryHeader A inner join SO_SalesOrderHistoryDetail B on A.SalesOrderNo = B.SalesOrderNo
inner join AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo
inner join SY_Country C on C.CountryCode = D.CountryCode
inner join CI_ExtendedDescription E on B.ExtendedDescriptionKey = E.ExtendedDescriptionKey
Left join RA_ReceiptsHistoryHeader F on A.RMANo = F.RMaNo }
WHERE A.orderstatus = 'A'
Difficult for me to debug as I dont have a SAGE server to hand.
maybe remove the space in front of the {
SELECT A.SalesOrderNo,
B.UDF_COMPLETE_PART,
B.UDF_CUST_PART,
E.ExtendedDescriptionText,
B.UDF_Serial,
A.CustomerPONo,
A.UDF_EXT_CUST_PO,
A.ARDivisionNo,
D.CustomerNo,
D.CustomerName,
A.ConfirmTo,
A.EmailAddress,
A.UDF_END_USER,
A.UDF_REG_MANAGER,
A.UDF_REP_ACCT,
A.BillToName,
A.BillToCity,
A.BillToState,
A.ShipToName,
A.ShipToCity,
A.ShipToState,
C.CountryName,
A.RMANo,
F.ReceiptDate
FROM
{SO_SalesOrderHistoryHeader A
inner join SO_SalesOrderHistoryDetail B on A.SalesOrderNo = B.SalesOrderNo
inner join AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo
inner join SY_Country C on C.CountryCode = D.CountryCode
inner join CI_ExtendedDescription E on B.ExtendedDescriptionKey = E.ExtendedDescriptionKey
Left join RA_ReceiptsHistoryHeader F on A.RMANo = F.RMano}
WHERE
A.orderstatus = 'A'
Ok Lets see if we can simplify a bit.
Can you create a query that gets you the RMano - something like this:
SELECT A.SaleOrderNo,
A.CustomerPONo,
A.UDF_EXT_CUST_PO,
A.ARDivisionNo,
A.ConfirmTo,
A.EmailAddress,
A.UDF_END_USER,
A.UDF_REG_MANAGER,
A.UDF_REP_ACCT,
A.BillToName,
A.BillToCity,
A.BillToState,
A.ShipToName,
A.ShipToCity,
A.ShipToState,
A.RMANo,
F.ReceiptDate
FROM
{SO_SalesOrderHistoryHeader A
Left join RA_ReceiptsHistoryHeader F on A.RMANo = F.RMano}
WHERE
A.orderstatus = 'A'
and then another query with the other records:
SELECT
A.SalesOrderNo,
B.UDF_COMPLETE_PART,
B.UDF_CUST_PART,
E.ExtendedDescriptionText,
B.UDF_Serial,
A.CustomerPONo,
A.UDF_EXT_CUST_PO,
A.ARDivisionNo,
D.CustomerNo,
D.CustomerName,
A.ConfirmTo,
A.EmailAddress,
A.UDF_END_USER,
A.UDF_REG_MANAGER,
A.UDF_REP_ACCT,
A.BillToName,
A.BillToCity,
A.BillToState,
A.ShipToName,
A.ShipToCity,
A.ShipToState,
C.CountryName,
A.RMANo
FROM
{SO_SalesOrderHistoryHeader A
inner join SO_SalesOrderHistoryDetail B on A.SalesOrderNo = B.SalesOrderNo
inner join AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo
inner join SY_Country C on C.CountryCode = D.CountryCode
inner join CI_ExtendedDescription E on B.ExtendedDescriptionKey = E.ExtendedDescriptionKey
WHERE
A.orderstatus = 'A'
verify that can run and are correct?
Does the first query run?
does this run?
SELECT
A.SalesOrderNo,
B.UDF_COMPLETE_PART,
B.UDF_CUST_PART,
E.ExtendedDescriptionText,
B.UDF_Serial,
A.CustomerPONo,
A.UDF_EXT_CUST_PO,
A.ARDivisionNo,
D.CustomerNo,
D.CustomerName,
A.ConfirmTo,
A.EmailAddress,
A.UDF_END_USER,
A.UDF_REG_MANAGER,
A.UDF_REP_ACCT,
A.BillToName,
A.BillToCity,
A.BillToState,
A.ShipToName,
A.ShipToCity,
A.ShipToState,
C.CountryName,
A.RMANo
FROM
{SO_SalesOrderHistoryHeader A
inner join SO_SalesOrderHistoryDetail B on A.SalesOrderNo = B.SalesOrderNo
inner join AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo
inner join SY_Country C on C.CountryCode = D.CountryCode
inner join CI_ExtendedDescription E on B.ExtendedDescriptionKey = E.ExtendedDescriptionKey}
WHERE
A.orderstatus = 'A'
if that doesnt work - run this:
SELECT
A.SalesOrderNo,
B.UDF_COMPLETE_PART,
B.UDF_CUST_PART,
E.ExtendedDescriptionText,
B.UDF_Serial,
A.CustomerPONo,
A.UDF_EXT_CUST_PO,
A.ARDivisionNo,
D.CustomerNo,
D.CustomerName,
A.ConfirmTo,
A.EmailAddress,
A.UDF_END_USER,
A.UDF_REG_MANAGER,
A.UDF_REP_ACCT,
A.BillToName,
A.BillToCity,
A.BillToState,
A.ShipToName,
A.ShipToCity,
A.ShipToState,
C.CountryName,
A.RMANo
FROM
SO_SalesOrderHistoryHeader A
inner join SO_SalesOrderHistoryDetail B on A.SalesOrderNo = B.SalesOrderNo
inner join AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo
inner join SY_Country C on C.CountryCode = D.CountryCode
inner join CI_ExtendedDescription E on B.ExtendedDescriptionKey = E.ExtendedDescriptionKey
WHERE
A.orderstatus = 'A'
error message?
Syntax error somewhere so lets break it down a bit ..
SELECT A.SalesOrderNo FROM SO_SalesOrderHistoryHeader A
inner join
SO_SalesOrderHistoryDetail B on A.SalesOrderNo = B.SalesOrderNo
inner join
AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo
inner join
SY_Country C on C.CountryCode = D.CountryCode
inner join
CI_ExtendedDescription E on B.ExtendedDescriptionKey = E.ExtendedDescriptionKey
WHERE A.orderstatus = 'A'
SELECT A.SalesOrderNo FROM
{SO_SalesOrderHistoryHeader A
inner join SO_SalesOrderHistoryDetail B on A.SalesOrderNo = B.SalesOrderNo
inner join AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo
inner join SY_Country C on C.CountryCode = D.CountryCode inner join CI_ExtendedDescription E on B.ExtendedDescriptionKey = E.ExtendedDescriptionKey}
WHERE A.orderstatus = 'A'
SELECT A.SalesOrderNo FROM
{SO_SalesOrderHistoryHeader A
inner join SO_SalesOrderHistoryDetail B on A.SalesOrderNo = B.SalesOrderNo
inner join AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo
inner join SY_Country C on C.CountryCode = D.CountryCode inner join CI_ExtendedDescription E on B.ExtendedDescriptionKey = E.ExtendedDescriptionKey}
WHERE A.orderstatus = "A"
Also double check that the table and field names are correct.
Put
Open in new window