OleDb SQL question: SUM function


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

Thread: OleDb SQL question: SUM function

  1. #1
    Join Date
    Feb 2004
    Location
    Seoul
    Posts
    22

    [RESOLVED] OleDb SQL question: SUM function

    Hi, all,

    I think I'm using this function correctly, but maybe someone can shed some light on the situation. Here is my SQL statement:

    SELECT dt AS AY, sum(IF lag='200112' val ELSE 0) AS C1, sum(IF lag='200201' val ELSE 0) AS C2, sum(IF lag='200202' val ELSE 0) AS C3, sum(IF lag='200203' val ELSE 0) AS C4, sum(IF lag='200204' val ELSE 0) AS C5, sum(IF lag='200205' val ELSE 0) AS C6, sum(IF lag='200206' val ELSE 0) AS C7, sum(IF lag='200207' val ELSE 0) AS C8, sum(IF lag='200208' val ELSE 0) AS C9, sum(IF lag='200209' val ELSE 0) AS C10, sum(IF lag='200210' val ELSE 0) AS C11, sum(IF lag='200211' val ELSE 0) AS C12, sum(IF lag='200212' val ELSE 0) AS C13, sum(IF lag='200301' val ELSE 0) AS C14, sum(IF lag='200302' val ELSE 0) AS C15, sum(IF lag='200303' val ELSE 0) AS C16, sum(IF lag='200304' val ELSE 0) AS C17, sum(IF lag='200305' val ELSE 0) AS C18, sum(IF lag='200306' val ELSE 0) AS C19, sum(IF lag='200307' val ELSE 0) AS C20, sum(IF lag='200308' val ELSE 0) AS C21, sum(IF lag='200309' val ELSE 0) AS C22, sum(IF lag='200310' val ELSE 0) AS C23, sum(IF lag='200311' val ELSE 0) AS C24, sum(IF lag='200312' val ELSE 0) AS C25
    FROM
    (SELECT [AY] AS dt, [CY] AS lag, round(sum(PAID),0) AS val
    FROM
    (SELECT [CM] AS CY, [AM] AS AY, [RM] AS RY, [PM] AS PY, [PRD_CODE] AS PRD_CODE, [PAID] AS Paid, [OS] AS OS, [AGE] AS AGE FROM Hospitalization_small.csv)
    GROUP BY AY, CY HAVING AY BETWEEN '200112' AND '200312')
    GROUP BY dt ORDER BY dt
    The error I am receiving says this:

    System.Data.OleDb.OleDbException: 'sum(IF lag='200112' val ELSE 0)' 쿼리식의 연산자가 없는 구문 오류입니다.
    at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
    at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
    at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
    at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
    The Korean translates to "Query is a syntax error which is not operator." I'm thinking it's a problem with SUM, but I'm not too sure. Can anybody help me out?

    Thanks in advance,

    ... Mike. ^_^
    Last edited by MikeKraken; 03-15-2005 at 09:18 PM.

  2. #2
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    I don't think you are using the SUM and IF statements correctly. Try replacing the IF with a case, eg:

    Replace
    Code:
    sum(IF lag='200112' val ELSE 0) AS C1
    with
    Code:
    case lag when '200112' then sum(val) else 0 end as C1
    Bob Rouse
    Dimension Data

  3. #3
    Join Date
    Feb 2004
    Location
    Seoul
    Posts
    22
    Thank you for your reply. The thing is, I had a CASE statement before and OleDb does not support it. I tried your code exactly and received the "E_FAIL(0x80004005)" error again, which is what I was expecting. -_-

    When I try "SELECT dt AS AY, IF lag='200112' SUM(val) ELSE 0 AS C1, ...", I get this error again instead: System.Data.OleDb.OleDbException: 'IF lag='200112' SUM(val) ELSE 0' 쿼리식의 연산자가 없는 구문 오류입니다. ("Query is a syntax error which is not operator.")

    ... Mike.

  4. #4
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    What DBMS are you using?
    Bob Rouse
    Dimension Data

  5. #5
    Join Date
    Feb 2004
    Location
    Seoul
    Posts
    22
    I'm using OleDb, now... I'm converting SQL from Oracle queries so that I can read data from Excel, Access, and text files. The original SQL code works fine for connecting with an Oracle database, but when I started using the same SQL for my OldDb version, I had to fix some things (like adding in the AS keyword and removing the CASE WHEN statement).

    I hope that was the right answer to your question... it's been a while since I dealt with databases and I'm not too versed in SQL anymore.

    ... Mike. ^_^

  6. #6
    Join Date
    Feb 2004
    Location
    Seoul
    Posts
    22
    At this Web page, they have the following:
    SELECT Sum(Amount) AS [Total Invoice Amount]
    FROM tblInvoices
    Since the page is for Jet SQL for Access 2000 (I am using OleDb, which uses Jet for accessing Access), I assumed that I could use the SUM statement as I am in my query.

    ... Mike. O_o

  7. #7
    Join Date
    Feb 2004
    Location
    Seoul
    Posts
    22
    Sorry... I'm getting frantic.

    I tried just using SUM(val) AS C1, etc., and it worked for getting data, but it was the wrong data (of course). There must be some issue with using the IF statement inside of the SUM function, but I'm not sure how to get around it.

    Like I said, SQL is hardly my specialty, and I can't seem to find anything on the Internet that talks about using a conditional statement inside of a function...

    ... Mike. O_o

  8. #8
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    You probably want the IIf(condition, truepart, falsepart) function, rather than the IF .. THEN .. ELSE construct:

    Code:
    SELECT dt AS AY, sum(IIF(lag='200112', val, 0)) AS C1
    FROM 
    (SELECT [AY] AS dt, [CY] AS lag, round(sum(PAID),0) AS val 
    FROM 
    (SELECT [CM] AS CY, [AM] AS AY, [RM] AS RY, [PM] AS PY, [PRD_CODE] AS PRD_CODE, [PAID] AS Paid, [OS] AS OS, [AGE] AS AGE FROM Hospitalization_small.csv) 
    GROUP BY AY, CY HAVING AY BETWEEN '200112' AND '200312') 
    GROUP BY dt ORDER BY dt
    This is not really a OLEDB issue, as I understand it, but rather an Access SQL issue.

    Rune
    If you hit a brick wall, you didn't jump high enough!

  9. #9
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    Okay sorry - my experience is with SQL, not oleDB. I know that the IIF() construct shown is a valid VBA syntax; I didn't realize you could use it with oleDB.

    Good to know...
    Bob Rouse
    Dimension Data

  10. #10
    Join Date
    Feb 2004
    Location
    Seoul
    Posts
    22
    Hmmm... well, I tried the IFF statement, and this is the error I got:

    System.Data.OleDb.OleDbException: 'IFF' The function was not defined.
    SELECT dt AS AY, SUM(IFF(lag='200112', val, 0)) AS C1, .... was the beginning of my query...

    To clarify, I am updating a program in Visual Basic .NET and am trying to fill a data adapter. I'm sorry, I should have mentioned that earlier. I was asking about this over on vbCity, and after finding out that I was using the CASE WHEN statement improperly (i.e. not supported in Access SQL), I decided to venture out and try to find why my SQL continued to not work.

    Thank you both for your help so far... anything further you may be able to suggest would be greatly welcomed, because I am plum out of ideas.

    ... Mike. *_*

  11. #11
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    IIf, not IFF!

    Rune
    If you hit a brick wall, you didn't jump high enough!

  12. #12
    Join Date
    Feb 2004
    Location
    Seoul
    Posts
    22
    <FOCLOL> Thank you so much, Rune! <LMAO> I guess I've been in the Math faculty for too long (IFF = if and only if)...

    IT WORKS LIKE A CHARM!!

    Thanks again!!

    ... Mike. ^_^

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center