Select Statement to include field from left outer table

[kristal9]

Is there a way to do this without doing a subreport?

I have two tables Orders & Order_Details

Some orders don't have details. Some do. Some of the order details I want to see in my report some i do not.


I want a group by Order then in the detail show the order details.

I've joined by left outer join to get the details. However,
I only want to show order details where [OrderDetail.Type]=1

I've tried to include in my select statement:

(isnull (Orderdetail.Type) Or [OrderDetail.Type]=1)

this returns only orders that have [Order_Detail.Type]=1

If there is a row of data for order detail, that type can only be 1 of 2 values. 0 or 1.

I can conditionally supress the detail if [Order_Detail.Type]<>1

but because the number of records returned is considerably higher this way than only those I really want, i'd wanted to see if anyone might have a cleaner solution.

ps the select statement above is a simplification the actual statement is

{ASSOCIATIONS.TABLE} = "ORDERS" and
{@Level 3} = "TEAM 32" and
{Order.Status} = 1.00 and
({OrderDetail.TYPE} = 1.00
or isnull({OrderDetail.Type}))

where the formula @level 3 is

select {TeamTable_4.DEPTH}
case 1: {TeamTable_4.NAME}
case 2: {TeamTable_4.NAME}
case 3: {TeamTable_4.NAME}
case 4: {TeamTable_3.NAME}
// this just brings back the Team 3 Name regardless of whether Team 3 is the lowest level of team hierarchy or next to lowest.

oh.. CR XI and SQL Server db.

 

[kristal9]

FYI... I changed my select statement to this

{ASSOCIATIONS.TABLE} = "ORDERS" and
{@Level 3} = "TEAM 32" and
{Order.Status} = 1.00 and
(if isnull({OrderDetail.Type}) then true else
{OrderDetail.TYPE} = 1.00)

which seemed to help but not quite.

for example say I have 100 Order Records. and I want to show all 100 records. Of those only 5 have OrderDetail.Type = 1. When I changed my record selection for the if / then it would bring back 8 Order records. Not the 100 I want.

So if 3 are null, what are the other 92? if there is no record in the detail table? What could it be beside NULL?
%-)

 

[lbass]

Answer: Other detail types.

A speedy way of handling this is to use a command as your sole datasource for the report, where you build the selection criteria into the joins in the from clause instead of using the where clause. For example,

From (Order
left outer join OrderDetail on
Order.OrderNo = OrderDetail.OrderNo and
Order.Status = 1 and
(
OrderDetail.Type is null or
OrderDetail.Type = 1
)
) //etc.

-LB

 

[kristal9]

thanks LBass.

I will try the command tomorrow.

but to clarify, when you wrote
"Answer: Other detail types. "

I'm not sure i understood. The detail type can only be a 0 or 1. I only want to return the ones with 1 or the order if it doesn't have details and regardless of whether i would write:

({OrderDetail.TYPE} = 1.00
or isnull({OrderDetail.Type})

or:


({OrderDetail.TYPE} <> 0
or isnull({OrderDetail.Type})

the result was the same.

 

[lbass]

The answer is then:

Records where OrderDetail.type = 0

The null check must always be first as in:

{ASSOCIATIONS.TABLE} = "ORDERS" and
{@Level 3} = "TEAM 32" and
{Order.Status} = 1 and
(
isnull({OrderDetail.Type}) or
{OrderDetail.TYPE} = 1
)

But, this still will not return all records. So you need to either use the command, or remove the selection criteria on the orderdetail table and instead use conditional formulas to return the results you do want to display in the report.

-LB