We help IT Professionals succeed at work.

New at Teradata Queries - getting an error Missing Right Parenthesis also not sure if I want an outer join or inner join

gdunn59
gdunn59 asked
on
I'm new at creating queries in Teradata.  The query I created is giving me an error that there is a right parenthesis missing.  Also, I'm not quite sure if I want an outer join or inner join.

select DISTINCT
facets.CMC_PRPR_PROV.PRPR_ID,
facets.CMC_PRPR_PROV.PRPR_NAME, 
facets.CMC_CLCL_Claim.PRPR_ID
FROM           
	{ oj FACETS.CMC_CLCL_CLAIM 
	LEFT OUTER JOIN
	  FACETS.CMC_PRPR_PROV ON FACETS.CMC_CLCL_CLAIM.PRPR_ID = FACETS.CMC_PRPR_PROV.PRPR_ID }
where FACETS.CMMC_PRPR_PROV.PRPR_ID = '0000000'

Open in new window

Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Not a Teradata person but everything I can find on the SQL, it is similar to any other SQL.

What do the braces "{}" and "oj" do?  I cannot find anything with that syntax online.
Information Technology Specialist
Commented:
I can only assume that the braces pertained to some other dbms, since you say your are new to Teradata, and are not a part of Teradata syntax. I also have no concept of the usage of "oj". Typically, your Teradata query would look more like this (note use of aliases to reduce typing) -
select distinct p.prpr_id as prpr_id, p.prpr_name, c.prpr_id as clcl_id
from FACETS.CMC_CLCL_CLAIM c [left outer/inner] join FACETS.CMC_PRPR_PROV p
on c.prpr_id = p.prpr_id
where p.prpr_id = '000000'
I'm not sure what you're trying to accomplish with this query. An inner join would only retrieve records where both ids equal '000000'. A left outer join would retrieve all of the clcl_ids and all of the prpr_ids and prpr_names would be null except for those having a matching value of '000000' in the prpr_ids of each table. Perhaps you could provide some sample data for both tables and what you expect as output.