https://netsuiteprofessionals.com logo
Join Slack
Powered by
I am challenged when creating a UNION query using ...
# suiteql
r
I am challenged when creating a UNION query using 2 common table expressions (cte) 1 for open invoice and 2 for related credits (both run fine on their own) When I select all from just one of the 2 ctes, I get a perfect result. When I Select cte_1.* UNION Select cte_2.* (not my exact syntax), It returns 'Invalid search type: on 1 of the 2 ctes. Not a great explanation, but does this ring a bell with anyone?
c
When I last encountered this i had to name the individual columns from both CTEs so that they had the same name. 
*
is not the way to go
Might not be the problem though
r
Much appreciated. They do have the exact same names, however, I didn't list them for the sake of brevity.
s
I've also been completely unable to UNION two ctes in NS. The same sort of stuff that I did for years in MSSQL with no problem.
r
Agreed. I keep thinking about how easy this is to do in MSSQL. There are other ways to get this done, but I really WANTED to do it with ctes.
c
Make sure you're not trying to UNION any longtext columns or similar, i recall them giving me grief too
I'd probably try with just the internalid as a starting point
r
I hadn't checked whether there are any such columns, but I will. Thank you!
s
Copy code
WITH
companies AS (
	SELECT id, externalid FROM entity WHERE isperson = 'F'
),
people AS (
	SELECT id, externalid FROM entity WHERE isperson = 'T'
)
SELECT id, externalid FROM (
	SELECT id, externalid FROM companies UNION
	SELECT id, externalid FROM people
)
👍 1
🙌 1
💯 1
s
also, try UNION ALL instead of UNION. UNION by itself discards duplicate rows, so the duplicate detection code could be causing issues. UNION ALL will leave duplicate rows alone, and will often perform better
👍 1
r
Much appreciated, all. @Shai Coleman FTW.
👍 1
7 Views
Open in Slack
PreviousNext
Powered by