asked on

I'm having problem running this SQL query. My DBMS is stating Error ORA-00933: SQL command line not properly ended, Batch 1 Line 21 Col 20 (using DBArtisan)

SELECT /*+ FIRST_ROWS */
dbo.v_member_q.ssn,
dbo.v_pension_application_q.ssn,
dbo.v_member_q.first_name,
dbo.v_member_q.last_name,
dbo.v_pension_application_q.sort_name,
dbo.addresses.address_1,
dbo.addresses.address_2,
dbo.addresses.city,
dbo.addresses.state,
dbo.pkg_benefit_s.f_build_zipcode(dbo.addresses.zip)as "FORMATTED_ZIP",
dbo.v_pension_application_q.vesting_date,
dbo.v_pension_application_q.pension_type_id,
dbo.v_pension_application_q.pension_effective_date,
dbo.v_pension_application_q.status_id
FROM
dbo.v_pension_application_q,
dbo.v_member_q,
dbo.addresses,
SUM
dbo.v_penapp_hist_q.column_value_06,
dbo.v_penapp_hist_q.column_value_04,
FROM
dbo.v_penapp_hist_q
WHERE
( v_pension_application_q.member_id=v_member_q.member_id AND
addresses.address_id=(dbo.f_get_current_address_id(dbo.pkg_benefit_s.f_get_entity_id('person'),dbo.v_pension_application_q.person_id)) AND
v_pension_application_q.application_id=v_penapp_hist_q.application_id ) AND
(dbo.v_penapp_hist_q.column_value_01 between 1982.000000000000000 and 2016.000000000000000)

SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

val edwards

ASKER

This helps a lot although i am still receiving a "FROM keyword not found where expected Batch 1 line 16 Col 21" which is
SUM (dbo.v_penapp_hist_q.column_value_06),

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

val edwards

ASKER

The query executed beautifully! data is accurate, however,it is only returning 3701 rows where it should be closer to 8,000.

WIP

Qlemo

That's something only you can research. We have no clue about the underlaying data logic, and where you get your comparative numbers from.
E.g. if the sum combines 2 - 3 rows in average, the 3701 / 8000 ratio looks reasonable.

PortletPaul

Are you certain you need to sum() the columns from the history table? The reduction of rows is likely to be due to the group by clause.

What happens if you remove the sum functions and remove the group by clause? Are you now getting the expected row numbers?