Toad World® Forums

With Param SQL


#1

I have a large query that runs fine in SQL*Plus and in TOAD. But in SQLNav, it give the error:
[1]: ORA-00907: missing right parenthesis

But, I am able to Format it in SQLNav without any issues.

Query:

SELECT *
FROM (WITH param
AS (SELECT TRUNC(TO_DATE(‘29-DEC-2012’)) as_of_date,
‘Bi-Week’ payroll_period_type
FROM DUAL),
std_bal_date AS (SELECT CAST(‘06-JAN-2001’ AS DATE) std_begin_date FROM DUAL),
std_supp_pay
AS (SELECT bal.assignment_id,
bal.period_type,
bal.val
FROM param
CROSS JOIN uhhs.uh_pay_balance_data bal
JOIN pay_defined_balances pdb
ON (bal.as_of_date = TRUNC(param.as_of_date)
AND bal.defined_balance_id = pdb.defined_balance_id)
JOIN pay_balance_types pbt
ON (pdb.balance_type_id = pbt.balance_type_id
AND pbt.balance_name = ‘UH STD Supp Pay Custom Balance’)
JOIN pay_balance_dimensions dim
ON (pdb.balance_dimension_id = dim.balance_dimension_id
AND dim.database_item_suffix = ‘_ASG_PTD’))
SELECT paf.assignment_id,
hou.name org_name,
REGEXP_SUBSTR(hou.name, ‘[[:digit:]]{5}$’) org_number,
gre_org.name entity,
ppf.full_name,
ppf.date_of_birth,
TO_DATE(REGEXP_SUBSTR(ppf.attribute1, ‘.{1,10}’), ‘YYYY/MM/DD’) seniority_date,
EXTRACT(YEAR FROM NUMTOYMINTERVAL(MONTHS_BETWEEN(TRUNC(param.as_of_date), TO_DATE(REGEXP_SUBSTR(ppf.attribute1, ‘.{1,10}’), ‘YYYY/MM/DD’)), ‘MONTH’))
|| ’ Years ’
|| EXTRACT(MONTH FROM NUMTOYMINTERVAL(MONTHS_BETWEEN(TRUNC(param.as_of_date), TO_DATE(REGEXP_SUBSTR(ppf.attribute1, ‘.{1,10}’), ‘YYYY/MM/DD’)), ‘MONTH’))
|| ’ Months’
AS current_length_of_service,
paf.assignment_number,
past.user_status,
pay.payroll_name,
pay.period_type,
ppd.segment1 position_name,
COALESCE(CAST(paf.ass_attribute10 AS NUMERIC(8, 2)), 0) sold_pto_dollars_lumpsum,
COALESCE(CAST(paf.ass_attribute10 AS NUMERIC(8, 2)), 0) * COALESCE(pcaf.proportion, 1) sold_pto_dol_lumpsum_split,
COALESCE(CAST(paf.ass_attribute4 AS NUMERIC(8, 2)), 0) std_balance,
COALESCE(CAST(paf.ass_attribute4 AS NUMERIC(8, 2)), 0) * COALESCE(pcaf.proportion, 1) std_balance_split,
COALESCE(CAST(paf.ass_attribute5 AS NUMERIC(8, 2)), 0) pto_sold_value,
COALESCE(CAST(paf.ass_attribute9 AS NUMERIC(8, 2)), 0) frozen_saved_vac_hrs,
COALESCE(CAST(paf.ass_attribute8 AS NUMERIC(8, 2)), 0) frozen_saved_vac_rate,
COALESCE(CAST(paf.ass_attribute7 AS NUMERIC(8, 2)), 0) frozen_saved_vac_balance,
COALESCE(CAST(paf.ass_attribute12 AS NUMERIC(8, 2)), 0) curr_saved_vac_hrs,
COALESCE(CAST(paf.ass_attribute13 AS NUMERIC(8, 2)), 0) curr_saved_vac_rate,
COALESCE(CAST(paf.ass_attribute14 AS NUMERIC(8, 2)), 0) curr_saved_vac_balance,
COALESCE(CAST(paf.ass_attribute14 AS NUMERIC(8, 2)), 0) * COALESCE(pcaf.proportion, 1) curr_saved_vac_balance_split,
EXTRACT(YEAR FROM NUMTOYMINTERVAL(MONTHS_BETWEEN(TRUNC(CASE
WHEN gre_org.name IN (‘UHHS Geauga Regional Hospital’, ‘UHHS Heather Hill Hospital’) THEN std_bal_date.std_begin_date
ELSE param.as_of_date
END),
TO_DATE(REGEXP_SUBSTR(ppf.attribute1, ‘.{1,10}’), ‘YYYY/MM/DD’)),
‘MONTH’))
std_factor_years,
(COALESCE
(
(SELECT CAST(pci.VALUE AS NUMERIC(8, 2))
FROM pay_user_tables put
JOIN pay_user_rows_f pur
ON (put.user_table_id = pur.user_table_id
AND TRUNC(CURRENT_DATE) BETWEEN pur.effective_start_date AND pur.effective_end_date)
JOIN pay_user_column_instances_f pci
ON (pur.user_row_id = pci.user_row_id
AND TRUNC(CURRENT_DATE) BETWEEN pci.effective_start_date AND pci.effective_end_date)
JOIN pay_user_columns puc ON (pci.user_column_id = puc.user_column_id)
WHERE put.user_table_name = ‘UH Term PTO Payout Percent’
AND puc.user_column_name = gre_org.name
AND EXTRACT
(
YEAR FROM NUMTOYMINTERVAL
(
MONTHS_BETWEEN(TRUNC(CASE
WHEN gre_org.name IN (‘UHHS Geauga Regional Hospital’, ‘UHHS Heather Hill Hospital’) THEN std_bal_date.std_begin_date
ELSE param.as_of_date
END),
TO_DATE(REGEXP_SUBSTR(ppf.attribute1, ‘.{1,10}’), ‘YYYY/MM/DD’)),
‘MONTH’
)
) BETWEEN pur.row_low_range_or_name
AND pur.row_high_range),
0
))
std_factor,
paf.effective_start_date assign_effective_start_date,
pcak.concatenated_segments,
COALESCE(pcaf.proportion, 1) proportion,
COALESCE(CAST(COALESCE(ssp.val, 0) AS NUMERIC(8, 2)), 0) std_supp_ptd,
COALESCE(CAST(COALESCE(ssp.val, 0) AS NUMERIC(8, 2)), 0) * COALESCE(pcaf.proportion, 1) std_supp_ptd_split,
param.as_of_date,
ps.actual_termination_date
FROM param
CROSS JOIN std_bal_date
CROSS JOIN apps.per_all_people_f ppf
INNER JOIN per_all_assignments_f paf
ON (paf.person_id = ppf.person_id
AND paf.assignment_type = ‘E’
AND TRUNC(param.as_of_date) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND TRUNC(param.as_of_date) BETWEEN paf.effective_start_date AND paf.effective_end_date)
JOIN per_assignment_status_types past
ON (paf.assignment_status_type_id = past.assignment_status_type_id)
JOIN apps.hr_all_organization_units hou
ON (hou.organization_id = paf.organization_id)
LEFT OUTER JOIN apps.hr_all_positions_f pos
ON (pos.position_id = paf.position_id
AND TRUNC(param.as_of_date) BETWEEN pos.effective_start_date AND pos.effective_end_date)
LEFT OUTER JOIN apps.per_position_definitions ppd
ON (pos.position_definition_id = ppd.position_definition_id)
LEFT OUTER JOIN apps.hr_soft_coding_keyflex hsck
ON (hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id)
LEFT OUTER JOIN apps.hr_all_organization_units gre_org
ON (hsck.segment1 = gre_org.organization_id)
LEFT OUTER JOIN apps.pay_cost_allocations_f pcaf
ON (paf.assignment_id = pcaf.assignment_id
AND TRUNC(param.as_of_date) BETWEEN pcaf.effective_start_date AND pcaf.effective_end_date)
JOIN pay_cost_allocation_keyflex pcak
ON (COALESCE(pcaf.cost_allocation_keyflex_id, hou.cost_allocation_keyflex_id) = pcak.cost_allocation_keyflex_id)
LEFT OUTER JOIN per_periods_of_service ps
ON (ps.period_of_service_id = paf.period_of_service_id)
JOIN pay_payrolls_f pay
ON (paf.payroll_id = pay.payroll_id
AND TRUNC(param.as_of_date) BETWEEN pay.effective_start_date AND pay.effective_end_date
AND pay.period_type = param.payroll_period_type)
LEFT JOIN std_supp_pay ssp
ON (paf.assignment_id = ssp.assignment_id
AND ssp.period_type = pay.period_type))


#2

Hi, I have the same issue in below query:
ORA-00907: missing right parenthesis.
In nav 5.5 it works ok.

select rownum lp, nazwiskoimie, prowizja, agreement_id umowa, package_id umowa_o_przelew
from
(
SELECT nazwisko || ’ ’ || imie nazwiskoimie,
sum(prowizja) prowizja,
agreement_id,
package_id,
grouping_id (nazwisko || ’ ’ || imie, agreement_id, package_id) as lpckg,
grouping_id (nazwisko || ’ ’ || imie) as lempl
FROM ( SELECT
FN imie,
LN nazwisko,
SUM (NVL (comm.commision, 0)) prowizja,
agreement_id,
package_id
FROM (SELECT vcda.user_id,
vcda.firstname FN,
vcda.lastname LN,
vcda.s_user_id,
round(NVL (vcda.rt_calc, 0) * amt_net,2) commision,
vcda.rt_calc,
vcda.s_type,
em.employee_id,
em.firstname,
em.lastname,
trov.amt_net,
agreement_id,
package_id
FROM em_info em
JOIN ( SELECT AGRM.EMPLOYEE_ID,
TERMS.AGREEMENT_ID,
TERMS.PACKAGE_ID,
ROUND (
NVL (terms.AMT_COMM_VAR_NORMAL_BCCY, 0)
+ NVL (terms.AMT_COMM_FXD_BCCY, 0)
/ (1
+ NVL (TERMS.RATE_VAT_COMM_FXD,
0)),
2)
amt_net
FROM mm_package terms
JOIN mm_agreement_empl agrm
ON TERMS.AGREEMENT_ID =
AGRM.AGREEMENT_ID
–WHERE TERMS.DATE_CLOSE >= $P{DATAOD} AND TERMS.DATE_CLOSE
) trov
ON em.employee_id = trov.employee_id
JOIN v_data_access1 vcda
ON em.employee_id = vcda.s_user_id
) comm
JOIN (select distinct user_id, s_user_id from v_data_access) vda
ON comm.user_id = vda.s_user_id
JOIN v_gui_user_roles rl
ON comm.user_id = rl.user_id
WHERE --vda.user_id = $P{USERID} – parametr
–AND
rl.role_code = ‘EMPLOYEE’
GROUP BY
comm.fn,
comm.LN,
agreement_id,
package_id
ORDER BY comm.LN, comm.FN, package_id
)
group by rollup (nazwisko || ’ ’ || imie, package_id, agreement_id )
order by nazwisko || ’ ’ || imie, agreement_id, package_id
)
where (lpckg = 0 and prowizja <> 0 or lpckg = 3)


#3

Heh, I’ve found reason, watch clip!.

Not good, not good…
clip0008.rar (172 KB)


#4

Hi Charlie,

For me, the formatter yields:
10:07:59 Start Format Text …
10:07:59 ERROR line 39, col 38, ending_line 39, ending_col 39,
10:07:59 Found: ‘**’
10:07:59 Invalid identifier: **
10:07:59 ERROR line 39, col 41, ending_line 39, ending_col 41,
10:07:59 Found: ‘_’
10:07:59 Invalid identifier: _
10:07:59 ERROR line 39, col 42, ending_line 39, ending_col 52,
10:07:59 Found: ‘attribute10’
10:07:59 an alias is not allowed here (reserved for XMLCOLATTVAL, XMLFOREST and XMLATTRIBUTES only)
10:07:59 ERROR line 39, col 54, ending_line 39, ending_col 55,
10:07:59 Found: ‘AS’
10:07:59 Expecting: ) ,
10:07:59 End Format Text

and when I try to run it, I get
[1]: ORA-01747: invalid user.table.column, table.column, or column specification

I have no clue how to explain the difference…

Roman


#5

Hi Piter,

I’m getting ‘missing right parenthesis’ both on 7.0 and 5.5. Formatter gives:
10:21:56 ERROR line 45, col 75, ending_line 45, ending_col 75,
10:21:56 Expecting: ) -or- WITH -or- ORDER

So, to me it looks like you have a syntax error here.

By the way, the query in your clip is different. Can you send it too?

Cheers,
Roman


#6

Roman, You are right, pasted query not working. Problem is in query formating, I haven’t noticed that after pasting formating was gone, an some part of query too :).

I’ve attached query with file, check this.

Regards P
query.sql (3.89 KB)


#7

Funny, when I pasted in the query, it automatically changed the word that starts with and “a” and ends with two "s"es. It got changed to ***. That is why your formatting failed. I have attached the query instead. (sorry for the “a ss” word - it is part of the name of the field in the table).

So to repeat - the attached query will run fine in SQL*Plus and in TOAD, but not in SQLNav. Gives the “missing right parenthesis” error. But the query does format in SQL Nav with no errors.

Thanks.
-Charlie
cw-query.sql (6.95 KB)


#8

Charlie,

I’ve reproduced the issue and created story #49572199 for it.

Regards,
Roman


#9

Hi Charlie and Piter,

This parser issue has been fixed already in latest beta build. Could you please have a check and confirm if it works well for you now? Any issue please let us know. Thx.

Thanks,

Shirly


#10

Hi Charlie and Piter,

To make sure that we have fixed the problem completely, could you please confirm the query can be executed and formatted well now in latest beta build 2933?

Any issue please let us know, thanks. :slight_smile:

Regards,

Shirly (shirly.nie@quest.com)


#11

Yes, this is working now, thanks.