Hi!

This is probably a simple question, but my, also quite simple it seems, mind just can't figure it out.

I have this SQL query:

SELECT
i.ItemCode,
i.ItemName,
IFNULL(v.Version, ' ') AS 'Version',
SUM(inl.Qty) AS 'sum'
FROM PUB.ItemVers AS v
INNER JOIN PUB.Item AS i ON v.ItemCode=i.ItemCode
INNER JOIN PUB.ItemGrp AS g ON i.ItemGrp=g.ItemGrp
LEFT JOIN PUB.InvLine AS inl ON i.ItemCode=inl.ItemCode AND v.Version=inl.Version
LEFT JOIN PUB.Invoice AS inv ON inl.InvNum=inv.InvNum AND inv.InvoiceDate BETWEEN TO_DATE ('" & startdate & "') AND TO_DATE ('" & enddate & "')
WHERE g.ItemGrp = 'someGrp'
GROUP BY v.Version, i.ItemName, i.ItemCode

With the second LEFT JOIN it selects all "versions", as it should, but the sum is for all rows in table InvLine for that particular version, it doesn't care about the date. If I change it to INNER JOIN it gets the sum right but only selects "versions" with rows in table InvLine.

So I would like it to select all versions and for a specific date in table Invoice. It's a Progress database.

Thanks for any help!

Since you are not selecting anything from that table and it is an outer join, meaning it does not limit any rows being returned, why not just remove it from the statement?

Dave Nance

I want to select all items and left join table invLines, but I also need to get the invoice date from table Invoice.

Did my question not pass the eligible-question-threshold or what? I still need some help on this one... Thank you!

so its probably best to ask in the Postgres forum

Excuse me, what?

Did you think about how incredibly illiterate I'd have to be to write "Progress" instead of "Postgres". There is such a thing as a Progress database, you know...

I think dyslexic is the word you're looking for, but the mistake is understandable, considering the circumstances. Either way, I'm sure healdem spent considerable time thinking about it, and then obviously went out of his way to personally insult you, even going so far as to move your post into a forum he thought might give you better help, thus cleverly baiting you into using another incorrect word. You handled it quite well, imo, especially considering we're all being compensated for taking time to provide you assistance.

Actually, I think Dave already tried to help you, which you categorically rejected, seemingly without even giving a moment's thought to what he was saying.

You keep saying this, yet as was already pointed out, the invoice date does not appear anywhere in your SELECT statement. So, either you're missing something in your SELECT statement, or you're needlessly joining on the Invoice table, or you don't understand what you're trying to do and your statement is completely wrong. Unfortunately, nobody else understands what you want either. Perhaps showing us some desired, sample output would enlighten us on what you're trying to accomplish.

Where's the like button for futurity's post?

It sounds like there may be either an:
inl.date = inv.date
or inl.version = inv.version
missing from your inv join clause.

Sorry, I didn't want to come of as ungrateful or anything, I'm really thankful for all the help I'm getting here. And, yeah, dyslexic would have been a better word in that context...

I'll try to explain a little better. Here's the basic structure:

tblINVOICE
InvoiceNumber, InvoiceDate

tblINVOICELINE
InvoiceNumber, Item, Version, Qty

tblITEM
Item, ItemName, ItemGrp

tblITEMGRP
ItemGrp

There's one row for one invoice. One invoice has several invoice lines. I want to select all items in an item group, and get the quantities from the invoice lines, if there is any, but only between certain dates which are found in tblInvoice, which is linked to tblInvoiceLine with InvoiceNumber. tblInvoiceLine does not hold any dates. I don't want to select the dates from tblInvoice but I want to limit the invoice lines by them.

Thanks again!

You'll have to supply the appropriate start and end dates.

Nice, dude! That did it! Thank you very much, futurity!

my apologies, some times on a drive by moderation you see things that aren't in the original post. I just presumed it was a typo...