Can this be optimized?

[kkorynta]

Hi All,

Is there a way to perform a max() in the WHERE clause of an SQL statement without having to load the full table beforehand?

In my example, I'd like to load the most current month's invoices as well as the previous month's. Typically the current month's invoices are not loaded until the 15th through 20th, so until the new ones are loaded I'll have to use the previous month and the month before that.

Example: 1307 invoices not loaded --> pull 1306 and 1305. If 1307 is loaded, pull 1307 and 1306.

Here's what I'm using currently:

AllInvoices:

SQL SELECT GL_Period__c

FROM Invoice__c;

MaxGLPeriod:

LOAD

  max(num#(GL_Period__c)) as MaxGLPeriod,

  max(num#(GL_Period__c)) -1 as MaxGLPeriodLessOne

RESIDENT AllInvoices;

LET vMaxGLPeriod = text(peek('MaxGLPeriod', 0, MaxGLPeriod));

LET vMaxGLPeriodLessOne = text(peek('MaxGLPeriodLessOne',0,MaxGLPeriod));

DROP TABLE MaxGLPeriod, AllInvoices;

INVOICE:

SQL SELECT

    GL_Period__c

FROM Invoice__c where GL_Period__c ='$(vMaxGLPeriod)' or GL_Period__c = '$(vMaxGLPeriodLessOne)';

I know the above works, but I'd like to see if it's possible to remove the 1st SQL table and avoid creating a 2nd table.

I was thinking something along the lines of below, but that doesn't compile.

INVOICE:

SQL SELECT

    GL_Period__c

FROM Invoice__c where GL_Period__c =max(num#(GL_Period__c)) or GL_Period__c = max(num#(GL_Period__c)) -1;

Thoughts?

[rbecher]

Hi Kelly,

you can achieve this by a Sub-Select in the Where clause:

SQL SELECT GL_Period__c FROM Invoice__c 
where GL_Period__c >= (SELECT max(GL_Period__c) FROM Invoice__c) -1;

- Ralf

[kkorynta]

I see and that makes sense.

Is it still possible to use the conversion functions in the where clause? The GL_Period__c field is a text field in our source database, so originally I converted it to a number for the max() statement then converted it back to text for the WHERE clause (hence the A OR B).

I tried something like

WHERE GL_Period__c >=  '(SELECT max(GL_Period__c) FROM Invoice__c) -1';

and it pulled all of the invoices ever created.

[rbecher · Accepted solution]

You'll need a database conversion/cast function like to_number():

SQL SELECT GL_Period__c FROM Invoice__c 
where GL_Period__c >= (SELECT max(to_number(GL_Period__c)) FROM Invoice__c) -1;

[kkorynta]

Hi Ralf,

I think I'm not able to get it working because I'm using the Salesforce connector which appears it has to follow these guidelines Force.com SOQL and SOSL Reference. I'm still kind of new to SQL and SOQL so I wasn't even aware that in SQL you could cast/convert. It appears SOQL doesn't allow casting/converting.

However, great feedback! It'll definitely get put to use at some point. Thank you for your time.

[rbecher]

And I thought you have a real database backend.. 😉

In this case just use this SQL select statement and Load:

LOAD num#(max_period) as MaxGLPeriod, num#(max_period)-1 as MaxGLPeriodLessOne;
SQL SELECT max(GL_Period__c) as max_period FROM Invoice__c

The SOQL max() aggregation function should work also on a varchar column period if it has the format YYMM.

Good luck!

- Ralf