https://netsuiteprofessionals.com logo
Join Slack
Powered by
I have a query using a concatenation of type and s...
# general
g
I have a query using a concatenation of type and status running in a UE script.
Copy code
(transaction.type || ':' || transaction.status) IN ('SalesOrd:B', 'SalesOrd:D', 'SalesOrd:E', 'SalesOrd:F', 'RtnAuth:F')
I was asked to run a test with some filters removed, which I did in the browser console to avoid making a script file update. I got no results back. I ran it in the NetSuite Script Debugger and got no results back. I then ran the query in SQuirreL SQL and got no results back. So I tested it in the User Event script again exactly the same as the Debugger/Console/SQuirreL tests. I got results (as expected). Changing the concatenation I was able to get results in the Debugger, the Console and SQuirreL:
Copy code
transaction.status IN ('SalesOrd:B', 'SalesOrd:D', 'SalesOrd:E', 'SalesOrd:F', 'RtnAuth:F')
I thought maybe I misunderstood something when I initially created the UE Script, so I modified it to use the method that worked in the Debugger/Console/SQuirreL and I got NO results back! SO the User Event script behaves how I would expect, but the others do not... Very odd. Has anyone else seen this behavior?
c
Remove the part of the where clause so that you list everything, and add 
transaction.type || ':' || transaction.status
to the select list and see what it comes out as in the cases where it doesn't work. Then let us know what it pesents itself as
g
@CD I have done that--I was getting the expected results of values such as "SalesOrd:B". I get the same results for that concatenation (when added to my select list) for both Debugger/Console/SQuirreL and in the User Event script (logged to the debugger).
Copy code
SELECT DISTINCT
	type,
	status,
	type || ':' || status concat
FROM
	transaction
WHERE
	status IN ('SalesOrd:B', 'SalesOrd:D', 'SalesOrd:E', 'SalesOrd:F', 'RtnAuth:F')
Running from the Console Produces:
Copy code
type	status	concat
SalesOrd	D	SalesOrd:D
SalesOrd	E	SalesOrd:E
SalesOrd	B	SalesOrd:B
SalesOrd	F	SalesOrd:F
If I run the same simplified query in my User Event script, the results are the same. There must be a joined table causing some sort of difference in the UE Script vs the Debugger...but I'm out of time to play with it for today.
I had some time to play again this morning and have some new information. In the User Event Script my query is actually a subquery, so it's really more like this:
Copy code
SELECT * FROM (
	SELECT ROWNUM row, tranid, type, status
	FROM transaction
	WHERE (type || ':' || status) IN ('SalesOrd:B', 'SalesOrd:D', 'SalesOrd:E', 'SalesOrd:F', 'RtnAuth:F')
) WHERE row BETWEEN 1 AND 5000
ORDER BY row
This gives the expected results...but changing the concatenation of type and status to status alone returns no results. Honestly, this is how I would expect it to work, so I'm mostly interested in how NetSuite interprets status when not run in a subquery.
3 Views
Open in Slack
PreviousNext
Powered by