0

SQL+ Function to get last record

Dorka Cruz

February 09, 2018 16:01

None

Follow

Good morning, I hoping someone can help get the last record from a table via Universal connection for Oracle using the sql= function

I'm trying to get the fourth line Exp_Date for this record which is blank(null).  I tried this function first =NL("last","sql=SELECT EXP_DATE FROM MBR_PLAN WHERE %FILTER1%","EXP_DATE","DataSource=","ODW","1S=SSN",$G11)

and it returns the 3rd record of 12/31/17.

When I tried =NL("last","sql=SELECT EXP_DATE FROM MBR_PLAN JOIN MEMBER ON MBR_PLAN.SSN=MEMBER.SSN WHERE %FILTER1% %FILTER2%","EXP_DATE","DataSource=","ODW","1S=SSN",$G11,"2S=MEMBER",$J11). It says SQL command not properly ended. I'm not sure if I'm missing a comma or WHERE statement by the %FILTER1%.

 

• Facebook
• Twitter
• LinkedIn

17 comments

• 0
  
  Harry Lewis February 09, 2018 16:34

  Have you tried using only one %filter% in your SQL statement? (it can be used multiple times as described in the Using SQL= article)

• 0
  
  Dorka Cruz February 09, 2018 17:35

  Using the %filter% without the 1 returns #value

  =NL("last","sql=SELECT EXP_DATE FROM MBR_PLAN WHERE %FILTER%","EXP_DATE","DataSource=","ODW","1S=SSN",$G11)

  when I used the %filter1% it returned the 3rd record 12/31/17 =NL("last","sql=SELECT EXP_DATE FROM MBR_PLAN WHERE %FILTER1%","EXP_DATE","DataSource=","ODW","1S=SSN",$G11)

• 0
  
  Harry Lewis February 09, 2018 23:37

  Hi Dorka -

  Keep in mind that the NL("Last") function will return the last record - based on the database's own sorting.  If you want to define last based on a particular field, you have to sort by that field.

  In the case of a SQL= function, I would assume it would require an ORDER BY clause.

• 0
  
  Dorka Cruz February 12, 2018 15:01

  I did try the ORDER BY clause and it returns #VALUE(see NL below), the https://jetsupport.jetreports.com/hc/en-us/articles/219403107-SQL-Reference article wasn't clear on how to write it.  Something is missing but I don't know what it is.

  
  
  

• 0
  
  Dorka Cruz February 12, 2018 15:03

  Sorry that was the wrong function it's this one:

• 0
  
  Dorka Cruz February 12, 2018 15:06

  The #value error says 'the function cannot be tokenized with the current function literals. The unknown text ‘EXP_DATE’ cannot be parsed'

• 0
  
  Harry Lewis February 12, 2018 15:43

  Hello Dorka -

  It appears that you want to return and sort by the EXP_DATE field.

  Let's try to simplify things, a bit.  Ensure that the data source you want to use is the default data source shown on the Jet ribbon:

  

  Next, let's try this function:

  =NL("Last","SQL=SELECT Exp_date FROM Mbr_plan WHERE %filter1% ORDER BY %sort","Exp_date","1S=EXP_DATE","*","+1S=EXP_DATE","*")

  What does that do?

• 0
  
  Dorka Cruz February 12, 2018 15:51

  I still get #value error that states 'a sort substitution tag was found in the SQL query but no sort fields were specified'

• 0
  
  Harry Lewis February 12, 2018 16:02

  And if you use a slightly modified version of  your original function?

  =NL("Last","SQL=SELECT Exp_date FROM Mbr_plan WHERE %filter1% ORDER BY %sort%","Exp_date","1S=EXP_DATE","*","+1D=EXP_DATE","*")

  (the only changes being that I replaced +1S with +1D and put in a missing % on %sort%)

   

  Edited by Harry Lewis February 12, 2018 16:04
• 0
  
  Dorka Cruz February 12, 2018 16:13

  same error: 'a sort substitution tag was found in the SQL query but no sort fields were specified'

• 0
  
  Harry Lewis February 12, 2018 17:27

  Here is a screen shot of my report (with an overlay of the data source):

  

  Could you show a similar screen shot of your function (without the data, of course)?

  Edited by Harry Lewis February 12, 2018 17:27
• 0
  
  Dorka Cruz February 12, 2018 19:41

  this is what it looks like in design mode without using ORDER BY %sort% it pull the 9/30/17 Exp_Date but when you run the report it gives the 12/31/17 Exp_Date.

  

  Adding the ORDER BY %sort% as per your instructions gives #value, in the original function it seems to not recognize the blank '@@' (null)

  

• 0
  
  Harry Lewis February 12, 2018 21:03

  And when you select the cell in column L and click the Check Error or Debug button on the Jet ribbon, this is the message that is displayed?

  'a sort substitution tag was found in the SQL query but no sort fields were specified'

  I don't understand what you are referring to when you state " the original function it seems to not recognize the blank '@@' (null)"

• 0
  
  Dorka Cruz February 12, 2018 21:33

  Yes, the debug displays 'a sort substitution tag was found in the SQL query but no sort fields were specified'

  This original function without the ORDER BY: =NL("last","sql=SELECT EXP_DATE FROM MBR_PLAN WHERE %FILTER1%","EXP_DATE","DataSource=","ODW","1S=SSN",$G11).  When I run the report is returns the EXP_DATE 12/31/17

  

• 0
  
  Harry Lewis February 12, 2018 21:52

  Hi Dorka -

  I cannot see an issue with your function.  I would suggest submitting a request to Jet Reports Technical Support to see if they have come across this before - either with or without Oracle.

• 0
  
  Dorka Cruz February 13, 2018 14:47

  I was thinking should I have %filter1% for the SSN and a %filter2% for the EXP_DATE before doing the ORDER BY.  If so would you know how it should look.  I'm not too familiar with the SQL= and the instructions/reference aren't too clear.  In the meantime I will put in a ticket to Technical support.

• 0
  
  Dorka Cruz February 13, 2018 22:05

  So technical support was able to provide guidance and the correct function is below, %sort% was not needed

  =NL("Last","SQL=SELECT Exp_date FROM Mbr_plan WHERE %filter1% ORDER BY  Exp_date","Exp_Date","DataSource=","ODW","1S=SSN",$G11)