1

I'm a newb to both ASP.NET and Oracle SQL. I'm attempting to create one long SQL string (using UNION ALL) and then pass the string to a Data Handler class that takes care of the rest. The problem certainly exists in my SQL string.

Here is the first section of my code, which runs flawlessly:

sql = "SELECT 'DCS - HCA (81)', HCA_SENT_DCS, HCA_VALID_DCS, HCA_SUSPEND_DCS, HCA_REJECTED_DCS,"
sql &= " SUM(HCA_VALID_DCS + HCA_SUSPEND_DCS + HCA_REJECTED_DCS) As ""Total Returned"","
sql &= " RESOLVED_CNT, SUM(HCA_REJECTED_DCS - RESOLVED_CNT) As ""To Do"""
sql &= " FROM HUB_CDI_CONTROL_NBRS WHERE REPORT_DATE = :0 "
_param.Add(dateSelected.ToString("dd-MMM-yyyy"))
sql &= " GROUP BY HCA_SENT_DCS, HCA_VALID_DCS, HCA_SUSPEND_DCS, HCA_REJECTED_DCS,"
sql &= " RESOLVED_CNT"

However, when I try to UNION this SQL with another SELECT statement, I get the following exception:

ORA-00933: SQL command not properly ended

Here is the full code:

sql = "SELECT 'DCS - HCA (81)', HCA_SENT_DCS, HCA_VALID_DCS, HCA_SUSPEND_DCS, HCA_REJECTED_DCS,"
sql &= " SUM(HCA_VALID_DCS + HCA_SUSPEND_DCS + HCA_REJECTED_DCS) As ""Total Returned"","
sql &= " RESOLVED_CNT, SUM(HCA_REJECTED_DCS - RESOLVED_CNT) As ""To Do"""
sql &= " FROM HUB_CDI_CONTROL_NBRS WHERE REPORT_DATE = :0 "
sql &= " GROUP BY HCA_SENT_DCS, HCA_VALID_DCS, HCA_SUSPEND_DCS, HCA_REJECTED_DCS,"
sql &= " RESOLVED_CNT"

sql &= " UNION ALL"

sql &= "SELECT 'SFDC - HCA (82)', HCA_SENT_SFDC, HCA_VALID_SFDC, HCA_SUSPEND_SFDC, HCA_REJECTED_SFDC,"
sql &= " SUM(HCA_VALID_SFDC + HCA_SUSPEND_SFDC + HCA_REJECTED_SFDC) As ""Total Returned"","
sql &= " RESOLVED_CNT, SUM(HCA_REJECTED_SFDC - RESOLVED_CNT) As ""To Do"""
sql &= " FROM HUB_CDI_CONTROL_NBRS WHERE REPORT_DATE = :0 "
sql &= " GROUP BY HCA_SENT_SFDC, HCA_VALID_SFDC, HCA_SUSPEND_SFDC, HCA_REJECTED_SFDC,"
sql &= " RESOLVED_CNT"

_param.Add(dateSelected.ToString("dd-MMM-yyyy"))
_DH.TheSQL = sql
_DT = _DH.GetTableWithParameters(_param.ToArray)

I am concerned with two parts of this code:

  1. Am I correctly calling the _param.Add() function? Since I'm referencing the parameter twice, using :0, do I only have to use the _param.Add() function once?

  2. I feel that I'm incorrectly using the GROUP BY syntax. Oracle yelled at me when I tried to run queries without using GROUP BY, but I'm unsure if I'm using them correctly. Essentially, I just added each column I SELECTed to GROUP BY

Otherwise, I am sure there are other errors I am missing. Please help!

Thank you.

Improve this question
1
  • It may be much easier to recognise problems by getting the SQL statement working first outside of your asp.net application. Also after a quick glance you need a space after UNION ALL or before the next select statement. Commented Nov 13, 2012 at 21:34

1 Answer 1

Reset to default
1 
sql &= " UNION ALL"
sql &= "SELECT 'SFDC - HCA (82)', HCA_SENT_SFDC, HCA_VALID_SFDC, HCA_SUSPEND_SFDC, HCA_REJECTED_SFDC,"

Results in, somewhere in the middle:

... UNION ALLSELECT 'SFDC - HCA (82)', HCA_SENT_SFDC, ...

Note: "UNION ALLSELECT"

The full code with other fixes. I don't believe you actually wanted to GROUP BY, to add/subtract, you DON'T need the SUM() function. To be valid in a UNION ALL, all columns of the first part must be named/aliased.

sql = "SELECT 'DCS - HCA (81)' Title, HCA_SENT_DCS, HCA_VALID_DCS, HCA_SUSPEND_DCS, HCA_REJECTED_DCS,"
sql &= " HCA_VALID_DCS + HCA_SUSPEND_DCS + HCA_REJECTED_DCS As ""Total Returned"","
sql &= " RESOLVED_CNT, HCA_REJECTED_DCS - RESOLVED_CNT As ""To Do"""
sql &= " FROM HUB_CDI_CONTROL_NBRS WHERE REPORT_DATE = :0 "
sql &= " UNION ALL "
sql &= "SELECT 'SFDC - HCA (82)', HCA_SENT_SFDC, HCA_VALID_SFDC, HCA_SUSPEND_SFDC, HCA_REJECTED_SFDC,"
sql &= " HCA_VALID_SFDC + HCA_SUSPEND_SFDC + HCA_REJECTED_SFDC As ""Total Returned"","
sql &= " RESOLVED_CNT, HCA_REJECTED_SFDC - RESOLVED_CNT As ""To Do"""
sql &= " FROM HUB_CDI_CONTROL_NBRS WHERE REPORT_DATE = :1 "

_param.Add(dateSelected.ToString("dd-MMM-yyyy"))
_param.Add(dateSelected.ToString("dd-MMM-yyyy"))  ' 2nd one
_DH.TheSQL = sql
_DT = _DH.GetTableWithParameters(_param.ToArray)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you very much. The error was indeed the missing space between UNION ALL and SELECT. Also, I removed all the SUM functions and used regular operators + and - instead. Now, I don't have to use the GROUP BY clause.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.