Question

SQL Syntax Error on PDT Create: ORA-00907: missing right parenthesis

  • 2 August 2019
  • 3 replies
  • 165 views

I am getting the following error for a PDT I created. It is confusing me because I am using a sql_trigger that works for multiple PDTs I have created previously. Any suggestions as to were a missing right Parenthesis could be? Both SQL queries run successfully in SQL developer.


derived_table aggregated_fulfillment_ty creation failed: SQL Error in CREATE TABLE: java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis


derived_table: {
sql:
Select distinct
f.fiscal_year,
f.fiscal_day,
olf.actual_ship_date_key as ship_date_key,
s.ifmx_store_nbr as store_nbr,
sum(olf.product_amt - olf.discount_amt) as revenue_amt,
sum(olf.order_qty) as Units,
sum(olf.product_amt - olf.discount_amt - olf.avg_cost_amt) as Gross_Margin_amt
from finldm.order_line_fct olf
join finldm.store_dm s on olf.FULFILL_STORE_KEY = s.STORE_KEY
join FINLDM.FISCAL_CALENDAR_DM f on olf.actual_ship_date_key = f.date_key
join finldm.status_dm s on olf.status_key = s.status_key
where f.fiscal_year = 2020
and s.status_cd = '850'
and olf.ORDER_LINE_NBR <> -1
group by
olf.actual_ship_date_key,
s.ifmx_store_nbr,
olf.status_key,
f.fiscal_year,
f.fiscal_day
order by
s.ifmx_store_nbr ;;

sql_trigger_value:
select max(ORDER_LINE_KEY) from finldm.order_line_fct where trunc(order_dt) = (trunc(SYSDATE)-1) ;;

}

3 replies

Userlevel 3

@Shawn_Curry Could you open an explore that would use this PDT and paste the output of the SQL tab?


From what I can see those SQL queries look fine with no abandoned left parenthesis.

@zckymc Hey I figured out the issue with previous problem. But I have encountered a similar problem. The PDT query works fine but when I use it in in an explore I am getting the same syntax error. Below is the screen shot of the issue and SQL output below.



– use existing web_inventory_file in Looker.LR$38MZ9P20H7D0FA255MB8E_web_i

– use existing web_inventory_file_recent_ind in Looker.LR$38MFF3BAYVT3JRKU2SKOB_web_i

WITH web_inventory_file_bsf_ind AS (SELECT

a.style_color,

case

when a.sales_amt > 0 then 1

else 0

END As sales_indicator

from

(SELECT

b.transaction_date_key,

p.ifmx_style_id || p.ifmx_color_id AS style_color,

sum(b.product_amt) as sales_amt

FROM FINLDM.BOOKED_SALES_FCT b

JOIN FINLDM.PRODUCT_DM p on p.product_key = b.product_key

where b.ifmx_store_nbr in ( 499, 1499)

and b.transaction_date_key >= 20180201

group by

b.transaction_date_key,

p.ifmx_style_id || p.ifmx_color_id

) a )

SELECT * FROM (SELECT

(web_inventory_product.IFMX_STYLE_ID||’ ‘||web_inventory_product.IFMX_COLOR_ID) AS “web_inventory.style_color”,

Case

when (web_inventory_file_recent_ind.recent_inventory_ind = 1 AND web_inventory_file_bsf_ind.sales_indicator = 0)

then “New Arrival” else “No”

END AS “web_inventory.new_arrival_ind”

FROM Looker.LR$38MZ9P20H7D0FA255MB8E_web_i web_inventory

LEFT JOIN FINLDM.PRODUCT_DM web_inventory_product ON web_inventory.product_key = web_inventory_product.PRODUCT_KEY

(web_inventory_product.IFMX_STYLE_ID||’ '||web_inventory_product.IFMX_COLOR_ID) = web_inventory_file_recent_ind.style_color

LEFT JOIN web_inventory_file_bsf_ind ON = web_inventory_file_bsf_ind.STYLE_COLOR = web_inventory_file_recent_ind.style_color


WHERE

((Case

when (web_inventory_file_recent_ind.recent_inventory_ind = 1 AND web_inventory_file_bsf_ind.sales_indicator = 0)

then “New Arrival” else “No”

END) = ‘New Arrival’)

GROUP BY (web_inventory_product.IFMX_STYLE_ID||’ '||web_inventory_product.IFMX_COLOR_ID),Case

when (web_inventory_file_recent_ind.recent_inventory_ind = 1 AND web_inventory_file_bsf_ind.sales_indicator = 0)

then “New Arrival” else “No”

END

ORDER BY 1 ) WHERE ROWNUM <= 500

Userlevel 3

I’m not sure if it’s just how what you’ve pasted has been formatted here, but it looks like this empty string between double pipes is causing issues. Full line is

SELECT * FROM (SELECT (web_inventory_product.IFMX_STYLE_ID||’ ‘||web_inventory_product.IFMX_COLOR_ID) AS “web_inventory.style_color”,

Reply