Avatar of SALA DONATELLA
SALA DONATELLA
 asked on

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 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.
Microsoft SQL Server

Avatar of undefined
Last Comment
SALA DONATELLA

8/22/2022 - Mon
Peter Chan

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

Open in new window

Jonathan Kelly

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

or just add
OR RManNo Is null
to the where clause
Your help has saved me hundreds of hours of internet surfing.
fblack61
SALA DONATELLA

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

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.
SALA DONATELLA

ASKER
What is the cte?  Sorry, I'm not expert.
Thanks.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ryan Chong

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

Jonathan Kelly

yes left outer join as above.
SALA DONATELLA

ASKER
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 started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SALA DONATELLA

ASKER
ops..
"Unexpected extra token: INNER"
Partha Mandayam

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
'
Jonathan Kelly

dont inlcude the "inner" keyword
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SALA DONATELLA

ASKER
Same error:
"Unexpected extra token: union"
Partha Mandayam

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

ASKER
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'
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SALA DONATELLA

ASKER
Hello Expert.
Any suggestion about my question?
Jonathan Kelly

Jonathan Kelly

  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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SALA DONATELLA

ASKER
I have this message:

Expected lexical element not found: <identifier>
Jonathan Kelly

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'

Open in new window

SALA DONATELLA

ASKER
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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jonathan Kelly

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'   

Open in new window

Jonathan Kelly

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' 

Open in new window

Jonathan Kelly

verify that can run and are correct?

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SALA DONATELLA

ASKER
Unfortunately that doesn't work.
Jonathan Kelly

which one?
SALA DONATELLA

ASKER
The query you posted above doesn't work.
This is the error message:
"[ProvideX][ODBC Driver]Expected lexical element not found: <identifier>".
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jonathan Kelly

second query missing the closing }
SALA DONATELLA

ASKER
I saw and I put it.
Jonathan Kelly

still get the error?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SALA DONATELLA

ASKER
Yes, still get the error.
Jonathan Kelly

Does the first query run?

SALA DONATELLA

ASKER
If you mean the first simplified query, yes it runs.
But, if I use that, I miss a lot of information needed.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jonathan Kelly

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'  

Open in new window

Jonathan Kelly

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'  

Open in new window

SALA DONATELLA

ASKER
No, it doesn't.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jonathan Kelly

error message?

SALA DONATELLA

ASKER
OLE DB provider "MSDASQL" for linked server "SAGE" returned message "[ProvideX][ODBC Driver]Expected lexical element not found: <identifier>".
Jonathan Kelly

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'  

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
SALA DONATELLA

ASKER
Error message:

OLE DB provider "MSDASQL" for linked server "SAGE" returned message "[ProvideX][ODBC Driver]Unexpected extra token: inner".
Jonathan Kelly

  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'   

Open in new window

SALA DONATELLA

ASKER
error message

OLE DB provider "MSDASQL" for linked server "SAGE" returned message "[ProvideX][ODBC Driver]Expected lexical element not found: <identifier>".
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jonathan Kelly

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.

Jonathan Kelly

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
SALA DONATELLA

ASKER
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''')
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jonathan Kelly

Jonathan Kelly

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?
SALA DONATELLA

ASKER
Yes, that runs putting {fn or {oj  before the table name (SO_SalesOrderHistoryHeader A)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jonathan Kelly

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'
SALA DONATELLA

ASKER
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.".
Jonathan Kelly

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
SALA DONATELLA

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ryan Chong

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

ASKER
Thanks everybody for your help in this matter.