If you have questions or if you want to share your opinion about Aware IM post your message on this forum
#52097 by Stetson
Fri Nov 15, 2019 7:36 pm
Looking for high level direction. I have a PICK FROM query. I have a 10-parameter (SQL Server) stored procedure called by EXEC_SP. The 10 parameters are to hold (up to) 10 instances of column 1 of the PICK FROM query results. How do I reference the picked rows (instance prefixes?) in order to use them in the EXEC_SP call?

The EXEC_SP call works fine. The PICK FROM query works fine. I'm just unclear how best to grab the PICK FROM values to pass. Is there a better way to do this rather than by using a PICK FROM query, since there's only ONE EXEC_SP call?

Thanks for any suggestions.
#52098 by Jaymer
Fri Nov 15, 2019 9:59 pm
whatever instances are selected from PICK ONE OR MORE will be in context.

Lets say you show a list of 50 States. You tell the user "Pick up to 10 States".
( PICK ONE OR MORE FROM States )

immediately after that statement, the selected States will be in context.
How about:
Code: Select allLISU.tmp_States = UNDEFINED ............. LISU = LoggedInSystemUser
BuildStateString ....... this calls a process of this name
LISU.tmp_States = ..... some code to strip off trailing , or leave it and handle in SP
EXEC_SP .... and send it 1 string with the 10 IDs instead of 10 fields


Process: BuildStateString (input BO: States)
Code: Select allLISU.tmp_States = LISU.tmp_States + States.ID + ','



There's prolly a way to do it with a WHILE loop also.
Either way you have to loop thru 10 in Aware, or parse the string to extract the 10 in SQL (whichever is easiest based on available functions)

You can't use LIST_LINE (or any LISTxxx functions) because you don't have 10 instances in a list, like LISU.pm_SelectedStates
Seems overkill to insert the States into a list, just to use LIST_LINE to build a single list out of them.
But you could if you wanted to:
In addition to creating tmp_States in the RegUser table, create: pm_States, type States (or whatever that PickFrom is coming from), Multiple
Then right after the PICK ONE OR MORE,
Code: Select allINSERT States in LoggedInSystemUser.pm_States

Now you'll have all those instances available under the User Object. List_Line will work.
(Better read this on LIST_LINE. Its quirky and the examples are bad in Docs)
Code: Select allLISU.tmp_States = LIST_LINE.....


thats just a couple of ways - i'm sure there are more, but not much activity on the Forum anymore, esp. on the weekend.
Last edited by Jaymer on Fri Nov 15, 2019 10:03 pm, edited 1 time in total.
#52099 by Jaymer
Fri Nov 15, 2019 10:02 pm
And if you wanted to do it with the 10 fields, you'll need some loop & a counter, to use in an IF statement...

assume you're looping through the list of n values.
ex: If counter=1 then field1 = theID
if counter=2, etc etc

hardcoded and not elegant.

Who is online

Users browsing this forum: Google [Bot] and 28 guests