i've tried poh.po_number = poli.po_number and poh.bo_number = poli.po_number, <<< comma following

putting statements on separate lines (like your example)

deleting and poh.bo_number = poli.po_number thinking it was the second condition

switching order_header oh join order_line_items on... as the first join and moving purchase_order_header poh join po_line_items... to the second line

still gives me an error on the second line (table name changes)

should add i'm running ASA 11 (2331) 64 bit on a 2008 server sp1

Well, I'm stabbing in the dark here, but... if this works in your second query:

order_header oh join order_line_items oli on oh.order_seq_num = oli.order_seq_num join inventory_master im on oli.inventory_seq_num = im.seq_num

Then this should also work, right? It's identical but ordered the other way around to make it easier to connect to the other query.

inventory_master im join order_line_items oli on im.seq_num = oli.inventory_seq_num join order_header oh on oli.order_seq_num = oh.order_seq_num

Now I think you should be able to use the same join to inventory master as you do in your first query:

join po_line_items poli on 
             poh.po_number = poli.po_number and 
             poh.bo_number = poli.bo_number
 join inventory_master im on poli.seq_num = im.seq_num 
 join order_line_items oli on im.seq_num = oli.inventory_seq_num
 join order_header oh on oli.order_seq_num = oh.order_seq_num

I don't know your data model, so I don't know why you've introduced an outer join in your 3rd query. I also haven't read your error messages, so I'm probably missing something really obvious. For your next error message, tell us what the code was and what the message said!

carolstone wanted to know the error message.

the error message i'm getting from iSQL when i run the script below is: could not execute state. syntax error new 'oli' on line 12 SQLCODE = -131, ODBC 3 State='42000' Line 1, column 1

carolstone also asked about the outer join:
the query is looking for sales items that may or may not (outer join) have purchases coming in

i've put table creates pasted the "pretty" script at the end of this post. if Mr. Allen would pretty it up again, it will be easier to read.

you don't need data to get the error.

thanks for the suggestions

--drop table poh go create table poh ( po_number int not null, bo_number int not null, po_status char( 1 ) not null ) go create unique index kp_poh on poh ( po_number, bo_number ) go

--drop table poli go create table poli ( po_number int not null, bo_number int not null, line_number int not null, seq_num int not null, quantity_ordered int not null ) go create unique index kp_poli on poli ( po_number, bo_number, line_number ) go

--drop table oh go create table oh ( order_seq_num int not null, status char( 1 ) not null ) go create unique index kp_on on oh ( order_seq_num ) go

--drop table oli go create table oli ( order_seq_num int not null, line_number int not null, inventory_seq_num int not null, quantity_ordered int not null ) go create unique index kp_oli on oli ( order_seq_num, line_number ) go

--drop table im go create table im ( seq_num int not null, short_description char( 40 ) not null, container_size char( 20 ) not null ) go create unique index kp_im on im ( seq_num ) go

select im.seq_num, im.short_description, im.container_size,
sum( oli.quantity_ordered ) oli_qo, sum( poli.quantity_ordered ) poli_qo from poh join poli on poh.po_number = poli.po_number and poh.bo_number = poli.bo_number oh join oli on oh.order_seq_num = oli.order_seq_num join im on oli.inventory_seq_num = im.seq_num left outer join oli.inventory_seq_num = poli.seq_num where oh.status in ( 'a', 'b', 'o', 'p' ) and poh.po_status = 'o' group by im.seq_num, im.short_description, container_size ;