INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using Same Table Twice

Using Same Table Twice

(OP)
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

RE: Using Same Table Twice

What error do you get?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Using Same Table Twice

(OP)
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.

RE: Using Same Table Twice

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close