Using Same Table Twice

[Cobby1812]

Hi there,

I am trying to create a script that will show all the information I require.

In one table I have a field called RECTYPE - now RECTYPE can be 801, 802, 803, 804 - it may exists sometimes for one user or not for another. In my simple mind I thought of just using a LEFT OUTER JOIN Twice then Aliasing the names i.e

LEFT OUTER JOIN schema.FFS01P0A
ON schema.FFS01P02.CLAFCNO = schema.FFS01P0A.CLAFCNO
AND schema.FFS01P02.CLIENTNO = schema.FFS01P0A.CLIENTNO
AND schema.FFS01P02.BUSINESS = schema.FFS01P0A.CLTYPE1
AND schema.FFS01P02.CLCURCD = schema.FFS01P0A.CLCURCD
AND schema.FFS01P0A.RECTYPE = 801

LEFT OUTER JOIN schema.FFS01P0A
FFS01P0A1
ON schema.FFS01P02.CLAFCNO = schema.FFS01P0A1.CLAFCNO
AND schema.FFS01P02.CLIENTNO = schema.FFS01P0A1.CLIENTNO
AND schema.FFS01P02.BUSINESS = schema.FFS01P0A1.CLTYPE1
AND schema.FFS01P02.CLCURCD = schema.FFS01P0A1.CLCURCD
AND schema.FFS01P0A.RECTYPE = 802

And so on - however i keep getting an error - can someone please tell me the correct syntax for this...or indeed if it can be done.

I am using Run a SQL Script in System i Navigator verion 7

Many thanks

 

[SkipVought]

What error do you get?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Cobby1812]

Hi there, sorry for the delay

ssage: [SQL5001] Column qualifier or table FFS01P0A1 undefined. Cause . . . . . : Name FFS01P0A1 was used to qualify a column name or was specified as the operand of the RRN, RID, HASHED_VALUE, PARTITION, NODENAME, NODENUMBER, DBPARTITIONNAME, DBPARTITIONNUM, DATAPARTITIONNAME, or DATAPARTITIONNUM scalar function, or was specified for the ROW CHANGE TIMESTAMP or ROW CHANGE TOKEN expression. The name is not defined to be a table designator in this SQL statement or the table designator cannot be referenced where it is specified in the SQL statement. The table designator for a VALUES fullselect cannot be used as the operand of any of the functions listed above. If a correlation name is specified following the table name in a FROM clause, the correlation name is considered to be the table designator. If a correlation name is not specified, the table name is considered to be the table designator. If using SQL naming and the table is qualified with authorization name, then the table designator is authorization-name.table-name. If the authorization name is not specified, the table designator is the implicit authorization name followed by the table name. Correlation from a nested table expression to a higher level table is only allowed if the TABLE keyword is used for the definition of the nested table expression. If the name is *N, a lateral correlation reference from a nested table expression is not allowed. You cannot correlate to a table at a higher level than the nested table expression for one of the following reasons: -- The nested table expression contains a UNION, EXCEPT, or INTERSECT. -- The nested table expression uses the DISTINCT keyword in the SELECT clause. -- The nested table expression contains an ORDER BY clause. -- The correlated provider is in the same FROM clause as the nested table expression but is part of a RIGHT OUTER JOIN, RIGHT EXCEPTION JOIN, or FULL OUTER JOIN. -- The nested table expression is in the FROM clause of another nested table expression that contains one of these restrictions. In an OLAP function, the ORDER OF table designator must refer to a table designator in the FROM clause of the subselect. Recovery . . . : Ensure all column names are qualified with a valid table designator. Make sure a table designator is specified as the argument to the function. Use the TABLE keyword to allow correlated columns within a nested table expression. Try the request again.

 

[Cobby1812]

I have resolved now - turned out I didnt put the correct table name in one part.