Scripting a SQL sum() in QV Load

[#1]

Hello folks!

A QV newbie here!

I want to load a GIANT dataset into QV and to make it very simple I want to use a SQL sum() in my statement to cut down on returned rows (since i'll sum them anyway).

Basically I want to Load this:

SELECT

Field 1,  Field 2,  Field 3,  Field 4, sum(Field5) as Count5

FROM

Table1

WHERE

...

GROUP BY

...

I figured this would be done by saying:

Table:

Load

[Field 1] as [FirstField],  [Field 2] as [SecondField],  [Field 3] as [ThirdField],  [Field 4] [FourthField], Count5 as [CountOfFifthField]

Now:

The SQL on it's own works and gives me exactly what I need in a SQL edito

The SQL without "sum(Field5) as Count5" works in QV.

But once I add "sum(Field5) as Count5" to my SQL in QV, it complains that: Field not found <Count5>.

How can I add that Sum() to my SQL so I do it in SQL rather than part of the Load statement itself (the bottleneck is pulling the data)

Note: RDBMS is DB2, and the SQL above is pretty fake.. but pretty accurate structure wise (there's a LOT of joins in there )

[maxgro]

try to start with the simpler statement, no LOAD, no as

does it works?

SQL

SELECT

Field 1,  Field 2,  Field 3,  Field 4, sum(Field5)

FROM

Table1

WHERE

...

GROUP BY

...;

[hic]

I am not sure I understand... Are you saying that

ODBC CONNECT TO <DB2_Data_Source> ;

SQL SELECT

"Field 1",  "Field 2",  "Field 3",  "Field 4", sum("Field5") as Count5

FROM Table1

WHERE   ...

GROUP BY "Field 1",  "Field 2",  "Field 3",  "Field 4" ;

doesn't work in QlikView? If the connect string is OK and the fields exist, I see no reason why it shouldn't.

HIC

[#4]

My Mistake, I would've been more clear.

I'm saying this works:

ODBC CONNECT TO <DB2_Data_Source> ;

SQL SELECT

"Field 1",  "Field 2",  "Field 3",  "Field 4", sum("Field5") as Count5

FROM Table1

WHERE   ...

GROUP BY "Field 1",  "Field 2",  "Field 3",  "Field 4" ;

But this doesn't:

ODBC CONNECT TO <DB2_Data_Source> ;

Table:

Load

[Field 1] as [FirstField],  [Field 2] as [SecondField],  [Field 3] as [ThirdField],  [Field 4] [FourthField], Count5 as [CountOfFifthField]

         SQL SELECT

"Field 1",  "Field 2",  "Field 3",  "Field 4", sum("Field5") as Count5

FROM Table1

WHERE   ...

GROUP BY "Field 1",  "Field 2",  "Field 3",  "Field 4" ;

[#5]

No load and no as works, sample data was loaded.

I think my issue is that in the LOAD portion I can't "rename" sum(Field5), so i thought i'd give it a SQL alias. So that's why I added the SQL as

[#6]

14                      To Connect a database using JDBC

Program:

import java.sql.*;

import java.lang.*;

public class Db{

  public static void main(String[] args) {

  System.out.println("display result");

  Connection con = null;

  try{

  Class.forName("com.mysql.jdbc.Driver");

  con = DriverManager.getConnection("jdbc:mysql://localhost:3306/felistas","root","");

try{                                                                     

  String sql = "select * from t1";

  Statement st = con.createStatement();

ResultSet rs=st.executeQuery(sql);

  System.out.println("result are!");

  while(rs.next())

1. System.out.println(rs.getString("name")+"\t"+rs.getString("regno"));

  con.close();

  } catch (SQLException s){

  System.out.println("SQL statement is not executed!");

  }

  }

  catch (Exception e){

  e.printStackTrace();

  }

  }

}

[eduardo_sommer]

Maybe its a typo, but an as is missing between [Field4] and [FourthField]

Eduardo

[#8]

no true

[#9]

use jdbc

ODBC CONNECT TO <DB2_Data_Source> ;

SQL SELECT

"Field 1",  "Field 2",  "Field 3",  "Field 4", sum("Field5") as Count5

FROM Table1

WHERE   ...

GROUP BY "Field 1",  "Field 2",  "Field 3",  "Field 4" ;

But this doesn't:

ODBC CONNECT TO <DB2_Data_Source> ;

Table:

Load

[Field 1] as [FirstField],  [Field 2] as [SecondField],  [Field 3] as [ThirdField],  [Field 4] [FourthField], Count5 as [CountOfFifthField]

         SQL SELECT

"Field 1",  "Field 2",  "Field 3",  "Field 4", sum("Field5") as Count5

FROM Table1

WHERE   ...

GROUP BY "Field 1",  "Field 2",  "Field 3",  "Field 4" ;

not sql

[maxgro]

you can rename sum(Field5) in the LOAD portion

[sum(Field5)] as ......