0

sorting an NP Union from different tables

Michael Hylands

June 10, 2022 22:23

None

Follow

I'm trying to replicate the following SQL code in Jet:

select order_num, my_date FROM
(
select [Job No_] AS order_num, [planned delivery date] AS my_date from [Job Planning Line]
where Year([Planned Delivery Date]) = '2022' AND Month([Planned Delivery Date]) >= '6' AND Month([Planned Delivery Date]) <= '8'
) AS job_info
union
(
select [No_] AS order_num, [due date] AS my_date from [Production Order]
where Year([due Date]) = '2022' AND Month([due Date]) >= '6' AND Month([due Date]) <= '8'
)
Order by my_date

I have filters for each of the select statements:

=@NL("filter","production order","no.","status","2|3","due date",$C$6)

=@NL("filter","job planning line","job no.","type","1","usage link","1","remaining qty.",">0","planned delivery date",$C$6)

where C6 contains the date range.

I cannot apply a date Sort in the NP("union") statement as the date fields have different names.

Is there a method in Jet similar to the 'Select AS' in SQL, or another way to do this?

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  CascadiaDataworks June 14, 2022 23:09

  There are some things that Jet just cannot do.  For instance, sort on a Filter, Unions, Intersect, etc.  Since you already have the SQL statement, have you tried using SQL=?

  https://support.jetglobal.com/hc/en-us/articles/219403107-SQL-Reference 

• 0
  
  Michael Hylands June 15, 2022 14:34

  Hi Bob,

  Yes, I looked at SQL= but we're using NAV and it says it is not available for the Dynamics NAV connector. Not sure if that means it won't work on a NAV database or it won't work if you're only licensed for Jet with NAV.

  Michael

• 0
  
  Michael Hylands June 15, 2022 15:21

  I tried a fairly simple SQL= but it returns an error that my SQL= statement is not a valid table.

  =@NL("rows","SQL = SELECT [No_] FROM [Production Order] WHERE %filter1%","no_","1d=[due date]", "15-6-22..06-7-22","datasource=","nav")

  "nav" is our Jet NAV connector so I tried creating a separate universal SQL connector for the NAV database but I receive the same error. Regular Jet statements work with the generic connector.

  Edited by Michael Hylands June 15, 2022 15:27
• 0
  
  Michael Hylands June 15, 2022 15:59

  I tried using SQL= on a different (non Nav) SQL database but still receive this error:

  Invalid Table 'SQL = SELECT VariableID FROM Variable WHERE %filter1%'.

  Here is the Jet statement:

  =@NL("rows","SQL = SELECT VariableID FROM Variable WHERE %filter1%","VariableID","1s=Variable.VariableName", "Calc*")

  I even tried changing my variable names to all lower case but that didn't help. Am I missing something in my SQL= statement?

• 0
  
  CascadiaDataworks June 15, 2022 16:33

  You are right, you can't use SQL= against a NAV database using the standard NAV connector, so if you need to pull from NAV, Jet doesn't really have a way to sort the data the way you want in your initial request.  It will always sort on Field parameter on the NL(Filters).

  In regards to SQL=, it can be tricky and sometimes can helpful to work with a consultant.  The statement looks right to me but there are a couple things that stick out.

  • Try SQL=SELECT instead of SQL = SELECT (remove the spaces)
  • You also probably don't need proceeding table name in the filter.  I would also try 1s=VariableName instead of 1s=Variable.VariableName

   

• 0
  
  Michael Hylands June 15, 2022 18:04

  That was it! The spaces were the issue. I have both my non-NAV and NAV SQL=SELECT statements working now.

  Note: I had to use the full table name inside [] for the NAV table, i.e. [NAV1$Employee]

  Thanks!

  Now to see if I can get the full query to work.