OleDb SQL question: SUM function

[MikeKraken]

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. ^_^

[brouse]

I don't think you are using the SUM and IF statements correctly. Try replacing the IF with a case, eg:

Replace
sum(IF lag='200112' val ELSE 0) AS C1
with
case lag when '200112' then sum(val) else 0 end as C1

[MikeKraken]

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. :confused:

[brouse]

What DBMS are you using?

[MikeKraken]

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. ^_^

[MikeKraken]

At this (http://www.abspos.net/HelpRT/Microsoft%20Jet%20SQL%20-%20Fundamentals.htm) 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

[MikeKraken]

Sorry... I'm getting frantic. :SICK:

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

[Rune Bivrin]

You probably want the IIf(condition, truepart, falsepart) function, rather than the IF .. THEN .. ELSE construct:

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

[brouse]

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...

[MikeKraken]

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 (http://www.vbcity.com/forums/topic.asp?tid=94952), 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. *_*

[Rune Bivrin]

IIf, not IFF!

Rune

[MikeKraken]

<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!! :D

Thanks again!!

... Mike. ^_^