missing records from linked table

[phartman]

CR Developer 9.2.2, Oracle 9i, ODBC drivers

I have 2 tables Item Master & transactions, linked (left outer join) by im_key > tx_imkey.

I want the report to show all items on the item master that begin with I114 and have catalog code 114. In addition, I want all trans for these items between date 9/1/04 and 9/30/04.

This is the SQL Query generated by CR
SELECT "IM"."IM_KEY", "IM"."IM_DESCR", "IM"."IM_UNIT_S", "IM"."IM_CATALOG", "IM"."IM_ON_HAND", "TX"."TX_DATE", "TX"."TX_QTY", "TX"."TX_ORIGIN"
FROM "PK1"."IM" "IM", "PK1"."TX" "TX"
WHERE ("IM"."IM_KEY"="TX"."TX_IMKEY" (+)) AND "IM"."IM_KEY" LIKE 'I114%' AND "IM"."IM_CATALOG"='114'
ORDER BY "IM"."IM_KEY"

and this is the record selection formula generated by Cr, except for the isnull line I added.
{IM.IM_KEY} startswith "I114" and
{IM.IM_CATALOG} = "114" and
(isnull({@trandate})or
{@trandate} = {?DateRange})

I am getting all items that have no trans at all, items and trans that have trans in 09/04, but I do not get the items with only trans prior to 9/01/04. What am I doing wrong?

 

[synapsevampire]

For better results, try posting:

Example data
Expected output

You state: "but I do not get the items with only trans prior to 9/01/04"

And you state: "I want the report to show...In addition, I want all trans for these items between date 9/1/04 and 9/30/04."

Which is it?

Your record selection formula shows:

(isnull({@trandate})or
{@trandate} = {?DateRange})

This indicates that you want those without a transaction, or those with transactions within a given date range.

Please clarify.

Again, post example data and the expected output, not descriptives.

-k

 

[phartman]

Sorry, I'll try to give some examples.
IM TABLE DATA
IM_Key IM-OHBALANCE

1234 1
1345 5
1456 9
1567 3

TX TABLE DATA
TX_IMKEY TX-DATE TX-QTY
1234 07/01/04 3
1234 08/01/04 1
1345 09/01/04 2
1456 06/05/04 4
1456 09/05/04 6
1456 09/15/04 2
1567 (no trans)

The results I would like is:
IM_KEY TX_date Qty Balance
1234 1

1345 09/01/04 2
1345 5

1456 09/05/04 6
1456 09/15/04 2
1456 9

1567 3

I want the ending on hand balance of all items, plus all current month transactions. Right now the report is not showing item 1234 ( any item with trans dated less than 9/01/04. Hope this helps.

Thanks for your help!

 

[synapsevampire]

Since the current month is October, I'll assume that what you mean is the designated month (parameter).

A Left Outer Join from the IM to TX table, with the following record selection formula seems fine:

(
isnull({tx.tx-date})
or
{tx-tx-date} = {?DateRange}
)
and
(
{IM.IM_KEY} startswith "I114"
)
and
(
{IM.IM_CATALOG} = "114" and
)

Since you didn't share the key formula {@trandate}, I can't advise on that, but I would omit it. If it's converting a field to a date type, I would suggest doing that in the SQL Expression and referencing that field, or converting the date parameter field to the field type and use the converted parameter value.

Hope this resolves.

-k

 

[lbass]

I think you have to remove the selection relating to the tx table from your record selection formula. Then create a formula like the following to replace your {tx.tx-date} field:

if isnull({tx.tx-date}) or
not({tx.tx-date} in {?DateRange}) then
date(0,0,0) else
{tx.tx-date}

Replace {tx.tx-qty} with:

if isnull({tx.tx-date}) or
not({tx.tx-date} in {?DateRange}) then
0 else {tx.tx-qty}

Format this last formula to suppress if zero. You should then be able to suppress duplicate fields to get the display you wish.

-LB

 

[phartman]

You are correct, taking the selection relating to the tx table out of the selection formula and creating the formulas with 0 suppression worked great.

Thanks so much for your help!