• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 58
  • Last Modified:

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)
0
val edwards
Asked:
val edwards
  • 3
  • 2
  • 2
  • +1
4 Solutions
 
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
 
QlemoBatchelor, 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
There is a comma missing at the end of the preceeding line of that SUM.
0
 
PortletPaulfreelancerCommented:
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
 
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
 
QlemoBatchelor, 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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now