Jump to content

executeSQL SUM help for executeSQL newbie

This topic is 2813 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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




Their are custom functions to set escape quotes for SQL

Link to comment
Share on other sites

This topic is 2813 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.