SQL Query
I have this query:
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
SO_SalesOrderHistoryDetail
SY_Country C,
AR_Customer D,
CI_ExtendedDescription E,
RA_ReceiptsHistoryHeader F
WHERE
A.CustomerNo = D.CustomerNo
and A.ARDivisionNo = D.ARDivisionNo
and A.orderstatus = ''A''
and A.SalesOrderNo = B.SalesOrderNo
and C.CountryCode = D.CountryCode
and B.ExtendedDescriptionKey = E.ExtendedDescriptionKey
and A.RMANo = F.RMaNo
I'd like also include in the results, all records from SO_SalesOrderHistoryHeader
How can I do this?
Thanks.
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
SO_SalesOrderHistoryDetail
SY_Country C,
AR_Customer D,
CI_ExtendedDescription E,
RA_ReceiptsHistoryHeader F
WHERE
A.CustomerNo = D.CustomerNo
and A.ARDivisionNo = D.ARDivisionNo
and A.orderstatus = ''A''
and A.SalesOrderNo = B.SalesOrderNo
and C.CountryCode = D.CountryCode
and B.ExtendedDescriptionKey = E.ExtendedDescriptionKey
and A.RMANo = F.RMaNo
I'd like also include in the results, all records from SO_SalesOrderHistoryHeader
How can I do this?
Thanks.
sorry
.. reading your query again you need an outer join with ReceiptHistoryHeader and then you need to change the where clause.
you might need to break this into two querys or use a cte.
.. reading your query again you need an outer join with ReceiptHistoryHeader and then you need to change the where clause.
you might need to break this into two querys or use a cte.
if I understand your requirements, try something like 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,
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 omitted to say that the query works with a linked server via odbc therefore this syntax doesn't work.
I have this error:
"Unexpected extra token: Left"
I have this error:
"Unexpected extra token: Left"
do a union query
select
<your query>
union
< second query here only choose rmanno=null )
All fields selected should be same
union will give cumulative results for both conditions
'
select
<your query>
union
< second query here only choose rmanno=null )
All fields selected should be same
union will give cumulative results for both conditions
'
Here below the complete working query.
I'd like also include in the results, all records from SO_SalesOrderHistoryHeader
Today the query is using the linked server (SAGE) via ODBC.
Thanks for your help.
Select * from openquery ([SAGE],
'SELECT
A.OrderStatus,
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.SalespersonNo,
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
SO_SalesOrderHistoryDetail
SY_Country C,
AR_Customer D,
CI_ExtendedDescription E,
RA_ReceiptsHistoryHeader F
WHERE
A.CustomerNo = D.CustomerNo
and A.ARDivisionNo = D.ARDivisionNo
and A.orderstatus = ''A''
and B.CancelledLine = ''N''
and A.SalesOrderNo = B.SalesOrderNo
and C.CountryCode = D.CountryCode
and B.ExtendedDescriptionKey = E.ExtendedDescriptionKey
and A.RMANo = F.RmaNo'
I'd like also include in the results, all records from SO_SalesOrderHistoryHeader
Today the query is using the linked server (SAGE) via ODBC.
Thanks for your help.
Select * from openquery ([SAGE],
'SELECT
A.OrderStatus,
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.SalespersonNo,
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
SO_SalesOrderHistoryDetail
SY_Country C,
AR_Customer D,
CI_ExtendedDescription E,
RA_ReceiptsHistoryHeader F
WHERE
A.CustomerNo = D.CustomerNo
and A.ARDivisionNo = D.ARDivisionNo
and A.orderstatus = ''A''
and B.CancelledLine = ''N''
and A.SalesOrderNo = B.SalesOrderNo
and C.CountryCode = D.CountryCode
and B.ExtendedDescriptionKey = E.ExtendedDescriptionKey
and A.RMANo = F.RmaNo'
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'
This structure works in this way only:
{fn SO_SalesOrderHistoryHeader
Left join RA_ReceiptsHistoryHeader F on A.RMANo = F.RMano}
I must to exclude all the other tables. So I miss the other information coming from the other external tables.
Does it make sense?
{fn SO_SalesOrderHistoryHeader
Left join RA_ReceiptsHistoryHeader F on A.RMANo = F.RMano}
I must to exclude all the other tables. So I miss the other information coming from the other external tables.
Does it make sense?
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'
The query you posted above doesn't work.
This is the error message:
"[ProvideX][ODBC Driver]Expected lexical element not found: <identifier>".
This is the error message:
"[ProvideX][ODBC Driver]Expected lexical element not found: <identifier>".
If you mean the first simplified query, yes it runs.
But, if I use that, I miss a lot of information needed.
But, if I use that, I miss a lot of information needed.
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'
OLE DB provider "MSDASQL" for linked server "SAGE" returned message "[ProvideX][ODBC Driver]Expected lexical element not found: <identifier>".
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'
Error message:
OLE DB provider "MSDASQL" for linked server "SAGE" returned message "[ProvideX][ODBC Driver]Unexpected extra token: inner".
OLE DB provider "MSDASQL" for linked server "SAGE" returned message "[ProvideX][ODBC Driver]Unexpected extra token: inner".
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'
error message
OLE DB provider "MSDASQL" for linked server "SAGE" returned message "[ProvideX][ODBC Driver]Expected lexical element not found: <identifier>".
OLE DB provider "MSDASQL" for linked server "SAGE" returned message "[ProvideX][ODBC Driver]Expected lexical element not found: <identifier>".
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.
I havent used ProvideX but I have found this manual. Have a look at the join syntax
https://manual.pvxplus.com/PXPLUS/odbc/using_odbc_driver/example_sql.htm#Mark33
https://manual.pvxplus.com/PXPLUS/odbc/using_odbc_driver/example_sql.htm#Mark33
It seems it doesn't accept more than one join.
This works:
Select * from openquery ([SAGE],
'SELECT A.SalesOrderNo FROM
{fn SO_SalesOrderHistoryHeader
inner join SO_SalesOrderHistoryDetail
WHERE A.orderstatus = ''A''')
This works:
Select * from openquery ([SAGE],
'SELECT A.SalesOrderNo FROM
{fn SO_SalesOrderHistoryHeader
inner join SO_SalesOrderHistoryDetail
WHERE A.orderstatus = ''A''')
SELECT A.SalesOrderNo FROM
{ SO_SalesOrderHistoryHeader
inner join AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo }
WHERE A.orderstatus = 'A'
does that run?
{ SO_SalesOrderHistoryHeader
inner join AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo }
WHERE A.orderstatus = 'A'
does that run?
ok great. So how about this:
SELECT A.SalesOrderNo FROM
{oj SO_SalesOrderHistoryHeader
inner join AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo
inner join SO_SalesOrderHistoryDetail
WHERE A.orderstatus = 'A'
SELECT A.SalesOrderNo FROM
{oj SO_SalesOrderHistoryHeader
inner join AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo
inner join SO_SalesOrderHistoryDetail
WHERE A.orderstatus = 'A'
OLE DB provider "MSDASQL" for linked server "SAGE" returned message "[ProvideX][ODBC Driver]Expected lexical element not found: <identifier>".
Then, I tried this solution:
Select ....
FROM
SO_SalesOrderHistoryDetail
{oj RA_ReceiptsHistoryHeader F LEFT OUTER JOIN SO_SalesOrderHistoryHeader
WHERE
A.SalesOrderNo = B.SalesOrderNo AND
A.ARDivisionNo = D.ARDivisionNo AND
A.CustomerNo = D.CustomerNo AND
C.CountryCode = D.CountryCode AND
B.ExtendedDescriptionKey = E.ExtendedDescriptionKey AND
A.orderstatus = ''A'' AND
B.CancelledLine = ''N''
OLE DB provider "MSDASQL" for linked server "SAGE" returned message "[ProvideX][ODBC Driver]Timeout expired.".
Then, I tried this solution:
Select ....
FROM
SO_SalesOrderHistoryDetail
{oj RA_ReceiptsHistoryHeader F LEFT OUTER JOIN SO_SalesOrderHistoryHeader
WHERE
A.SalesOrderNo = B.SalesOrderNo AND
A.ARDivisionNo = D.ARDivisionNo AND
A.CustomerNo = D.CustomerNo AND
C.CountryCode = D.CountryCode AND
B.ExtendedDescriptionKey = E.ExtendedDescriptionKey AND
A.orderstatus = ''A'' AND
B.CancelledLine = ''N''
OLE DB provider "MSDASQL" for linked server "SAGE" returned message "[ProvideX][ODBC Driver]Timeout expired.".
if we cant figure out the syntax, how about using another db engine like Access or MS SQL.
Import the data and create your query.
Import the data and create your query.
select ................
FROM
SO_SalesOrderHistoryDetail
{oj RA_ReceiptsHistoryHeader F LEFT OUTER JOIN SO_SalesOrderHistoryHeader
WHERE
A.SalesOrderNo = B.SalesOrderNo AND
A.ARDivisionNo = D.ARDivisionNo AND
A.CustomerNo = D.CustomerNo AND
C.CountryCode = D.CountryCode AND
B.ExtendedDescriptionKey = E.ExtendedDescriptionKey AND
A.orderstatus = 'A'
FROM
SO_SalesOrderHistoryDetail
{oj RA_ReceiptsHistoryHeader F LEFT OUTER JOIN SO_SalesOrderHistoryHeader
WHERE
A.SalesOrderNo = B.SalesOrderNo AND
A.ARDivisionNo = D.ARDivisionNo AND
A.CustomerNo = D.CustomerNo AND
C.CountryCode = D.CountryCode AND
B.ExtendedDescriptionKey = E.ExtendedDescriptionKey AND
A.orderstatus = 'A'
Premium Content
You need an Expert Office subscription to comment.Start Free Trial