executeSQL SUM help for executeSQL newbie

[infoweb]

Hi there,

 

I'm trying to get a simple "SUM" calculation working using executeSQL (I need to expand on it's complexity later)

 

I have 2 Tables:

 

• ProductSalesReportLines
• Vend_Sales_Lines_API

 

In the ProductSalesReportLines I have 4 fields - 3 with existing Values and one that I'm trying to calculate (Sales) based on on data in another table.

 

• _kf__Product__UUID - Product Key (existing)
• StartDate - Start Date (Global field - populated)
• EndDate - End date (Global field - Populated)
• Sales - Number of sales to be calculated from Vend_Sales_Lines_API between start and end date  above

 

Vend_Sales_Lines_API has 3 values

 

• _kf__Product__UUID
• Date
• Quantity

 

The Goal is to calculate the following:

 

I am trying to calculate the number of "Sales" using:

 

• SUM of quantity in Vend_Sales_Lines_API
• Between the date range specified in StartDate and EndDate
• Using related records based on _kf__Product__UUID

 

I am completely new to executeSQL so I thought I'd start with a simpler query - Just get the total number of sales from Vend_Sales_Lines_API (leaving out the date range part for now) but after hours of reading and testing I can't get a correct result - I just keep getting "?" in the sales field

 

I have tried dozens of syntax's but obviously I'm just getting it wrong.

 

This is the calculation I've finished with:

ExecuteSQL ( " SELECT SUM ( quantity ) FROM Vend_Sales_Lines_api JOIN ProductSalesReportLines ON  Vend_Sales_Lines_api._kf__Product_UUID = ProductSalesReportLines._kf__Product_UUID  " ; " "  ; " " )

Any pointers as to what I'm doing wrong?

[Wim Decorte]

The "?" indicates a syntax error.

 

Use something like this http://www.modularfilemaker.org/module/query-builder/to help you find the error.

 

From the looks of your calc, start with quoting all your fields and tablenames.  SQL does not like fields that start with underscores.

[Kris M]

Syntax for using aliases:

SELECT C.NameFirst, C.NameLast, O.DateOrdered
FROM Customers AS C, Orders AS O
WHERE C.custID = O.custIDf

Once you define an alias ( AS something) you have to use it everywhere

 

Wims comment about _ field names is spot on so you need to properly escape quote them as...

 

SELECT alias."field" FROM alias."table" ... etc

 

Proper escape quoting will guard against object name incompatibilities for specific characters like underscore in first position of object name AND reserved SQL words used as object name

 

https://www.drupal.org/node/141051

 

Their are custom functions to set escape quotes for SQL

[Wim Decorte]

Once you have your syntax cleaned up, I think you'll find that you are still not getting the right result.  You're declaring a join but not doing anything with it

[Kris M]

Wim with the slam dunk !

Sorry March madness

[infoweb]

Thanks for the input. Wim thanks to your initial suggestion I came across sql explorer, so getting familiar with that at the moment.