We help IT Professionals succeed at work.

SQL Query

SALA DONATELLA
on
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 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 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 having RMANo = NULL
How can I do this?
Thanks.
Comment
Watch Question

HuaMin ChenProblem resolver

Commented:
Hi,
Put
isnull(RMANo,'')=''

Open 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

Author

Commented:
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.

Author

Commented:
What is the cte?  Sorry, I'm not expert.
Thanks.
Ryan ChongSoftware Team Lead

Commented:
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'

Open in new window

yes left outer join as above.

Author

Commented:
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"

Author

Commented:
ops..
"Unexpected extra token: INNER"
Partha MandayamTechnical Director

Commented:
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

Author

Commented:
Same error:
"Unexpected extra token: union"
Partha MandayamTechnical Director

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

Author

Commented:
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'

Author

Commented:
Hello Expert.
Any suggestion about my question?
  
 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'
   

Author

Commented:
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'
 

Author

Commented:
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'   

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?

Author

Commented:
Unfortunately that doesn't work.

Author

Commented:
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 }

Author

Commented:
I saw and I put it.
still get the error?

Author

Commented:
Yes, still get the error.

Does the first query run?

Author

Commented:
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'  

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'  

Author

Commented:
No, it doesn't.

error message?

Author

Commented:
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'  

Author

Commented:
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' 
   

Author

Commented:
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

Author

Commented:
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''')
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?

Author

Commented:
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'

Author

Commented:
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.
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'
Ryan ChongSoftware Team Lead
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.