Re: [firebird-support] order by

2014-04-07 Thread Andrea Raimondi 
I don't think so.

And anyway, you should never be using an order by number - unless there is
really no way around it, in my opinion.

A


On Mon, Apr 7, 2014 at 11:47 AM, checkmail  wrote:

>
>
> Hello,
>
>
>
> I've a litte question. Now I can order by the column number, order by 1.
> Can I set this information with a variable?
>
> Dim k as integer = 1;
>
> Order by :k?
>
>
>
> Thanks
>
>
>
>
>
>
> 
>



-- 
Mr. Andrea Raimondi
Senior Software Analyst&Developer

Re: [firebird-support] order by

2014-04-07 Thread Tim Ward 

On 07/04/2014 12:40, Andrea Raimondi wrote:


And anyway, you should never be using an order by number - unless 
there is really no way around it, in my opinion.
When people make statements like that it would be really helpful if they 
were to say *why* they are of this opinion, otherwise the reader doesn't 
gain anything.


--
Tim Ward

Re: [firebird-support] order by

2014-04-07 Thread Andrea Raimondi 
I am of this opinion because SQL scripts change and what is today column
number 2 may become column number 14 two days later.
This, in turn, leads to potential problems because you think it's ordering
by something whereas instead it's ordering by something else.

A


On Mon, Apr 7, 2014 at 2:58 PM, Tim Ward  wrote:

>
>
> On 07/04/2014 12:40, Andrea Raimondi wrote:
>
>
>
>  And anyway, you should never be using an order by number - unless there
> is really no way around it, in my opinion.
>
>  When people make statements like that it would be really helpful if they
> were to say *why* they are of this opinion, otherwise the reader doesn't
> gain anything.
>
> --
> Tim Ward
>
>
>
> 
>



-- 
Mr. Andrea Raimondi
Senior Software Analyst&Developer

Re: [firebird-support] order by

2014-04-07 Thread Tim Ward 
Thanks. Knowing this reasoning, one can then use one's judgement in 
comparing this reason against a desire to avoid repeating a long complex 
expression which could also lead to a maintenance risk.


On 07/04/2014 15:02, Andrea Raimondi wrote:
I am of this opinion because SQL scripts change and what is today 
column number 2 may become column number 14 two days later.
This, in turn, leads to potential problems because you think it's 
ordering by something whereas instead it's ordering by something else.


A


On Mon, Apr 7, 2014 at 2:58 PM, Tim Ward > wrote:




On 07/04/2014 12:40, Andrea Raimondi wrote:


And anyway, you should never be using an order by number - unless
there is really no way around it, in my opinion.

When people make statements like that it would be really helpful
if they were to say *why* they are of this opinion, otherwise the
reader doesn't gain anything.

-- 
Tim Ward





--
Tim Ward

Re: [firebird-support] order by

2014-04-07 Thread Andrea Raimondi 
To avoid those cases, I will usually wrap the long and complex expression
in an outer select giving it an intuitive column name.
It does not work in *all* cases, but it does work in most.

A


On Mon, Apr 7, 2014 at 3:05 PM, Tim Ward  wrote:

>
>
> Thanks. Knowing this reasoning, one can then use one's judgement in
> comparing this reason against a desire to avoid repeating a long complex
> expression which could also lead to a maintenance risk.
>
>
> On 07/04/2014 15:02, Andrea Raimondi wrote:
>
>
>  I am of this opinion because SQL scripts change and what is today column
> number 2 may become column number 14 two days later.
>  This, in turn, leads to potential problems because you think it's
> ordering by something whereas instead it's ordering by something else.
>
>  A
>
>
> On Mon, Apr 7, 2014 at 2:58 PM, Tim Ward  wrote:
>
>>
>>
>> On 07/04/2014 12:40, Andrea Raimondi wrote:
>>
>>
>>
>>  And anyway, you should never be using an order by number - unless there
>> is really no way around it, in my opinion.
>>
>>  When people make statements like that it would be really helpful if
>> they were to say *why* they are of this opinion, otherwise the reader
>> doesn't gain anything.
>>
>> --
>> Tim Ward
>>
>>
>>
> --
> Tim Ward
>
>
>
> 
>



-- 
Mr. Andrea Raimondi
Senior Software Analyst&Developer

Re: [firebird-support] order by

2014-04-07 Thread Ann Harrison 
On Mon, Apr 7, 2014 at 6:47 AM, checkmail  wrote:

>
>
> I've a litte question. Now I can order by the column number, order by 1.
> Can I set this information with a variable?
>
> Dim k as integer = 1;
>
> Order by :k?
>
>
>
You can do the equivalent with an EXECUTE STATEMENT.With the exception
of EXECUTE {STATEMENT | BLOCK}, Firebird optimizes a statement the first
time it is presented.  Query optimization includes choosing how and when to
sort the input.  Optimizing once and executing many times is more efficient
than using EXECUTE STATEMENT which treats each new iteration as a totally
new statement.

Good luck,

Ann

P.S.  I agree with the person who discourage the use of column numbers in
order by clauses.  Referencing fields by name rather than position was a
huge step forward when going from primitive to relational databases.  I
hate to see that abstraction eliminated to save typing.

Re: [firebird-support] Order by

2015-10-06 Thread liviusliv...@poczta.onet.pl [firebird-support] 
Hi,

ORDER BY CASE USER_NAME WHEN ‘PAUL’ THEN 1 WHEN ‘JOHN’ THEN 2 .. END

regards,
Karol Bieniaszewski

From: mailto:firebird-support@yahoogroups.com 
Sent: Tuesday, October 06, 2015 5:20 PM
To: Firebird Support 
Subject: [firebird-support] Order by

  

Hello everyone

There is the possibility of ordering the result of a SELECT in an order 
specified by the user.
For example
having a field NAME
in the clause ORDER BY 'PAUL, JOHN, PETER, MARIA'

Of course, that should be all the options set



Thank you.


Hola a todos

Existe la posibilidad de ordenar el resultado de un SELECT en un order 
especificado por el usuario.
Por ejemplo
teniendo un campo NOMBRE
en la clausula ORDER BY 'PABLO, JUAN, PEDRO, MARIA'

Claro esta, que se deben tener todas las opciones establecidas

gracias.

Re: [firebird-support] Order by

2015-10-06 Thread Germán Balbi bal...@yahoo.com [firebird-support] 
Excellent, very simple. Thank Karol
 


 El Martes, 6 de octubre, 2015 16:32:08, "liviusliv...@poczta.onet.pl 
[firebird-support]"  escribió:
   

     Hi, ORDER BY CASE USER_NAME WHEN ‘PAUL’ THEN 1 WHEN ‘JOHN’ THEN 2 .. END 
regards,Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: 
Tuesday, October 06, 2015 5:20 PMTo: Firebird Support Subject: 
[firebird-support] Order by   Hello everyone There is the possibility of 
ordering the result of a SELECT in an order specified by the user.For 
examplehaving a field NAMEin the clause ORDER BY 'PAUL, JOHN, PETER, MARIA' Of 
course, that should be all the options set

Thank you.
Hola a todos Existe la posibilidad de ordenar el resultado de un SELECT en un 
order especificado por el usuario.Por ejemploteniendo un campo NOMBREen la 
clausula ORDER BY 'PABLO, JUAN, PEDRO, MARIA' Claro esta, que se deben tener 
todas las opciones establecidas gracias.  #yiv4720080945 #yiv4720080945 -- 
#yiv4720080945ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 
0;padding:0 10px;}#yiv4720080945 #yiv4720080945ygrp-mkp hr {border:1px solid 
#d8d8d8;}#yiv4720080945 #yiv4720080945ygrp-mkp #yiv4720080945hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv4720080945 #yiv4720080945ygrp-mkp #yiv4720080945ads 
{margin-bottom:10px;}#yiv4720080945 #yiv4720080945ygrp-mkp .yiv4720080945ad 
{padding:0 0;}#yiv4720080945 #yiv4720080945ygrp-mkp .yiv4720080945ad p 
{margin:0;}#yiv4720080945 #yiv4720080945ygrp-mkp .yiv4720080945ad a 
{color:#ff;text-decoration:none;}#yiv4720080945 #yiv4720080945ygrp-sponsor 
#yiv4720080945ygrp-lc {font-family:Arial;}#yiv4720080945 
#yiv4720080945ygrp-sponsor #yiv4720080945ygrp-lc #yiv4720080945hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4720080945 
#yiv4720080945ygrp-sponsor #yiv4720080945ygrp-lc .yiv4720080945ad 
{margin-bottom:10px;padding:0 0;}#yiv4720080945 #yiv4720080945actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4720080945 
#yiv4720080945activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4720080945
 #yiv4720080945activity span {font-weight:700;}#yiv4720080945 
#yiv4720080945activity span:first-child 
{text-transform:uppercase;}#yiv4720080945 #yiv4720080945activity span a 
{color:#5085b6;text-decoration:none;}#yiv4720080945 #yiv4720080945activity span 
span {color:#ff7900;}#yiv4720080945 #yiv4720080945activity span 
.yiv4720080945underline {text-decoration:underline;}#yiv4720080945 
.yiv4720080945attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv4720080945 .yiv4720080945attach div a 
{text-decoration:none;}#yiv4720080945 .yiv4720080945attach img 
{border:none;padding-right:5px;}#yiv4720080945 .yiv4720080945attach label 
{display:block;margin-bottom:5px;}#yiv4720080945 .yiv4720080945attach label a 
{text-decoration:none;}#yiv4720080945 blockquote {margin:0 0 0 
4px;}#yiv4720080945 .yiv4720080945bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv4720080945 
.yiv4720080945bold a {text-decoration:none;}#yiv4720080945 dd.yiv4720080945last 
p a {font-family:Verdana;font-weight:700;}#yiv4720080945 dd.yiv4720080945last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4720080945 
dd.yiv4720080945last p span.yiv4720080945yshortcuts 
{margin-right:0;}#yiv4720080945 div.yiv4720080945attach-table div div a 
{text-decoration:none;}#yiv4720080945 div.yiv4720080945attach-table 
{width:400px;}#yiv4720080945 div.yiv4720080945file-title a, #yiv4720080945 
div.yiv4720080945file-title a:active, #yiv4720080945 
div.yiv4720080945file-title a:hover, #yiv4720080945 div.yiv4720080945file-title 
a:visited {text-decoration:none;}#yiv4720080945 div.yiv4720080945photo-title a, 
#yiv4720080945 div.yiv4720080945photo-title a:active, #yiv4720080945 
div.yiv4720080945photo-title a:hover, #yiv4720080945 
div.yiv4720080945photo-title a:visited {text-decoration:none;}#yiv4720080945 
div#yiv4720080945ygrp-mlmsg #yiv4720080945ygrp-msg p a 
span.yiv4720080945yshortcuts 
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4720080945 
.yiv4720080945green {color:#628c2a;}#yiv4720080945 .yiv4720080945MsoNormal 
{margin:0 0 0 0;}#yiv4720080945 o {font-size:0;}#yiv4720080945 
#yiv4720080945photos div {float:left;width:72px;}#yiv4720080945 
#yiv4720080945photos div div {border:1px solid 
#66;height:62px;overflow:hidden;width:62px;}#yiv4720080945 
#yiv4720080945photos div label 
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv4720080945
 #yiv4720080945reco-category {font-size:77%;}#yiv4720080945 
#yiv4720080945reco-desc {font-size:77%;}#yiv4720080945 .yiv4720080945replbq 
{margin:4px;}#yiv4720080945 #yiv4720080945ygrp-actbar div a:first-child 
{margin-right:2px;padding-right:5px;}#yiv4720080945 #yiv4720080945ygrp-mlmsg 
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv472008

Re: [firebird-support] Order by

2015-10-06 Thread 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] 
Germán, as you can read Spanish, this article can be useful:

https://firebird21.wordpress.com/2014/11/20/mostrando-los-resultados-ordenados-por-cualquier-criterio/

Greetings.

Walter.


On Tue, Oct 6, 2015 at 6:17 PM, Germán Balbi bal...@yahoo.com
[firebird-support]  wrote:

>
>
> Excellent, very simple. Thank Karol
>
>
>
>
> El Martes, 6 de octubre, 2015 16:32:08, "liviusliv...@poczta.onet.pl
> [firebird-support]"  escribió:
>
>
>
> Hi,
>
> ORDER BY CASE USER_NAME WHEN ‘PAUL’ THEN 1 WHEN ‘JOHN’ THEN 2 .. END
>
> regards,
> Karol Bieniaszewski
>
> *From:* mailto:firebird-support@yahoogroups.com
> 
> *Sent:* Tuesday, October 06, 2015 5:20 PM
> *To:* Firebird Support 
> *Subject:* [firebird-support] Order by
>
>
> Hello everyone
>
> There is the possibility of ordering the result of a SELECT in an order
> specified by the user.
> For example
> having a field NAME
> in the clause ORDER BY 'PAUL, JOHN, PETER, MARIA'
>
> Of course, that should be all the options set
>
> Thank you.
>
> Hola a todos
>
> Existe la posibilidad de ordenar el resultado de un SELECT en un order
> especificado por el usuario.
> Por ejemplo
> teniendo un campo NOMBRE
> en la clausula ORDER BY 'PABLO, JUAN, PEDRO, MARIA'
>
> Claro esta, que se deben tener todas las opciones establecidas
>
> gracias.
>
>
> 
>

Re: [firebird-support] Order by

2015-10-07 Thread Germán Balbi bal...@yahoo.com [firebird-support] 
Walter, conosco tu pagina me encanta, siempre compartiendo información de los 
problemas cotidianos.En general es el primer lugar a consultar, esta vez no fue 
así. Te pido disculpas . no volverá a pasar.Para tu tranquilidad al recivir la 
respuesta Karol, lo primero que hice fue buscar "ORDER BY CASE" y adivina a 
donde fui a caer. Si a Firebird21.
Muchas gracias por tu dedicación.
Te saludo desde Buenos AiresGermán 
 


 El Martes, 6 de octubre, 2015 21:02:31, "'Walter R. Ojeda Valiente' 
sistemas2000profesio...@gmail.com [firebird-support]" 
 escribió:
   

    

 Germán, as you can read Spanish, this article can be useful:
https://firebird21.wordpress.com/2014/11/20/mostrando-los-resultados-ordenados-por-cualquier-criterio/

Greetings.
Walter.

On Tue, Oct 6, 2015 at 6:17 PM, Germán Balbi bal...@yahoo.com 
[firebird-support]  wrote:

     Excellent, very simple. Thank Karol
 


 El Martes, 6 de octubre, 2015 16:32:08, "liviusliv...@poczta.onet.pl 
[firebird-support]"  escribió:
   

     Hi, ORDER BY CASE USER_NAME WHEN ‘PAUL’ THEN 1 WHEN ‘JOHN’ THEN 2 .. END 
regards,Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: 
Tuesday, October 06, 2015 5:20 PMTo: Firebird Support Subject: 
[firebird-support] Order by   Hello everyone There is the possibility of 
ordering the result of a SELECT in an order specified by the user.For 
examplehaving a field NAMEin the clause ORDER BY 'PAUL, JOHN, PETER, MARIA' Of 
course, that should be all the options set

Thank you.
Hola a todos Existe la posibilidad de ordenar el resultado de un SELECT en un 
order especificado por el usuario.Por ejemploteniendo un campo NOMBREen la 
clausula ORDER BY 'PABLO, JUAN, PEDRO, MARIA' Claro esta, que se deben tener 
todas las opciones establecidas gracias.  

  

  #yiv9527526986 -- #yiv9527526986ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9527526986 
#yiv9527526986ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9527526986 
#yiv9527526986ygrp-mkp #yiv9527526986hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv9527526986 #yiv9527526986ygrp-mkp #yiv9527526986ads 
{margin-bottom:10px;}#yiv9527526986 #yiv9527526986ygrp-mkp .yiv9527526986ad 
{padding:0 0;}#yiv9527526986 #yiv9527526986ygrp-mkp .yiv9527526986ad p 
{margin:0;}#yiv9527526986 #yiv9527526986ygrp-mkp .yiv9527526986ad a 
{color:#ff;text-decoration:none;}#yiv9527526986 #yiv9527526986ygrp-sponsor 
#yiv9527526986ygrp-lc {font-family:Arial;}#yiv9527526986 
#yiv9527526986ygrp-sponsor #yiv9527526986ygrp-lc #yiv9527526986hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9527526986 
#yiv9527526986ygrp-sponsor #yiv9527526986ygrp-lc .yiv9527526986ad 
{margin-bottom:10px;padding:0 0;}#yiv9527526986 #yiv9527526986actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9527526986 
#yiv9527526986activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9527526986
 #yiv9527526986activity span {font-weight:700;}#yiv9527526986 
#yiv9527526986activity span:first-child 
{text-transform:uppercase;}#yiv9527526986 #yiv9527526986activity span a 
{color:#5085b6;text-decoration:none;}#yiv9527526986 #yiv9527526986activity span 
span {color:#ff7900;}#yiv9527526986 #yiv9527526986activity span 
.yiv9527526986underline {text-decoration:underline;}#yiv9527526986 
.yiv9527526986attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv9527526986 .yiv9527526986attach div a 
{text-decoration:none;}#yiv9527526986 .yiv9527526986attach img 
{border:none;padding-right:5px;}#yiv9527526986 .yiv9527526986attach label 
{display:block;margin-bottom:5px;}#yiv9527526986 .yiv9527526986attach label a 
{text-decoration:none;}#yiv9527526986 blockquote {margin:0 0 0 
4px;}#yiv9527526986 .yiv9527526986bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv9527526986 
.yiv9527526986bold a {text-decoration:none;}#yiv9527526986 dd.yiv9527526986last 
p a {font-family:Verdana;font-weight:700;}#yiv9527526986 dd.yiv9527526986last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9527526986 
dd.yiv9527526986last p span.yiv9527526986yshortcuts 
{margin-right:0;}#yiv9527526986 div.yiv9527526986attach-table div div a 
{text-decoration:none;}#yiv9527526986 div.yiv9527526986attach-table 
{width:400px;}#yiv9527526986 div.yiv9527526986file-title a, #yiv9527526986 
div.yiv9527526986file-title a:active, #yiv9527526986 
div.yiv9527526986file-title a:hover, #yiv9527526986 div.yiv9527526986file-title 
a:visited {text-decoration:none;}#yiv9527526986 div.yiv9527526986photo-title a, 
#yiv9527526986 div.yiv9527526986photo-title a:active, #yiv9527526986 
div.yiv9527526986photo-title a:hover, #yiv9527526986 
div.yiv9527526986photo-title a:visited {text-decoration:none;}#yiv9527526986 
div#yiv9527526986ygrp-mlmsg #yiv9527526986ygrp-msg p a 
span.yiv9527526986yshortcuts 
{font-family:Verdana;font-size:10px;fon

Re: [firebird-support] Order by

2015-10-07 Thread 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] 
Ok Germán.

Just for your advice, we have a forum too, you can ask your questions there
in Spanish, Portuguese or English. It is about Firebird, SQL and database
design. Usually you receive an answer in the next 24 hours.

http://yoforeo.com/firebird21

Greetings.

Walter.




On Wed, Oct 7, 2015 at 8:32 AM, Germán Balbi bal...@yahoo.com
[firebird-support]  wrote:

>
>
> Walter, conosco tu pagina me encanta, siempre compartiendo información de
> los problemas cotidianos.
> En general es el primer lugar a consultar, esta vez no fue así. Te pido
> disculpas [image: *;) guiño]. no volverá a pasar.
> Para tu tranquilidad al recivir la respuesta Karol, lo primero que hice
> fue buscar "ORDER BY CASE" y adivina a donde fui a caer. Si a Firebird21.
>
> Muchas gracias por tu dedicación.
> Te saludo desde Buenos Aires
> Germán
>
>
>
>
> El Martes, 6 de octubre, 2015 21:02:31, "'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support]" <
> firebird-support@yahoogroups.com> escribió:
>
>
>
>
>
> Germán, as you can read Spanish, this article can be useful:
>
>
> https://firebird21.wordpress.com/2014/11/20/mostrando-los-resultados-ordenados-por-cualquier-criterio/
>
> Greetings.
>
> Walter.
>
>
> On Tue, Oct 6, 2015 at 6:17 PM, Germán Balbi bal...@yahoo.com
> [firebird-support]  wrote:
>
>
> Excellent, very simple. Thank Karol
>
>
>
>
> El Martes, 6 de octubre, 2015 16:32:08, "liviusliv...@poczta.onet.pl
> [firebird-support]"  escribió:
>
>
>
> Hi,
>
> ORDER BY CASE USER_NAME WHEN ‘PAUL’ THEN 1 WHEN ‘JOHN’ THEN 2 .. END
>
> regards,
> Karol Bieniaszewski
>
> *From:* mailto:firebird-support@yahoogroups.com
> 
> *Sent:* Tuesday, October 06, 2015 5:20 PM
> *To:* Firebird Support 
> *Subject:* [firebird-support] Order by
>
>
> Hello everyone
>
> There is the possibility of ordering the result of a SELECT in an order
> specified by the user.
> For example
> having a field NAME
> in the clause ORDER BY 'PAUL, JOHN, PETER, MARIA'
>
> Of course, that should be all the options set
>
> Thank you.
>
> Hola a todos
>
> Existe la posibilidad de ordenar el resultado de un SELECT en un order
> especificado por el usuario.
> Por ejemplo
> teniendo un campo NOMBRE
> en la clausula ORDER BY 'PABLO, JUAN, PEDRO, MARIA'
>
> Claro esta, que se deben tener todas las opciones establecidas
>
> gracias.
>
>
>
>
>
> 
>

RE: [firebird-support] order by with grouping?

2013-01-24 Thread Leyne, Sean 
Kelly,

> always a great place to get help!!
> 
> here's what i have data wise:
> 
> Tag ID Date
> 
> ABC 11 2012
> DEF 11 2011
> GHJ 11 2010
> HHH 22 2012
> ZZZ 22 2011
> AAA 22 2010
> AAK 33 2012
> AAD 44 2012
> YYY 44 2010

> 
> My desire is to still keep the "groups" of ID together
> 
> i'd LIKE
> 
> AAA22
> HHH22
> ZZZ22
> AAD44
> YYY44
> AAK33
> ABC11
> DEF11
> GHJ11
> 
> but
> select * order by tag, date group by ID doesn't work

First, your expected output and your SELECT don't align.  Where is the Date 
column?

Second, stop using SELECT * very bad habit.  Ok, for debugging/testing, bad for 
production.


> NOTE i do NOT want to sort by ID.  I just want the list sorted by Tag,
> subsorted by date.  BUT I want all the IDs together because the Tag might
> change from date to date for ID

Your post/requirements are contradictory:

- "My desire is to still keep the "groups" of ID together"

- "NOTE i do NOT want to sort by ID"

If you want them "together", they need to be sorted


- "I just want the list sorted by Tag, subsorted by date."

Which is it, do you want the ID together or the Tags by date?


Sean

Re: [firebird-support] ORDER BY too slow

2019-03-21 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Hiyes, slowdown is because of sort as engine must sort the table first and then 
retrieve 10 records.But you can try to speed this up by:SELECT  FIRST 10 SKIP 0 
 msg.OBJ_GUID AS "MSG_GUID", msg.PRTY, msg.TTL,  pst.OBJ_GUID AS "PST_GUID", 
pst.MSTB_DTSFROM  (Select * from MSGS m order by m.PRTY) msg  JOIN MSG_PSTS pst 
ON msg.OBJ_GUID = pst.MSG_GUID  JOIN MSG_USRS meu ON msg.OBJ_GUID = 
meu.MSG_GUID  JOIN USRS usr ON msg.USR_GUID = usr.OBJ_GUIDWHERE  meu.USR_GUID = 
'12A61B0FAE3046B6AEDEEDF6B4FE0E78'ORDER BY  msg.PRTY, pst.MSTB  Please include 
results: timing and fetching stats. I am interested self in the results. I 
suppose that this should be faster then your oryginal query.Regards,Karol 
Bieniaszewski
null

RE: [firebird-support] order by takes too long

2020-03-02 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
HiWithout analysis i can only advice to create descending index.Create 
descending index ixd_mytable__timestamp on mytable(timestamp)Regards,Karol 
Bieniaszewski
null

Re: [firebird-support] order by takes too long

2020-03-03 Thread Matthias Winkler spmm...@gmail.com [firebird-support] 
Hello,

it seems the problem is related to the VIEW. This is what I also figured
out so far. Now I read:

"If you see a *NATURAL *plan going against a big table, you've found the
problem. If you have where clause or JOIN to that table, make sure you have
index defined on related fields. If you do have index, but it isn't used,
perhaps you have ascending index (default) and you need descending (or vice
versa). Or perhaps you just need to rebuild the index statistics so that
Firebird finds it usable. That can be done with SET STATISTICS sql command.

If you use views with unions (you cannot index a view), I highly recommend
you use at least Firebird 2.0 as earlier versions don't use any indexes of
underlying tables when you use WHERE or JOIN with a view. If you can't use
Firebird 2.0, the only way to speed it up is to write a stored procedure
that takes value in WHERE clause as agrument."

from http://www.firebirdfaq.org/faq13/

I am using firebird 2.1.7 and can confirm that the problematic table has
the keyowrd "NATURAL" in the plan analyzer.
According to the text indexes should work within views with FB > 2.0.

I also added indexes DESC and ASC for any field of the tables which is used
in where statements.

Any idea how to proceed from here?

greetings

Matthias




On Tue, Mar 3, 2020 at 7:32 AM liviuslivius liviusliv...@poczta.onet.pl
[firebird-support]  wrote:

>
>
> Hi
>
> Without analysis i can only advice to create descending index.
> Create descending index ixd_mytable__timestamp on mytable(timestamp)
>
> Regards,
> Karol Bieniaszewski
>
> 
>

Re: [firebird-support] order by takes too long

2020-03-03 Thread Matthias Winkler spmm...@gmail.com [firebird-support] 
Hello,

I already re-calculated the indexes. But as far as I understood is that as
soon
as the natural keyword is present the indexes are not used at all.

The question is when and how decides the generator
of the "plan" that it connot use the indexes

Matthias







On Tue, Mar 3, 2020 at 1:13 PM 'Mathias Pannier (unitel)'
pann...@ubsysteme.de [firebird-support] 
wrote:

>
>
> Can You check the selectivity of the index? Perhaps You could recalculate
> that selectivity: http://www.firebirdfaq.org/faq167/
>
>
>
> *Von:* firebird-support@yahoogroups.com [mailto:
> firebird-support@yahoogroups.com]
> *Gesendet:* Dienstag, 3. März 2020 12:41
> *An:* firebird-support@yahoogroups.com
> *Betreff:* Re: [firebird-support] order by takes too long
>
>
>
>
>
> Hello,
>
>
>
> it seems the problem is related to the VIEW. This is what I also figured
> out so far.. Now I read:
>
>
>
> "If you see a *NATURAL *plan going against a big table, you've found the
> problem. If you have where clause or JOIN to that table, make sure you
> have index defined on related fields. If you do have index, but it isn't
> used, perhaps you have ascending index (default) and you need descending
> (or vice versa). Or perhaps you just need to rebuild the index statistics
> so that Firebird finds it usable. That can be done with SET STATISTICS sql
> command.
>
>
> If you use views with unions (you cannot index a view), I highly recommend
> you use at least Firebird 2.0 as earlier versions don't use any indexes of
> underlying tables when you use WHERE or JOIN with a view. If you can't use
> Firebird 2.0, the only way to speed it up is to write a stored procedure
> that takes value in WHERE clause as agrument."
>
>
>
> from http://www.firebirdfaq.org/faq13/
>
>
>
> I am using firebird 2.1.7 and can confirm that the problematic table has
> the keyowrd "NATURAL" in the plan analyzer.
>
> According to the text indexes should work within views with FB > 2.0.
>
>
>
> I also added indexes DESC and ASC for any field of the tables which is
> used in where statements.
>
>
>
> Any idea how to proceed from here?
>
>
>
> greetings
>
>
>
> Matthias
>
>
>
>
>
>
>
>
>
> On Tue, Mar 3, 2020 at 7:32 AM liviuslivius liviusliv...@poczta.onet.pl
> [firebird-support]  wrote:
>
>
>
> Hi
>
>
>
> Without analysis i can only advice to create descending index.
>
> Create descending index ixd_mytable__timestamp on mytable(timestamp)
>
>
>
> Regards,
>
> Karol Bieniaszewski
>
>
>
> ub.unitel GmbH, Schulstraße 16, 06792 Sandersdorf-Brehna
> Geschaeftsfuehrung Klaus Richter, Olaf Meyer
> Amtsgericht Stendal
> HRB 26389 FA Bitterfeld Steuernr. 116/107/08597 Ust.identNr. DE815796778
> Deutsche Bank IBAN DE53 86070024 0 6143234 00
> Kreissparkasse Anhalt-Bitterfeld IBAN DE69 80053722 0 3050326 82
> _
> Dieses E-Mail ist nur für den Empfänger bestimmt, an den es gerichtet
> ist und kann vertrauliches bzw. unter das Berufsgeheimnis fallendes
> Material enthalten. Jegliche darin enthaltene Ansicht oder Meinungs-
> äußerung ist die des Autors und stellt nicht notwendigerweise die
> Ansicht oder Meinung von ub.unitel GmbH dar.
> Sind Sie nicht der Empfänger, so haben Sie diese E-Mail irrtümlich
> erhalten und jegliche Verwendung, Veröffentlichung, Weiterleitung,
> Abschrift oder jeglicher Druck dieser E-Mail ist strengstens untersagt.
> _
>
> 
>

Re: [firebird-support] order by takes too long

2020-03-03 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
03.03.2020 13:23, Matthias Winkler spmm...@gmail.com [firebird-support] wrote:
> The question is when and how decides the generator
> of the "plan" that it connot use the indexes

  http://www.ibase.ru/dataaccesspaths/


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] order by 1, 2 - fb 32990

2018-04-12 Thread Helen Borrie hele...@iinet.net.au [firebird-support] 
acgomes2...@yahoo.com.br wrote:

> select

> A.Codigo

> 'DINHEIRO' FORMA_PAGTO

> FROM NF A

> where A.dataEmissao between '01.01.2018' and '31.01.2018'

>  and A.Modelo = '55'

This should work fine if you place a comma after A.Codigo.  It does
not make any sense to ORDER BY 2 (nor ORDER BY 1,2) as your literal
field FORMA_PAGTO has the same value in every row.  So:

 select
 A.Codigo,   /* << */
 'DINHEIRO' FORMA_PAGTO
 FROM NF A
 where A.dataEmissao between '01.01.2018' and '31.01.2018'
  and A.Modelo = '55'
 order by 1

HB

Re: [firebird-support] order by 1, 2 - fb 32990

2018-04-13 Thread acgomes2...@yahoo.com.br [firebird-support] 
hi helen... the comma is the less problem... 

 A correct sql:
 

 select N.DTEMISSAONOTA as DATA,
'DINHEIRO' as MEIO_PAGAMENTO1,
cast('DINHEIRO' as char(10)) as MEIO_PAGAMENTO2,
'00' as MEIO_PAGAMENTO,
N.VLRTOTALNOTA as VALOR,
0 as AUTENT_01,
0 as AUTENT_01_CALC,
N.AUTENTICACAO_NOTA as AUTENT_02,
3 as TIPO
 from NOTA N
 where (N.EMPRESANOTA = 1)
   and (N.DTEMISSAONOTA between '05/11/2018' and '04/11/2018')
   and (N.MODELONOTA = '55')
 order by 1, 2
 

 Error: 
 can't format message 13:587 -- message file D:\GDS_FBCLIENT\FB3\firebird.msg 
not found.
 conversion error from string "DINHEIRO".
 

 Of course, the index for the field "const" does not make much sense, the 
strange is the error.

 

 because the constant field will be the same value in all registers.

 

 Firebird: Server version: WI-V6.3.3.32900 Firebird 3.0

Re: [firebird-support] order by using parameter of stored procedure

2012-03-18 Thread Helen Borrie 
At 09:26 AM 19/03/2012, Newbie wrote:
>How to best implement ORDER BY of a query when the field by which to 
>order, is given as stored procedure input parameter?

It is not possible to that directly, as ORDER BY is a structural element that 
is pre-compiled.

>for example a typical product list where could be ordered by Name, Price 
>etc fields depending on what user clicks.
>
>I tried to include it into query:
>
>for
>select f1, f2 from t1
>order by :order_by_field
>into :p1, :p2
>
>I am not getting any errors, but it doesn't order the list. how to do it 
>better?

Use EXECUTE STATEMENT.  Look up the Language Reference Update document for your 
version of Firebird to see how this is achieved.  You should find it in the 
../doc/ subdirectory of your installation;  if not, you can download it from 
the Firebird website documentation area.

./heLen

Re: [firebird-support] order by using parameter of stored procedure

2012-03-19 Thread Mark Rotteveel 
On Sun, 18 Mar 2012 22:26:11 +0200, Newbie  wrote:
> How to best implement ORDER BY of a query when the field by which to 
> order, is given as stored procedure input parameter?
> for example a typical product list where could be ordered by Name, Price

> etc fields depending on what user clicks.
> 
> I tried to include it into query:
> 
> for
> select f1, f2 from t1
> order by :order_by_field
> into :p1, :p2
> 
> I am not getting any errors, but it doesn't order the list. how to do it

> better?
> thanks.

You can't. You don't get an error as you are now simply ordering by the
value of :order_by_field, which is the same for all records.

You might get away with using a selector variable to select the field:

ORDER BY CASE :selector WHEN 1 THEN field1 WHEN 2 THEN field2 ELSE field3
END

If this works I am not sure if it will perform well though.

Mark

Re: [firebird-support] order by using parameter of stored procedure

2012-03-20 Thread Tomasz Tyrakowski 
> How to best implement ORDER BY of a query when the field by which to
> order, is given as stored procedure input parameter?

If the data set returned by the procedure is not very large, return all 
relevant fields from the procedure and use order by in the query 
selecting from the procedure (it won't use indices, so it's only 
suitable for reasonably small number of records). Otherwise, I'd opt for 
EXECUTE STATEMENT, as Helen suggested.

regards
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] order by using parameter of stored procedure

2012-03-20 Thread Ann Harrison 
On Tue, Mar 20, 2012 at 12:48 PM, Tomasz Tyrakowski <
t.tyrakow...@sol-system.pl> wrote:


> > How to best implement ORDER BY of a query when the field by which to
> > order, is given as stored procedure input parameter?
>
> If the data set returned by the procedure is not very large, return all
> relevant fields from the procedure and use order by in the query
> selecting from the procedure (it won't use indices, so it's only
> suitable for reasonably small number of records). O


Returning the results and sorting them in the query is good advice,
regardless of the size of the result set.  In general, even though sort is
an NLogN operation and random access to a data page is a KN operation, the
size of K completely overwhelms the LogN.  Accessing data in storage order
then sorting in memory (generally) is faster than bouncing all over the
disk to find records in index order.  The exceptions are when data is
stored in index order or when you have a LIMIT n (First n) clause that
causes the query to return a small fraction of the records that would be
sorted.

Good luck,

Ann


[Non-text portions of this message have been removed]

Re: [firebird-support] Order By Not Working Using "WITH" Clause

2015-07-03 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On Fri, 3 Jul 2015 12:15:31 + (UTC), "Vishal Tiwari
vishuals...@yahoo.co.in [firebird-support]"
 wrote:
> Hi All,
> I have below SQL, which is concatenating the ShortCode column data, but
> without ordering as per the ORDER_NUMBER column in ABC table in "WITH"
> clause.
> Please help.
> 
> With TBL_SHORT_CODE (SHORT_CODE, FK_KEY) As (SELECT
> Distinct(XYZ.SHORT_CODE) As SHORT_CODE, ABC.FK_KEY From ABC Join XYZ On
> ABC.PK_KEY = XYZ.FK_KEYwhere XYZ.FK_KEY =
> '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1Order By
> ABC.ORDER_NUMBER)
> 
> SELECT LIST(Distinct(TBL_SHORT_CODE.SHORT_CODE), '' ), ABC.FK_BOMFrom
> ABC Join XYZ ON ABC.FK_KEY = XYZ.PK_KEY Join TBL_SHORT_CODE On
> TBL_SHORT_CODE.FK_KEY = ABC.FK_KEYwhere ABC.FK_BOM =
> '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1Group By
> ABC.FK_BOM

It doesn't work because of your use of distinct in LIST. This forces a
sort which overrides any previous order. I don't think there is a solution
to this problem except ineffecient and convoluted double querying.

Mark

Re: [firebird-support] Order By Not Working Using "WITH" Clause

2015-07-03 Thread Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] 
Even if I don't use Distinct, i don't get expected result. 


 On Friday, 3 July 2015 7:17 PM, "Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support]"  wrote:
   

     On Fri, 3 Jul 2015 12:15:31 + (UTC), "Vishal Tiwari
vishuals...@yahoo.co.in [firebird-support]"
 wrote:
> Hi All,
> I have below SQL, which is concatenating the ShortCode column data, but
> without ordering as per the ORDER_NUMBER column in ABC table in "WITH"
> clause.
> Please help.
> 
> With TBL_SHORT_CODE (SHORT_CODE, FK_KEY) As (SELECT
> Distinct(XYZ.SHORT_CODE) As SHORT_CODE, ABC.FK_KEY From ABC Join XYZ On
> ABC.PK_KEY = XYZ.FK_KEYwhere XYZ.FK_KEY =
> '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1Order By
> ABC.ORDER_NUMBER)
> 
> SELECT LIST(Distinct(TBL_SHORT_CODE.SHORT_CODE), '' ), ABC.FK_BOMFrom
> ABC Join XYZ ON ABC.FK_KEY = XYZ.PK_KEY Join TBL_SHORT_CODE On
> TBL_SHORT_CODE.FK_KEY = ABC.FK_KEYwhere ABC.FK_BOM =
> '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1Group By
> ABC.FK_BOM

It doesn't work because of your use of distinct in LIST. This forces a
sort which overrides any previous order. I don't think there is a solution
to this problem except ineffecient and convoluted double querying.

Mark
  #yiv0671293864 #yiv0671293864 -- #yiv0671293864ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0671293864 
#yiv0671293864ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0671293864 
#yiv0671293864ygrp-mkp #yiv0671293864hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv0671293864 #yiv0671293864ygrp-mkp #yiv0671293864ads 
{margin-bottom:10px;}#yiv0671293864 #yiv0671293864ygrp-mkp .yiv0671293864ad 
{padding:0 0;}#yiv0671293864 #yiv0671293864ygrp-mkp .yiv0671293864ad p 
{margin:0;}#yiv0671293864 #yiv0671293864ygrp-mkp .yiv0671293864ad a 
{color:#ff;text-decoration:none;}#yiv0671293864 #yiv0671293864ygrp-sponsor 
#yiv0671293864ygrp-lc {font-family:Arial;}#yiv0671293864 
#yiv0671293864ygrp-sponsor #yiv0671293864ygrp-lc #yiv0671293864hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0671293864 
#yiv0671293864ygrp-sponsor #yiv0671293864ygrp-lc .yiv0671293864ad 
{margin-bottom:10px;padding:0 0;}#yiv0671293864 #yiv0671293864actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0671293864 
#yiv0671293864activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0671293864
 #yiv0671293864activity span {font-weight:700;}#yiv0671293864 
#yiv0671293864activity span:first-child 
{text-transform:uppercase;}#yiv0671293864 #yiv0671293864activity span a 
{color:#5085b6;text-decoration:none;}#yiv0671293864 #yiv0671293864activity span 
span {color:#ff7900;}#yiv0671293864 #yiv0671293864activity span 
.yiv0671293864underline {text-decoration:underline;}#yiv0671293864 
.yiv0671293864attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv0671293864 .yiv0671293864attach div a 
{text-decoration:none;}#yiv0671293864 .yiv0671293864attach img 
{border:none;padding-right:5px;}#yiv0671293864 .yiv0671293864attach label 
{display:block;margin-bottom:5px;}#yiv0671293864 .yiv0671293864attach label a 
{text-decoration:none;}#yiv0671293864 blockquote {margin:0 0 0 
4px;}#yiv0671293864 .yiv0671293864bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv0671293864 
.yiv0671293864bold a {text-decoration:none;}#yiv0671293864 dd.yiv0671293864last 
p a {font-family:Verdana;font-weight:700;}#yiv0671293864 dd.yiv0671293864last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0671293864 
dd.yiv0671293864last p span.yiv0671293864yshortcuts 
{margin-right:0;}#yiv0671293864 div.yiv0671293864attach-table div div a 
{text-decoration:none;}#yiv0671293864 div.yiv0671293864attach-table 
{width:400px;}#yiv0671293864 div.yiv0671293864file-title a, #yiv0671293864 
div.yiv0671293864file-title a:active, #yiv0671293864 
div.yiv0671293864file-title a:hover, #yiv0671293864 div.yiv0671293864file-title 
a:visited {text-decoration:none;}#yiv0671293864 div.yiv0671293864photo-title a, 
#yiv0671293864 div.yiv0671293864photo-title a:active, #yiv0671293864 
div.yiv0671293864photo-title a:hover, #yiv0671293864 
div.yiv0671293864photo-title a:visited {text-decoration:none;}#yiv0671293864 
div#yiv0671293864ygrp-mlmsg #yiv0671293864ygrp-msg p a 
span.yiv0671293864yshortcuts 
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0671293864 
.yiv0671293864green {color:#628c2a;}#yiv0671293864 .yiv0671293864MsoNormal 
{margin:0 0 0 0;}#yiv0671293864 o {font-size:0;}#yiv0671293864 
#yiv0671293864photos div {float:left;width:72px;}#yiv0671293864 
#yiv0671293864photos div div {border:1px solid 
#66;height:62px;overflow:hidden;width:62px;}#yiv0671293864 
#yiv0671293864photos div label 
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0671293864
 #yiv0671293864reco-category {font-size:77%;}#yiv06712

Re: [firebird-support] Order By Not Working Using "WITH" Clause

2015-07-03 Thread 'Mark Rotteveel' m...@lawinegevaar.nl [firebird-support] 
Ok, then there are also other grouping or sorting effects involved. 
Unfortunately there is no way to enforce a specific order. The CTE trick you 
use is just that: a trick and it doesn't always work.
So until Firebird supports a list(... Order by...), there is no way to always 
get a deterministic order.

Mark

- Reply message -
Van: "Vishal Tiwari vishuals...@yahoo.co.in [firebird-support]" 

Aan: "firebird-support@yahoogroups.com" 
Onderwerp: [firebird-support] Order By Not Working Using "WITH" Clause
Datum: vr, jul. 3, 2015 17:00

Even if I don't use Distinct, i don't get expected result.



On Friday, 3 July 2015 7:17 PM, "Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support]"  wrote:














On Fri, 3 Jul 2015 12:15:31 + (UTC), "Vishal Tiwari
vishuals...@yahoo.co.in [firebird-support]"
 wrote:
> Hi All,
> I have below SQL, which is concatenating the ShortCode column data, but
> without ordering as per the ORDER_NUMBER column in ABC table in "WITH"
> clause.
> Please help.
> 
> With TBL_SHORT_CODE (SHORT_CODE, FK_KEY) As (SELECT
> Distinct(XYZ.SHORT_CODE) As SHORT_CODE, ABC.FK_KEY From ABC Join XYZ On
> ABC.PK_KEY = XYZ.FK_KEYwhere XYZ.FK_KEY =
> '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1Order By
> ABC.ORDER_NUMBER)
> 
> SELECT LIST(Distinct(TBL_SHORT_CODE.SHORT_CODE), '' ), ABC.FK_BOMFrom
> ABC Join XYZ ON ABC.FK_KEY = XYZ.PK_KEY Join TBL_SHORT_CODE On
> TBL_SHORT_CODE.FK_KEY = ABC.FK_KEYwhere ABC.FK_BOM =
> '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1Group By
> ABC.FK_BOM

It doesn't work because of your use of distinct in LIST. This forces a
sort which overrides any previous order. I don't think there is a solution
to this problem except ineffecient and convoluted double querying.

Mark

RE: [firebird-support] Order By Not Working Using "WITH" Clause

2015-07-03 Thread Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] 
>Hi All,
>
>I have below SQL, which is concatenating the ShortCode column data, but 
>without ordering as per the ORDER_NUMBER column in ABC table in "WITH" clause.
>
>Please help.
>
>With TBL_SHORT_CODE (SHORT_CODE, FK_KEY) As 
>(
>SELECT Distinct(XYZ.SHORT_CODE) As SHORT_CODE, ABC.FK_KEY 
>From ABC Join XYZ On ABC.PK_KEY = XYZ.FK_KEY
>where XYZ.FK_KEY = '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And 
>ABC.STATUS_CODE = 1
>Order By ABC.ORDER_NUMBER
>)
>
>SELECT LIST(Distinct(TBL_SHORT_CODE.SHORT_CODE), '' ), ABC.FK_BOM
>From ABC 
>Join XYZ ON ABC.FK_KEY = XYZ.PK_KEY 
>Join TBL_SHORT_CODE On TBL_SHORT_CODE.FK_KEY = ABC.FK_KEY
>where ABC.FK_BOM = '{009DA0F 8-51EE-4207-86A6-7E18F96B983A}' And 
>ABC.STATUS_CODE = 1
>Group By ABC.FK_BOM

I've had a similar problem once, Vishal. Then I think I solved it by using 
EXECUTE BLOCK. You could try something similar:

EXECUTE BLOCK RETURNS (SHORT_CODES VARCHAR(2000), FK_BOM INTEGER
AS 
  DECLARE VARIABLE SHORT_CODE VARCHAR(20);
  DECLARE VARIABLE FK_BOM2 INTEGER;
  DECLARE VARIABLE DUMMY INTEGER;
BEGIN
  FK_BOM = NULL;
  FOR With TBL_SHORT_CODE (SHORT_CODE, FK_KEY, ORDER_NUMBER) As 
  (SELECT XYZ.SHORT_CODE, ABC.FK_KEY, min(ABC.ORDER_NUMBER)
   From ABC Join XYZ On ABC.PK_KEY = XYZ.FK_KEY
   where XYZ.FK_KEY = '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And 
ABC.STATUS_CODE = 1
   group by 1, 2)

  SELECT ABC.FK_BOM, tsc.SHORT_CODE, min(tsc.ORDER_NUMBER)
  From ABC 
  Join XYZ ON ABC.FK_KEY = XYZ.PK_KEY 
  Join TBL_SHORT_CODE tsc On tsc.FK_KEY = ABC.FK_KEY
  where ABC.FK_BOM = '{009DA0F 8-51EE-4207-86A6-7E18F96B983A}' And 
ABC.STATUS_CODE = 1
  Group By 1, 2
  ORDER BY 1, 3
  into :FK_BOM2, :SHORT_CODE, :DUMMY do
  begin
if (FK_BOM2 > FK_BOM) then
  suspend;
if (FK_BOM2 is distinct from FK_BOM) then
begin
  FK_BOM = FK_BOM2;
  SHORT_CODES = '';
end
SHORT_CODES = SHORT_CODES ||', '||SHORT_CODE;
  end
  suspend;
end

This should order the SHORT_CODES by the lowest ORDER_NUMBER within each 
FK_BOM. To also order the FK_BOMs by the lowest ORDER_NUMBER amongst themselves 
would add more complexity, hopefully you don't need that.
 
HTH,
Set

Re: [firebird-support] Order By Not Working Using "WITH" Clause

2015-07-06 Thread Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] 
Hi SET,
You have done it this time too
Your SQL worked with a small amount of change in below statement
    DECLARE VARIABLE FK_BOM2 VARCHAR(2000);

Because FK_BOM2 is of GUID type.
You are awesome as usual.
I would like to say Thank You from bottom of HEART 

Thanks Once Again SET... You Are my ROCKSTAR.

Thanking You And With Best Regards.
Vishal

 


 On Friday, 3 July 2015 11:33 PM, "Svein Erling Tysvær 
svein.erling.tysv...@kreftregisteret.no [firebird-support]" 
 wrote:
   

     >Hi All,
>
>I have below SQL, which is concatenating the ShortCode column data, but 
>without ordering as per the ORDER_NUMBER column in ABC table in "WITH" clause.
>
>Please help.
>
>With TBL_SHORT_CODE (SHORT_CODE, FK_KEY) As 
>(
>SELECT Distinct(XYZ.SHORT_CODE) As SHORT_CODE, ABC.FK_KEY 
>From ABC Join XYZ On ABC.PK_KEY = XYZ.FK_KEY
>where XYZ.FK_KEY = '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And 
>ABC.STATUS_CODE = 1
>Order By ABC.ORDER_NUMBER
>)
>
>SELECT LIST(Distinct(TBL_SHORT_CODE.SHORT_CODE), '' ), ABC.FK_BOM
>From ABC 
>Join XYZ ON ABC.FK_KEY = XYZ.PK_KEY 
>Join TBL_SHORT_CODE On TBL_SHORT_CODE.FK_KEY = ABC.FK_KEY
>where ABC.FK_BOM = '{009DA0F 8-51EE-4207-86A6-7E18F96B983A}' And 
>ABC.STATUS_CODE = 1
>Group By ABC.FK_BOM

I've had a similar problem once, Vishal. Then I think I solved it by using 
EXECUTE BLOCK. You could try something similar:

EXECUTE BLOCK RETURNS (SHORT_CODES VARCHAR(2000), FK_BOM INTEGER
AS 
 DECLARE VARIABLE SHORT_CODE VARCHAR(20);
 DECLARE VARIABLE FK_BOM2 INTEGER;
 DECLARE VARIABLE DUMMY INTEGER;
BEGIN
 FK_BOM = NULL;
 FOR With TBL_SHORT_CODE (SHORT_CODE, FK_KEY, ORDER_NUMBER) As 
 (SELECT XYZ.SHORT_CODE, ABC.FK_KEY, min(ABC.ORDER_NUMBER)
 From ABC Join XYZ On ABC.PK_KEY = XYZ.FK_KEY
 where XYZ.FK_KEY = '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And 
ABC.STATUS_CODE = 1
 group by 1, 2)

SELECT ABC.FK_BOM, tsc.SHORT_CODE, min(tsc.ORDER_NUMBER)
 From ABC 
 Join XYZ ON ABC.FK_KEY = XYZ.PK_KEY 
 Join TBL_SHORT_CODE tsc On tsc.FK_KEY = ABC.FK_KEY
 where ABC.FK_BOM = '{009DA0F 8-51EE-4207-86A6-7E18F96B983A}' And 
ABC.STATUS_CODE = 1
 Group By 1, 2
 ORDER BY 1, 3
 into :FK_BOM2, :SHORT_CODE, :DUMMY do
 begin
 if (FK_BOM2 > FK_BOM) then
 suspend;
 if (FK_BOM2 is distinct from FK_BOM) then
 begin
 FK_BOM = FK_BOM2;
 SHORT_CODES = '';
 end
 SHORT_CODES = SHORT_CODES ||', '||SHORT_CODE;
 end
 suspend;
end

This should order the SHORT_CODES by the lowest ORDER_NUMBER within each 
FK_BOM. To also order the FK_BOMs by the lowest ORDER_NUMBER amongst themselves 
would add more complexity, hopefully you don't need that.
 
HTH,
Set  #yiv3332182013 #yiv3332182013 -- #yiv3332182013ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3332182013 
#yiv3332182013ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3332182013 
#yiv3332182013ygrp-mkp #yiv3332182013hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv3332182013 #yiv3332182013ygrp-mkp #yiv3332182013ads 
{margin-bottom:10px;}#yiv3332182013 #yiv3332182013ygrp-mkp .yiv3332182013ad 
{padding:0 0;}#yiv3332182013 #yiv3332182013ygrp-mkp .yiv3332182013ad p 
{margin:0;}#yiv3332182013 #yiv3332182013ygrp-mkp .yiv3332182013ad a 
{color:#ff;text-decoration:none;}#yiv3332182013 #yiv3332182013ygrp-sponsor 
#yiv3332182013ygrp-lc {font-family:Arial;}#yiv3332182013 
#yiv3332182013ygrp-sponsor #yiv3332182013ygrp-lc #yiv3332182013hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3332182013 
#yiv3332182013ygrp-sponsor #yiv3332182013ygrp-lc .yiv3332182013ad 
{margin-bottom:10px;padding:0 0;}#yiv3332182013 #yiv3332182013actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3332182013 
#yiv3332182013activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3332182013
 #yiv3332182013activity span {font-weight:700;}#yiv3332182013 
#yiv3332182013activity span:first-child 
{text-transform:uppercase;}#yiv3332182013 #yiv3332182013activity span a 
{color:#5085b6;text-decoration:none;}#yiv3332182013 #yiv3332182013activity span 
span {color:#ff7900;}#yiv3332182013 #yiv3332182013activity span 
.yiv3332182013underline {text-decoration:underline;}#yiv3332182013 
.yiv3332182013attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv3332182013 .yiv3332182013attach div a 
{text-decoration:none;}#yiv3332182013 .yiv3332182013attach img 
{border:none;padding-right:5px;}#yiv3332182013 .yiv3332182013attach label 
{display:block;margin-bottom:5px;}#yiv3332182013 .yiv3332182013attach label a 
{text-decoration:none;}#yiv3332182013 blockquote {margin:0 0 0 
4px;}#yiv3332182013 .yiv3332182013bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv3332182013 
.yiv3332182013bold a {text-decoration:none;}#yiv3332182013 dd.yiv3332182013last 
p a {font-family:Verdana;font-weight:700;}#yiv3332182013 dd.yiv3332182013last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3332182013 
dd.yiv333

Re: [firebird-support] Order by :variable in a Stored Procedure

2015-11-03 Thread 'Mark Rotteveel' m...@lawinegevaar.nl [firebird-support] 
If you pass a variable into an order by, it will sort on that *value*, and as 
that is the same for all rows, it is essentially a no-op. You can only do this 
by either sourcing the order by using a case to specify the column from a 
parameter, or by dynamically constructing the query, and executing it with 
execute statement, with risk for SQL injection.
Mark

- Reply message -
Van: "Gugui gugui_sarubi_mac...@yahoo.com.br [firebird-support]" 

Aan: 
Onderwerp: [firebird-support] Order by :variable in a Stored Procedure
Datum: di, nov. 3, 2015 19:58

Hi all,
is it possible to pass a variable into a Order by ?

I did it and the StoredProcedure was created without errors
but the variable passed into an 'order by' was ignored and firebird 
assumed the primary key order instead

any tip ?

thanks








++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links



https://info.yahoo.com/legal/us/yahoo/utos/terms/