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 )

[#2]

yes, an as is there, it's a typo in here (i manually typed the example)

[its_anandrjs]

It seems you put count statement twice for the load statement use it only once and try

ODBC CONNECT TO <DB2_Data_Source> ;

Table:

Load

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

         SQL SELECT

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

FROM Table1

WHERE   ...

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

[#4]

I think that got me a bit closer! now it says that fieldisn't found:

Field not found - <sum(MCAQRYLIB.MPC_MBFINL.BWMEID05)>

[maxgro · Accepted solution]

if you load without LOAD, just sql select

and then CTRL-T (table viewer) I suppose you can see all field (upper/lower case)

then, using these field names you add the LOAD section

[MarcoWedel]

Isn't there a semicolon missing at the end of the preceding load?

Load

[Field 1] as [FirstField],  [Field 2] as [SecondField],  [Field 3] as [ThirdField],  [Field 4] as [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" ;

regards

Marco

[#7]

BINGO!!! you got it!

So the logic is, for "generated" fields I need to fetch the field name the database generates and then rename that in my LOAD!

THANK YOU!

[MarcoWedel]

can you please post the generated LOAD/SELECT for us to learn the changes?

thanks

regards

Marco

[#9]

So this will differ in one case to the other. But basically the method was:

1. Run the SQL in QVD alone without LOAD
2. CTRL-T and Locate the generated field name
3. Add the generated name (and any other names) to your LOAD.

So step 1:

Run the script:

        SQL SELECT

Field1,  Field2,  Field3,  "Field4, sum(Field5)

FROM Table1

WHERE  Field1 = 'test query'

GROUP BY Field1,  Field2, Field3,  Field4 ;

Step 2:

CTRL-T to find what the Database returned as a field name for "sum(Field5)"

Step 3:

LOAD

[Field1] as [FirstField],  [Field2] as [SecondField],  [Field3] as [ThirdField],  [Field4] as [FourthField], 00012 as [SumOfFifthField];

        SQL SELECT

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

FROM Table1

WHERE  Field1 = 'test query'

GROUP BY Field1,  Field2, Field3,  Field4;

So in this case 00012 was given by the database to the sum() function.

NB: The queries and loads above are written by hand (not my actuals) because my actual is WAY too complex to point out the exact solve (50 lines of SQL), but this should tell you the story.

I actually replicated that logic for a count() I had in another tab of my script, and works there too! excellent thought process by Massimo Grossi (grazie mille)