on 2011 Oct 26 4:39 PM
--join 1 works
select im.seq_num, im.short_description, sum( poli.quantity_ordered ) poli_qo from purchase_order_header poh 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 where poh.po_status = 'o' group by im.seq_num, im.short_description ;
--join 2 works
select im.seq_num, im.short_description, container_size, sum( oli.quantity_ordered ) oli_qo from 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 where oh.status in ( 'a', 'b', 'o', 'p' ) group by im.seq_num, im.short_description, container_size;
--join 3 fails
select im.seq_num, im.short_description, container_size, sum( oli.quantity_ordered ) oli_qo, sum( poli.quantity_ordered ) poli_qo from purchase_order_header poh join po_line_items poli on poh.po_number = poli.po_number and poh.bo_number = poli.bo_number 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 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 ;
the error message is
[Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'order_header' on line 195
Request clarification before answering.
If you format it a bit, you'll notice you need a comma or a join or something before "order_header." Just like the error message says, it's a syntax error.
join po_line_items poli on poh.po_number = poli.po_number and poh.bo_number = poli.bo_number order_header oh join order_line_items
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 ;
See the [missing ...] items below... but that just gets you past the syntax errors; the left outer join may have other problems since the ON predicate seems to refer to tables already defined earlier in the query:
select im.seq_num, im.short_description, container_size, sum( oli.quantity_ordered ) oli_qo, sum( poli.quantity_ordered ) poli_qo from purchase_order_header poh join po_line_items poli on poh.po_number = poli.po_number and poh.bo_number = poli.bo_number join order_header oh [possible missing ON predicate] [missing join operator] 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 left outer join [missing table name] [missing ON keyword] 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 ;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Breck's comments are still exactly right. You're missing items.
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 **[missing join operator]** oh **[possible missing ON predicate]** join oli on oh.order_seq_num = oli.order_seq_num join im on oli.inventory_seq_num = im.seq_num left outer join **[missing table name]** **[missing ON operator]** 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 ;
I read your missing items as being items out of place. My suggestion brings back no errors, but it probably gives you the opposite of what you need:
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 join im on poli.seq_num = im.seq_num join oli on im.seq_num = oli.inventory_seq_num join oh on oli.order_seq_num = oh.order_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 ;
What my solution tells you is you can re-order your tables to get what you need. If you don't need to connect to the same table multiple times, don't do it. Just move the tables around until you get what you need. I can't stick around to finish this for you. But there should be enough here for you to get this figured out.
(To pretty-up your code, highlight your code and select the fifth icon from the left (it looks like 101 010), or press control-k. I haven't been able to make it work reliably in the comments section, but that's just me.)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This really is my best guess, I've kept the group by but I'd have just used two sub selects to make it easy to read.
select im.seq_num, im.short_description, container_size, oli_qo, (select sum(poli.quantity_ordered) from purchase_order_header poh join po_line_items poli ON poh.po_number = poli.po_number AND poh.bo_number = poli.bo_number where im.seq_num = poli.seq_num and poh.po_status = 'o') as poli_qo from ( SELECT im.seq_num, im.short_description, container_size, SUM(oli.quantity_ordered) oli_qo, FROM order_header oh JOIN order_line oli ON oh.order_seq_num = oli.order_seq_num JOIN inventory_master im ON oli.inventory_seq_num = im.seq_num WHERE oh.status IN ( 'a', 'b', 'o', 'p' ) GROUP BY im.seq_num, im.short_description, container_size; ) as foo
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
96 | |
11 | |
9 | |
9 | |
7 | |
5 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.