0

sorting an NP Union from different tables

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?

6 comments

Please sign in to leave a comment.