SQL Query

Hi,
Put

isnull(RMANo,'')=''

Select allOpen in new window

does he need to add
And RmanNo = ' '
to the where clause

or just add
OR RManNo Is null
to the where clause

Using AND it doesn't return any record.
Using OR is returns duplicate records.

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.

What is the cte?  Sorry, I'm not expert.
Thanks.

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'

Select allOpen in new window

yes left outer join as above.

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"

ops..
"Unexpected extra token: INNER"

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
'

dont inlcude the "inner" keyword

Same error:
"Unexpected extra token: union"

PLease post the full sql query you have written
You must have made some syntax errors

Here below the complete working query.
I'd like also include in the results, all records from SO_SalesOrderHistoryHeader having the RMANo = NULL
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 A,
      SO_SalesOrderHistoryDetail B,
      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'

Hello Expert.
Any suggestion about my question?

I found this:

https://www.sagecity.com/support_communities/sage100_erp/f/sage-100-technical-and-installation-discussions/39920/unexpected-extra-token-inner

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'  

Select allOpen in new window

I have this message:

Expected lexical element not found: <identifier>

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'

Select allOpen in new window

This structure works in this way only:

{fn SO_SalesOrderHistoryHeader A  
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'   

Select allOpen in new window

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' 

Select allOpen in new window

verify that can run and are correct?

Unfortunately that doesn't work.

which one?

The query you posted above doesn't work.
This is the error message:
"[ProvideX][ODBC Driver]Expected lexical element not found: <identifier>".

second query missing the closing }

I saw and I put it.

still get the error?

Yes, still get the error.

Does the first query run?

If you mean the first simplified query, yes it runs.
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'  

Select allOpen in new window

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'  

Select allOpen in new window

No, it doesn't.

error message?

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'  

Select allOpen in new window

Error message:

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'   

Select allOpen in new window

error message

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

It seems it doesn't accept more than one join.
This works:

Select * from openquery ([SAGE],
'SELECT A.SalesOrderNo FROM
{fn SO_SalesOrderHistoryHeader A      
inner join   SO_SalesOrderHistoryDetail B on A.SalesOrderNo = B.SalesOrderNo}
WHERE A.orderstatus = ''A''')

this says nested joins are possible:
https://manual.pvxplus.com/PXPLUS/odbc/using_odbc_driver.htm

SELECT A.SalesOrderNo FROM
{ SO_SalesOrderHistoryHeader A      
inner join  AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo  }
WHERE A.orderstatus = 'A'

does that run?

Yes, that runs putting {fn or {oj  before the table name (SO_SalesOrderHistoryHeader A)

ok great. So how about this:

SELECT A.SalesOrderNo FROM
{oj SO_SalesOrderHistoryHeader A      
inner join  AR_Customer D on A.CustomerNo = D.CustomerNo and A.ARDivisionNo = D.ARDivisionNo
inner join  SO_SalesOrderHistoryDetail B on A.SalesOrderNo = B.SalesOrderNo  }
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 B, SY_Country C, AR_Customer D, CI_ExtendedDescription E,
{oj RA_ReceiptsHistoryHeader F LEFT OUTER JOIN SO_SalesOrderHistoryHeader A ON A.RMANo = F.RMANo}

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.

I thought I have mentioned to use "Left join" at the first place? so apparently it seems doesn't help at all = /

Thanks everybody for your help in this matter.