Inspired by BobK and his post about EXEC_SQL, even though he wasn't referring to this at all.
So here is a Query: And the text version:
I have a BO to receive this data. "NP_Manuf" It has 1 field in it: "Manuf"
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
(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? 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.
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!