I have a hierarchy table dept-subdept-class and joind those 3 tables. I would like to get the result where the subdept hier_type is 0 but when I put this in the where clause, it's not giving me any result back.

JOIN
 CLASS.CLASS.SUBDEPT_ID IN CLASS TO UNIQUE
 SUBDEPARTMENT.SUBDEPARTMENT.SUBDEPT_ID IN SUBDEPARTMENT AS J0
 END
JOIN
 SUBDEPARTMENT.SUBDEPARTMENT.DEPT_ID IN CLASS TO UNIQUE
 DEPARTMENT.DEPARTMENT.DEPT_ID IN DEPARTMENT AS J1
 END
TABLE FILE CLASS
BY DEPT_ID
BY DEPT_DESCR
BY 'DEPARTMENT.DEPARTMENT.HIER_TYPE' AS 'DEPT_HIER_TYPE'
BY SUBDEPT_ID
BY SUBDEPT_DESCR
BY 'SUBDEPARTMENT.SUBDEPARTMENT.HIER_TYPE' AS 'SUBDEPT_HIER_TYPE'
BY CLASS_ID
BY CLASS_DESCR
BY 'CLASS.CLASS.HIER_TYPE' AS 'CLASS_HIER_TYPE'
WHERE DEPT_ID EQ 40;
WHERE 'SUBDEPARTMENT.SUBDEPARTMENT.HIER_TYPE' EQ '0';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END

I looked at the table and i can see subdept hier_type = 0 for dept 40. Is there something wrong with my join?

QUERY:
select a.dept_id, c.hier_type, a.subdept_id, b.hier_type, a.class_id, a.hier_type
from class a, subdepartment b, department c
where a.subdept_id = b.subdept_id
and b.dept_id = c.dept_id
and a.dept_id = 40
and b.hier_type = '0'

Result:
dept_id,hier_type,subdept_id,hier_type,class_id,hier_type
40,'1',40998,'0',40998998,'0'


Thanks for the help!

Joel

This message has been edited. Last edited by: Joel,

quote:

WHERE 'SUBDEPARTMENT.SUBDEPARTMENT.HIER_TYPE' EQ '0';

"SUBDEPARTMENT.SUBDEPARTMENT.HIER_TYPE" will never be equal to "0".

Why are your column names in quotes?

Based on the query that I ran, there are records where the subdepartment's hier_type is "0", so I don't understand why in webfocus it's not giving me any result.

I have to put specify
'DEPARTMENT.DEPARTMENT.HIER_TYPE'
'SUBBDEPARTMENT.SUBDEPARTMENT.HIER_TYPE'
'CLASS.CLASS.HIER_TYPE'
since when I just put HIER_TYPE it's just geting the class hier_type only (which I'm not trying to use the filter on).

quote:

"SUBDEPARTMENT.SUBDEPARTMENT.HIER_TYPE" will never be equal to "0".

Why are your column names in quotes?

Like I said, since you have the filter column name in quotes, the value "SUBDEPARTMENT.SUBDEPARTMENT.HIER_TYPE" will never be equal to the value "0". Remove the quotes around the column names.

This will return results:

TABLE FILE CAR
SUM CAR.BODY.SALES
BY CAR.ORIGIN.COUNTRY
WHERE CAR.CARREC.MODEL CONTAINS 'DOOR'
END

This will not return results, because "CAR.CARREC.MODEL" does not contain "DOOR":

TABLE FILE CAR
SUM 'CAR.BODY.SALES'
BY 'CAR.ORIGIN.COUNTRY'
WHERE 'CAR.CARREC.MODEL' CONTAINS 'DOOR'
END

quote:

I have to put specify
'DEPARTMENT.DEPARTMENT.HIER_TYPE'
'SUBBDEPARTMENT.SUBDEPARTMENT.HIER_TYPE'
'CLASS.CLASS.HIER_TYPE'

Not true if you use TAG syntax -

JOIN SUBDEPT_ID IN CLASS TAG T1 TO UNIQUE SUBDEPT_ID IN SUBDEPARTMENT TAG T2 AS J0
JOIN T2.DEPT_ID IN CLASS        TO UNIQUE DEPT_ID    IN DEPARTMENT    TAG T3 AS J1
TABLE FILE CLASS
BY DEPT_ID
BY DEPT_DESCR
BY T2.HIER_TYPE AS DEPT_HIER_TYPE
etc. ....

T

Thanks Francis and Tony. I've learned another trick ... I haven't used TAGs before and I was taught to enclose use the whole columns with " " for the field you want to be displayed if you have the same field names on the tables.

You also might want to check out your JOINs. Start simple. What I see is an inner, one to one join, meaning that if your join doesn't continue to the bottom of your tree (all joins valid) the records are eliminated. That might be stating the obvious, but when something doesn't work, you start with the obvious.