Teradata Query Assistance

Can someone assist with this?  I am getting 3706 Syntax error, expected something between ',' and 'LEFT' keyword:

SELECT TOP 500 * 
FROM (SELECT l.CustomerID, l.DateCreated, l.TranType, l.BeforeImage, l.AfterImage, l.Initials, c.LastName 
FROM PROD_STEAKHOUSE_TB.TranLog l
   LEFT OUTER JOIN PROD_STEAKHOUSE_TB.Customer c ON l.CustomerID = c.CustomerID WHERE l.TranType = 'Res Chg' AND c.LastName LIKE 'k%' 
UNION SELECT l.CustomerID, l.Time_Stamp, 'SEC', LEFT(l.ActionContext + N' ' + CASE WHEN l.AuthSuccess = 1 THEN 'Success' 
   ELSE 'Failure' END + N' on ' + l.PhysicalLocation, 255), '', ISNULL(w.WaiterInitials, ''), ISNULL(c.LastName, '') FROM PROD_STEAKHOUSE_TB.SecurityLog AS l 
     LEFT OUTER JOIN PROD_STEAKHOUSE_TB.Waiter w ON l.WaiterID = w.WaiterID 
     LEFT OUTER JOIN PROD_STEAKHOUSE_TB.Customer c UNION ON l.CustomerID = c.CustomerID WHERE  'SEC' = 'Res Chg' AND c.LastName LIKE 'kr%' ) a 
ORDER BY DateCreated DESC;

Open in new window

Cody VanceSr. Analyst - ERPAsked:
Who is Participating?
 
pcelbaConnect With a Mentor Commented:
So the final result could be:

SELECT TOP 500 l.CustomerID, l.DateCreated, l.TranType, l.BeforeImage, l.AfterImage, l.Initials, c.LastName 
  FROM PROD_STEAKHOUSE_TB.TranLog l
  LEFT OUTER JOIN PROD_STEAKHOUSE_TB.Customer c ON l.CustomerID = c.CustomerID
 WHERE l.TranType = 'Res Chg' AND c.LastName LIKE 'k%'
 ORDER BY l.DateCreated DESC;

Open in new window

The removed UNION part was in the command for some future extensions planned.
0
 
pcelbaCommented:
I am not Terradata expert but what means  " UNION ON " inside the last LEFT JOIN ?

Also 'SEC' = 'Res Chg'  cannot evaluate to True so the whole command can be reduced to
SELECT TOP 500 * 
FROM (SELECT l.CustomerID, l.DateCreated, l.TranType, l.BeforeImage, l.AfterImage, l.Initials, c.LastName 
FROM PROD_STEAKHOUSE_TB.TranLog l
   LEFT OUTER JOIN PROD_STEAKHOUSE_TB.Customer c ON l.CustomerID = c.CustomerID WHERE l.TranType = 'Res Chg' AND c.LastName LIKE 'k%') a 
ORDER BY DateCreated DESC;

Open in new window

0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
TO be honest, someone else created this and is no longer available to troubleshoot..
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
magarityConnect With a Mentor Commented:
I think the problem is the UNION ON in the join should just be ON.

The 'SEC' = 'Res Chg' will work - that's Teradata's column aliasing at work in the third element of the UNION statement on line 5.
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Thanks that worked
0
 
pcelbaCommented:
The 'SEC' = 'Res Chg' will work ??

Maybe yes... If the 'SEC' is handled as quoted column name and 'Res Chg' is either quoted column name or string constant. I don't know whether Teradata handles quoted identifiers this mixed way...

I know T-SQL and some MySQL and 'SEC' means a character string constant obviously. It should result into constant value in the third column but quoted column name is also an explanation.

The more exact answer could be possible if we would know your data model.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.