SELECT DISTINCT discussion / EXEC_SQL - major hack

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

SELECT DISTINCT discussion / EXEC_SQL - major hack

Post by Jaymer »

At this point, this isn't very useful... in fact, this will be a waste of your time to read. Abandon all hope...
Inspired by BobK and his post about EXEC_SQL, even though he wasn't referring to this at all.

So here is a Query:
Screen Shot 2022-02-12 at 4.20.46 PM.png
Screen Shot 2022-02-12 at 4.20.46 PM.png (62.05 KiB) Viewed 4985 times
And the text version:

Code: Select all

EXEC_SQL ` 
select 
	ROW_NUMBER() OVER(ORDER BY xManuf ASC) AS ID,
	1 as BASVERSION, 
	getdate() AS BASTIMESTAMP, 
	xmanuf as Manuf 
from
 (select distinct xmanuf from assets) as s 
` 
RETURN NP_Manuf
I have a BO to receive this data. "NP_Manuf" It has 1 field in it: "Manuf"
(Imagine you have a Big inventory file, and in it there is a manufacturer field - and you want to get a unique list of Manufs... maybe for Filter choices)

NOTE 1: I tried this as a Non-persist BO, but EXEC_SQL can't send results into NP and complains, so its a "real" BO even tho still starts with "NP_"
NOTE 2: Even though it now exists in MSSQL, it never gets any rows inserted into it - so it doesn't really hurt anything.
NOTE 3: You could use the Inventory file for the "RETURN" statement at the end of the Query - but that generates a TON of error messages in the Server log. Because the BO might have 20 fields and you're only returning 1 from SQL. Thats messy and causing overhead (ie. the errors) so It seems worth it to just create a matching BO for the data structure you're getting back from the SQL.

So now I can get a grid of Unique Manufs from all my 1000s of Inventory records. Big Deal. Now what?
Screen Shot 2022-02-12 at 4.14.06 PM.png
Screen Shot 2022-02-12 at 4.14.06 PM.png (73.34 KiB) Viewed 4985 times
You'd think you tap a record to start a process - then you can do something with this. But HOLD ON - that won't work.

When you run a Process with an "Input", the 1st thing that process does is read that Input BO from the db, based on the ID pointer that was passed. In this case, since the list of Manufs doesn't really exists, there is no record to read. So you CAN click on a record, and have a Process start, but still nothing to do here. There are no values related to the record you clicked on available anywhere I can find.

Even using the "AwareApp.startProcess"-type functions won't help.

SO, while we generally say that you can't get "DISTINCT" or Unique rows from a BO, thats not quite true.
This shows you CAN get a list as a result of custom SQL, but you just can't do anything with it.

MORE WEIRDNESS
OK, lets say instead of only having a Unique Manuf name in the file, you ALSO had the ID # of the Inventory record that ordered the most product from that Manuf (or Vendor). (I'm saying that you have more complicated SQL that returns more than just the ManufName.) Now, knowing that the ID is associated to an Inventory BO instance, you could tap on a Unique Manuf grid line, call a AwareApp.startProcess2 that passed in "Inventory" and the ID #, and then bring up that Inventory record.
(Of course to do this, you'd need the nifty trick shown by Robin in Portugal (user's conference) to trap a click on a grid column and execute the AwareApp.startProcess2)

If you can think of any additional ways to use EXEC_SQL, please contribute!
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Post Reply