How to avoid Union/Union All?

Question

How to avoid Union/Union All?

Praxo

Mr or Mrs. 500

Points: 532
More actions
December 27, 2014 at 3:59 am

#293861

Hi All
I am working one SQL where I need to avoid Union. Following is my need. I know I can write SQL using Union All, however there is some limitation and need to avoid Union All. Please le me know if you have any ways to achieve this.
I have customer_table (Cust_id, Cust_name,....)
I have quote_table (Qt_id, Cust_id, Update_TS, Qt_details....)
I have sell_table (Sell_id, Cust_id, Update_TS, Sell_details....)
I need results as below in Update_TS- DESC order (Q - for Quote table record, S - for Sell table record)
Cust_id | Rec_id | Rec_type | Update_TS
1 | 1 | Q | datetime
1 | 10 | S | datetime
2 | 7 | Q | datetime
2 | 4 | Q | datetime
2 | 4 | S | datetime

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why can't you use UNION ALL? It would be the obvious way. There's alternatives, but they're complex

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Praxo Mr or Mrs. 500 Points: 532 More actions · Answer 2

Using Union All is limitation and hence the problem.

This SQL is build dynamically by front end and it has limitation wherein it is not supporting Union All (views, Stored procedure as well).

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Praxo (12/27/2014)
Using Union All is limitation and hence the problem.
This SQL is build dynamically by front end and it has limitation wherein it is not supporting Union All (views, Stored procedure as well).

Please explain what the limitation is for you because, as Gail said, UNION ALL is the right way to do this one. We need to know what the limitation is to see if there's a work around.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Praxo Mr or Mrs. 500 Points: 532 More actions · Answer 4

Limitation I was referring is from front end side, which as I mentioned generates the SQL dynamically. The front end is not having this framework and does not support creating SQLs with UNION ALL .

I understand the UNION ALL option is the right option in this case, however since front end does not support UNION ALL, we need to find some other alternative to get the desired results.

Let us know if there can be any other option.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Praxo (12/27/2014)
Limitation I was referring is from front end side, which as I mentioned generates the SQL dynamically. The front end is not having this framework and does not support creating SQLs with UNION ALL .
I understand the UNION ALL option is the right option in this case, however since front end does not support UNION ALL, we need to find some other alternative to get the desired results.
Let us know if there can be any other option.

There absolutely is. Stop building SQL code in the front end and use a stored procedure. 😉

It's actually difficult for me to believe that the front end can't produce or use a UNION ALL but I'm also not a front end programmer. Someone else will have to jump in here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Praxo (12/27/2014)
Limitation I was referring is from front end side, which as I mentioned generates the SQL dynamically. The front end is not having this framework and does not support creating SQLs with UNION ALL .

What else doesn't it support? If we don't know, this is going to turn into a guessing game where people suggest solutions and you then say they won't work either.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Praxo Mr or Mrs. 500 Points: 532 More actions · Answer 7

It doesn't support UNION ALL, Views, Stored procedures, temp tables. However, it does support all type of joins, sub queries.

I understand, this is annoying, but that where the issue is. Without these limitations, we could have used UNION ALL. But now we are compelled to find alternate approach/SQL.

thanks for responding.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

CROSS APPLY?

I'd be seriously considering throwing out such a restrictive framework tbh.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Jeff Moden (12/27/2014)
It's actually difficult for me to believe that the front end can't produce or use a UNION ALL

You'll probably find that the senior guru expert development lead decided to write his own inspired framework for generating database queries and now every project must use that framework because it's perfect and nothing else could approach that ideal work of art.

You'll probably also find that it's riddled with SQL injection vulnerabilities, implicit conversion problems and other strange behaviours.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 10

I dont understand why this cannot be a view ?

If you can SELECT from a table you can SELECT from a view.

Just make sure you ORDER BY on the SELECT not the view and job should be done.

But i do agree with the others, putting this sort of artificial constraint in place can only hurt you.

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

GilaMonster (12/28/2014)
Jeff Moden (12/27/2014)
It's actually difficult for me to believe that the front end can't produce or use a UNION ALL
You'll probably find that the senior guru expert development lead decided to write his own inspired framework for generating database queries and now every project must use that framework because it's perfect and nothing else could approach that ideal work of art.
You'll probably also find that it's riddled with SQL injection vulnerabilities, implicit conversion problems and other strange behaviours.

Agreed. But I've actually seen such restrictions as what the OP is seeing. From the list of restrictions the OP gave, I'd say that it's actually SQL Server CE. A previous company that I was working for made the same mistake. They wanted to create a "mobile app" and they chose CE, which I not so lovingly refer to as the "[font="Arial Black"]C[/font]rippled [font="Arial Black"]E[/font]dition".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

Dave Ballantyne (12/28/2014)
I dont understand why this cannot be a view ?
If you can SELECT from a table you can SELECT from a view.
Just make sure you ORDER BY on the SELECT not the view and job should be done.
But i do agree with the others, putting this sort of artificial constraint in place can only hurt you.

Like I said above, I don't believe it's an artificial constraint. I think they might be using SQL Server CE. It's truly "just a place to store data". A lot of people don't even know the edition exists. Not sure it was continued past 2008 or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

@Praxo,

Man, I feel for you. Those are some pretty heavy restrictions and they're not unlike the restrictions one will find in SQL Server CE (Compact Edition). You covered some of them but is there any way you could find out if that's the cause of the restrictions so that we know what all the restrictions are?

As for your current UNION ALL problem, if it is CE or something similar, the only way I know around the restrictions is to load the results of each SELECT into a common array and go from there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101099 More actions · Answer 14

A FULL OUTER JOIN should handle this rather easily. From what you stated, a FOJ should be available to you.

SELECT ca1.Cust_id, ca1.Rec_id, ca1.Rec_type, ca1.Update_TS

FROM quote_table q

FULL OUTER JOIN sell_table s ON 1 = 2

--CROSS APPLY used only to assign alias names (not to do a true tabular join)

CROSS APPLY (

SELECT

CASE WHEN q.cust_id IS NULL THEN 'S' ELSE 'Q' END AS Rec_type,

ISNULL(q.cust_id, s.cust_id) AS Cust_id,

ISNULL(q.qt_id, s.sell_id) AS Rec_id,

ISNULL(q.update_ts, s.update_ts) AS Update_TS

) AS ca1

ORDER BY ca1.Cust_id, ca1.Update_TS DESC

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".