Jump to content

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

Recommended Posts

Posted

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?

Posted

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

Posted

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

Posted

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

This topic is 3669 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.