Hi Guys,
I have a problem that is driving me nuts!
I have 2 tables holding details of stock movements, referred to as
Transactions, TransHeader and TransDetails. TransHeader holds details of
the transaction type, TransType, which can be Goods In (GI), Transfer (TF)
or Goods Out (GO). There are 2 tables, Suppliers and Locations each with an
integer ID field and a name. TransHeader has 4 integer fields to hold
FromSuppID, FromLocnID, ToLocnID and ToSuppID. If the TransType is GI the
FromSuppID and ToLocnID are populated with values and the others are zeroes,
if it is TF the SuppID fields are zero with values in the others, etc. I
need to populate a cursor with details of TransDate, TransType,
FromSuppName, FromLocnName, ToLocnName, ToSuppName for each record in
TransDetails where the stockID has a particular value. I have used the View
generator to create an SQL statement which I have modified by adding
conditionals to test for zeroes as shown below:
SELECT Transheader.transdate, Transheader.transtype,;
IIF(EMPTY(Transheader.fromsuppid), SPACE(50), Suppliers.suppname) as
FromSuppName, ;
IIF(EMPTY(Transheader.fromlocnid), SPACE(50), Locations.locnname) as
FromLocnName, ;
IIF(EMPTY(Transheader.tolocnid), SPACE(50), Locations_a.locnname) as
ToLocnName, ;
IIF(EMPTY(Transheader.tosuppid), SPACE(50), Suppliers_a.suppname) as
ToSuppName, ;
Transdetails.qty;
FROM ;
kactstock!transheader ;
INNER JOIN kactstock!transdetails ;
ON Transheader.transid = Transdetails.transid ;
INNER JOIN kactstock!suppliers Suppliers_a ;
ON Transheader.tosuppid = Suppliers_a.suppid ;
INNER JOIN kactstock!locations Locations_a ;
ON Transheader.tolocnid = Locations_a.locnid ;
INNER JOIN kactstock!locations ;
ON Transheader.fromlocnid = Locations.locnid ;
INNER JOIN kactstock!suppliers ;
ON Transheader.fromsuppid = Suppliers.suppid ;
ORDER BY TransHeader.Transdate descending ;
WHERE transdetails.stockid = 578
It works perfectly if all of the ID fields are positive but if any are zero
there is no result despite the IIF statements.
I'm sure it is something very simple that I cannot see but I'm baffled.
Help!!!
TIA
John Weller
01380 723235
07976 393631
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
