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)
val edwardsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
What are you trying to accomplish?  The SUM keyword in Oracle is associated with a function, and would normally take a column name as it's parm).

Also, you have two FROM statements, that's not allowed.

Amd the first FROM ends with

                dbo.addresses,

But the last entry in a FROM list needs to not end with a trailing comma.

Hard to propose a fully corrected solution without knowing what you want.  Perhaps something like below, but you will need some GROUP BY criteria, and the joins may not be complete.

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 
       SUM(dbo.v_penapp_hist_q.column_value_06),
       SUM(dbo.v_penapp_hist_q.column_value_04)
FROM 
       dbo.v_pension_application_q, 
       dbo.v_member_q, 
       dbo.addresses, 
       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)

Open in new window


»bp
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Also, your query seems to origin from MSSQL, as Oracle does not know of a dbo user or schema.

The correction by Bill would require to put all columns not contained in SUM, AVG, and similar aggregates into a trailing GROUP BY clause.
0
val edwardsAuthor Commented:
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),
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
There is a comma missing at the end of the preceeding line of that SUM.
0
PortletPaulEE Topic AdvisorCommented:
I am not sure about the use of that function in your current where clause, but joining predicates should be specified in the joins, not the where clause, and as already mentioned there is a comma missing (added at beginning of line 16 below), and you need to include the "non-aggregating" columns into a group by clause.  Like so:
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
    , SUM(dbo.v_penapp_hist_q.column_value_06)
    , SUM(dbo.v_penapp_hist_q.column_value_04)
FROM dbo.v_pension_application_q
INNER JOIN dbo.v_member_q ON v_pension_application_q.member_id = v_member_q.member_id
INNER JOIN dbo.addresses ON 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)
INNER JOIN dbo.v_penapp_hist_q ON v_pension_application_q.application_id = v_penapp_hist_q.application_id
WHERE dbo.v_penapp_hist_q.column_value_01 BETWEEN 1982.000000000000000 AND 2016.000000000000000
GROUP BY
      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)
    , 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

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
val edwardsAuthor Commented:
The query executed beautifully! data is accurate, however,it is only returning 3701 rows where it should be closer to 8,000.

WIP
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
1
PortletPaulEE Topic AdvisorCommented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.