Problem Pulling Data from Aliased Tables

[byrne1]

I am using CR Dev 10 on a Pervasive SQL 8.0 database and I am having problems pulling data from tables using aliases. I want to pull data from the ITEMS table and subsequent VENDOR and ITMDESCR tables based on a match on the ItemNum field. If there is no match, then I want to use the ITEMCREF table for a match on the AltNum field, then use this value to pull data from the ITEMS table and subsequent tables based on a match on the ItemNum field (see attempt to show this below). Please note the formula at the end of this post. When I comment a section out everything works fine (but only if the ITEMCREF table doesn't have to be used).

QUERY
-----
TicketNum --> TICKHISH.TicketNum

TICKHISH
--------
TicketNum --> TICKHISD.TicketNum
Batchnum --> TICKHISD.BatchNum

TICKHISD
--------
ItemNum --> ITEM.ItemNum *OR* ITEMCREF.AltNum

ITEMCREF
--------
ItemNum --> ITEM.ItemNum

ITEM
----
ItemNum --> ITMCREF.ItemNum
VendorNum --> VENDOR.VendorNum


I've tried using LEFT OUTER JOINS on the ITEM and ITEMCREF tables but this doesn't seem to work (see SQL statement below). Can anyone help me?

SELECT "TICKHISH"."TicketNum", "ITMDESCR"."Description1", "VENDOR"."Name", "ITMDESCR"."Description2", "TICKHISH"."EventMode", "TICKHISH"."VoidStatus", "TICKHISD"."VoidFlag", "TICKHISD"."DepartmentNum", "TICKHISD"."ItemDescription", "TICKHISD"."QtySold"
FROM {oj ((("TICKHISH" "TICKHISH" INNER JOIN "TICKHISD" "TICKHISD" ON ("TICKHISH"."TicketNum"="TICKHISD"."TicketNum") AND ("TICKHISH"."BatchNum"="TICKHISD"."BatchNum")) LEFT OUTER JOIN "ITEM" "ITEM" ON "TICKHISD"."ItemNum"="ITEM"."ItemNum") LEFT OUTER JOIN "ITMDESCR" "ITMDESCR" ON "ITEM"."ItemNum"="ITMDESCR"."ItemNum") LEFT OUTER JOIN "VENDOR" "VENDOR" ON "ITEM"."VendorNum"="VENDOR"."VendorNum"}
WHERE "TICKHISH"."VoidStatus"='' AND "TICKHISD"."VoidFlag"='' AND "TICKHISH"."TicketNum"=344273 AND "TICKHISD"."DepartmentNum"<70 AND ("TICKHISH"."EventMode"='L' OR "TICKHISH"."EventMode"='U')
ORDER BY "TICKHISH"."TicketNum"


*** This works if comments are left in, but breaks when comments are removed ***
//if not isnull({ITEM.ItemNum}) then
if isnull({ITMDESCR.Description1}) or {ITMDESCR.Description1}= " " then
if {TICKHISD.QtySold}>1 then
propercase(Towords ({TICKHISD.QtySold},0)+" "+ trimright({TICKHISD.ItemDescription}))+"s"
else
"One "+propercase(trimright({TICKHISD.ItemDescription}))
else
if {TICKHISD.QtySold}>1 then
propercase(Towords ({TICKHISD.QtySold},0)+" "+trimright({ITMDESCR.Description2}))+"s"
else
"One "+propercase(trimright({ITMDESCR.Description2}))
//else
// if isnull({ITMDESCR_1.Description1}) or {ITMDESCR_1.Description1}= " " then
// if {TICKHISD.QtySold}>1 then
// propercase(Towords ({TICKHISD.QtySold},0)+" "+ trimright({TICKHISD.ItemDescription}))+"s"
// else
// "One "+propercase(trimright({TICKHISD.ItemDescription}))
// else
// if {TICKHISD.QtySold}>1 then
// propercase(Towords ({TICKHISD.QtySold},0)+" "+trimright({ITMDESCR_1.Description2}))+"s"
// else
// "One "+propercase(trimright({ITMDESCR_1.Description2}))