Hey,
I have a question about creating subquery in SQL. My current query looks like this:
select
tdsls002.item, | Item
tdsls002.oqua, | Ordered Quantity
tdsls002.ddta, | Planned Delivery Date
tiitm001.stoc | Inventory on Hand
from
tdsls002, |
tiitm001, | Items
tiitm100 |
where
tdsls002.item >= tdsls002.item.f and
tdsls002.item <= tdsls002.item.t and
tdsls002.item refers to tiitm100 and
tiitm100.item refers to tiitm001 and
tdsls002.sorn = 0
order by
tdsls002.item,
tdsls002.ddta
I takes sales quotations and looks for inv on hand. How could I do query in a way that I could get a report like this:

Item 1
sq line 1
sq line 2
sq line 3
.....

Item 2
sq line 1
sq line 2
sq line 3
.....

.....

Instead of what i have now:
Item 1 - sq line 1 - on hand....
Item 1 - sq line 2 - on hand....
......
Item 2 - sq line 1 - on hand....
Item 2 - sq line 2 - on hand....
.......

I think it's not a matter of sql.
It's a matter of arranging the data on the report.
Introduce a before.field section in the layout of your report.
Am I right?

by the way:
I think table tiitm100 should not be part of the 'from' part of the sql query

I think it's not a matter of sql.
It's a matter of arranging the data on the report.
Introduce a before.field section in the layout of your report.
Am I right?

by the way:
I think table tiitm100 should not be part of the 'from' part of the sql query

Thanks for quick answer, I was not aware of before.field section possibility until you told so. I will try it out.

tiitm100 is some intermediate table in our Baan because of EDI link i suppose.

Works just as the way I wanted.

Big thanks Evert-Jan!!! :)