Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

enhancment to union - keyword multiunion - to simplify the query [CORE2643] #3050

Open
firebird-automations opened this issue Sep 27, 2009 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @livius2

i have some concept to enhance union

real word example:
sometimes data are divided between tables for count of data optimalization.
For example data are stored in tables SALES_2000 SALES_2001 SALES_2002 ...
sometimes are in month tables like SALES_2000_01, SALES_2000_02, SALE_2000_03 ...
may by i sometimes in day tables or hours

and now when we need to select composite report from two or more tables like this
we write

SELECT ID, DATA, XXXX
FROM SALES_2000_01
UNION
SELECT ID, DATA, XXXX
FROM SALES_2000_02
UNION
SELECT ID, DATA, XXXX
FROM SALES_2000_03

.....

of course this is simple example but add here where clause and some calculations like
SELECT ID, DATA, XXXX, (SELECT YYY FORM AAAA) ....
FROM SALES_2000_01
WHERE
EXISTS ...

then query is very very big text to analise by parser and is worst to readable

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

my proposition is like this

SELECT ID, DATA, XXXX
MULTIUNION FROM SALES_2000_01, SALES_2000_02, SALES_2000_03 ...
WHERE
...

of course you can name this different not MULTIUNION and syntax can be changed this is only proposition
and also may be this is impossible to addapt to server engine

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

description: i have some concept to enhance union

real word example:
sometime data are divided between tables for count of data optimalization.
For example data are stored in tables SALES_2000 SALES_2001 SALES_2002 ...
sometimes are in month tables like SALES_2000_01, SALES_2000_02, SALE_2000_03 ...
may by i some times in day tables or hours

and now when we need to select composite report from two or more tables like this
we write

SELECT ID, DATA, XXXX
FROM SALES_2000_01
UNION
SELECT ID, DATA, XXXX
FROM SALES_2000_02
UNION
SELECT ID, DATA, XXXX
FROM SALES_2000_03

.....

of course this is simple example but add here where clause and some calculations like
SELECT ID, DATA, XXXX, (SELECT YYY FORM AAAA) ....
FROM SALES_2000_01
WHERE
EXISTS ...

then query is very very big text to analise by parser and is worst to readable

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

my proposition is like this

SELECT ID, DATA, XXXX
MULTIUNION FROM SALES_2000_01, SALES_2000_02, SALES_2000_03 ...
WHERE
...

of course you can name this different not MULTIUNION and syntax can be changed this is only proposition

=>

i have some concept to enhance union

real word example:
sometimes data are divided between tables for count of data optimalization.
For example data are stored in tables SALES_2000 SALES_2001 SALES_2002 ...
sometimes are in month tables like SALES_2000_01, SALES_2000_02, SALE_2000_03 ...
may by i sometimes in day tables or hours

and now when we need to select composite report from two or more tables like this
we write

SELECT ID, DATA, XXXX
FROM SALES_2000_01
UNION
SELECT ID, DATA, XXXX
FROM SALES_2000_02
UNION
SELECT ID, DATA, XXXX
FROM SALES_2000_03

.....

of course this is simple example but add here where clause and some calculations like
SELECT ID, DATA, XXXX, (SELECT YYY FORM AAAA) ....
FROM SALES_2000_01
WHERE
EXISTS ...

then query is very very big text to analise by parser and is worst to readable

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

my proposition is like this

SELECT ID, DATA, XXXX
MULTIUNION FROM SALES_2000_01, SALES_2000_02, SALES_2000_03 ...
WHERE
...

of course you can name this different not MULTIUNION and syntax can be changed this is only proposition

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

description: i have some concept to enhance union

real word example:
sometimes data are divided between tables for count of data optimalization.
For example data are stored in tables SALES_2000 SALES_2001 SALES_2002 ...
sometimes are in month tables like SALES_2000_01, SALES_2000_02, SALE_2000_03 ...
may by i sometimes in day tables or hours

and now when we need to select composite report from two or more tables like this
we write

SELECT ID, DATA, XXXX
FROM SALES_2000_01
UNION
SELECT ID, DATA, XXXX
FROM SALES_2000_02
UNION
SELECT ID, DATA, XXXX
FROM SALES_2000_03

.....

of course this is simple example but add here where clause and some calculations like
SELECT ID, DATA, XXXX, (SELECT YYY FORM AAAA) ....
FROM SALES_2000_01
WHERE
EXISTS ...

then query is very very big text to analise by parser and is worst to readable

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

my proposition is like this

SELECT ID, DATA, XXXX
MULTIUNION FROM SALES_2000_01, SALES_2000_02, SALES_2000_03 ...
WHERE
...

of course you can name this different not MULTIUNION and syntax can be changed this is only proposition

=>

i have some concept to enhance union

real word example:
sometimes data are divided between tables for count of data optimalization.
For example data are stored in tables SALES_2000 SALES_2001 SALES_2002 ...
sometimes are in month tables like SALES_2000_01, SALES_2000_02, SALE_2000_03 ...
may by i sometimes in day tables or hours

and now when we need to select composite report from two or more tables like this
we write

SELECT ID, DATA, XXXX
FROM SALES_2000_01
UNION
SELECT ID, DATA, XXXX
FROM SALES_2000_02
UNION
SELECT ID, DATA, XXXX
FROM SALES_2000_03

.....

of course this is simple example but add here where clause and some calculations like
SELECT ID, DATA, XXXX, (SELECT YYY FORM AAAA) ....
FROM SALES_2000_01
WHERE
EXISTS ...

then query is very very big text to analise by parser and is worst to readable

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

my proposition is like this

SELECT ID, DATA, XXXX
MULTIUNION FROM SALES_2000_01, SALES_2000_02, SALES_2000_03 ...
WHERE
...

of course you can name this different not MULTIUNION and syntax can be changed this is only proposition
and also may be this is impossible to addapt to server engine

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

I don't see any benefit to this feature.

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

And since it is not specified by the SQL standards, I'd say it would be unwise to introduce.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

>>Sean Leyne - [28/Sep/09 01:50 AM ]
>>I don't see any benefit to this feature.

try construct query to 10 or more tables with big select fields, computing .. and big where clause extended filters and subqueries
you must put this query (n) times but with multiunion this query will be very simple

>>And since it is not specified by the SQL standards, I'd say it would be unwise to introduce.

i suppose that many extensions in Firebird are not present in SQL standard
but users are very happy that exists

this is only proposition you choose put it in Firebird or not
i can only think about new features (better or worser) and put it here for acceptance or not ;-)

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 Initial [ 10301 ] =>

Version: 3.0 Alpha 1 [ 10331 ] =>

Version: 3.0 Beta 1 [ 10332 ] =>

Version: 3.0.0 [ 10048 ] =>

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant