Re: [firebird-support] where condition

[Thomas Beckmann thomas.beckm...@assfinet.de [firebird-support]]

and coalesce(d.bsnr, -1) = coalesce((select ag from
p_getmaxag(a.teilenr,:typ,1)),d.bsnr,-1)

seem to be a possible, but probably not very performant approach.

Doing a left join with the procedure in case the stuff before your
leading "and" holds true could be a solution...

Thomas


Am 14.08.2014 15:49, schrieb 'checkmail' check_m...@satron.de
[firebird-support]:
>  
> 
> This works:
> 
> and (d.bsnr = coalesce((select ag from
> p_getmaxag(a.teilenr,:typ,1)),d.bsnr))
> 
> but how can I get the records without a bsnr, without an workstep, no
> record in the table d.bsnr..)
> 
>  
> 
> Thanks
> 
> 
> 
> Hello,
> 
>  
> 
> 1st, the following condition does not work unfortunately. (call a stored
> procedure in the where-condition in a coalesce..)
> 
> and d.bsnr = coalesce(select ag from p_getmaxag(:teilenr,:typ,1),d.bsnr)
> and d.bsnr < coalesce (select ag from p_getmaxag(:teilenr,:typ,2),100)
> 
>  
> 
> I have some article, several with worksteps, other without this.
> 
>  
> 
> For example
> 
> ArticleA =lamp
> 
> ArticleB  Workstep2= bicycle mill-cut
> 
> ArticleB  Workstep2= bicycle varnish
> 
>  
> 
> Now I have a stored procedure where I can get the entire stock, input
> parameter the type (material (no workstep), unfinish products (workstep
> < the last workstep) = typ2 and finished products (last workstep = typ 3
> input parameter)
> 
> If the input typ = 1, I should get all material, typ = 2 all unfinished
> products…
> 
>  
> 
> And this I will write in a sql where condition, preferably without an
> execute statement. IIF and Case do not work in the condition, neither
> the procedure call. (p_getmaxag)
> 
>  
> 
> How can I realize this simply?
> 
>  
> 
> Thanks in advance
> 
>  
> 
> Best regards
> 
>  
> 
> Olaf
> 
>  
> 
>  
> 
> 

-- 
Mit freundlichen Grüßen,

Thomas Beckmann
Diplom-Informatiker


Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckm...@assfinet.de 

ASSFINET-Logo

*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
i...@assfinet.de  • www.assfinet.de


Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
nicht gestattet.







++

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/

AW: [firebird-support] where condition

['checkmail' check_m...@satron.de [firebird-support]]

This works:

and (d.bsnr = coalesce((select ag from
p_getmaxag(a.teilenr,:typ,1)),d.bsnr))

but how can I get the records without a bsnr, without an workstep, no record
in the table d.bsnr..)

 

Thanks



Hello,

 

1st, the following condition does not work unfortunately. (call a stored
procedure in the where-condition in a coalesce..)

and d.bsnr = coalesce(select ag from p_getmaxag(:teilenr,:typ,1),d.bsnr) and
d.bsnr < coalesce (select ag from p_getmaxag(:teilenr,:typ,2),100)

 

I have some article, several with worksteps, other without this.

 

For example

ArticleA =lamp

ArticleB  Workstep2= bicycle mill-cut

ArticleB  Workstep2= bicycle varnish

 

Now I have a stored procedure where I can get the entire stock, input
parameter the type (material (no workstep), unfinish products (workstep <
the last workstep) = typ2 and finished products (last workstep = typ 3 input
parameter)

If the input typ = 1, I should get all material, typ = 2 all unfinished
products.

 

And this I will write in a sql where condition, preferably without an
execute statement. IIF and Case do not work in the condition, neither the
procedure call. (p_getmaxag)

 

How can I realize this simply?

 

Thanks in advance

 

Best regards

 

Olaf

 

 

[firebird-support] where condition

['checkmail' check_m...@satron.de [firebird-support]]

Hello,

 

1st, the following condition does not work unfortunately. (call a stored
procedure in the where-condition in a coalesce..)

and d.bsnr = coalesce(select ag from p_getmaxag(:teilenr,:typ,1),d.bsnr) and
d.bsnr < coalesce (select ag from p_getmaxag(:teilenr,:typ,2),100)

 

I have some article, several with worksteps, other without this.

 

For example

ArticleA =lamp

ArticleB  Workstep2= bicycle mill-cut

ArticleB  Workstep2= bicycle varnish

 

Now I have a stored procedure where I can get the entire stock, input
parameter the type (material (no workstep), unfinish products (workstep <
the last workstep) = typ2 and finished products (last workstep = typ 3 input
parameter)

If the input typ = 1, I should get all material, typ = 2 all unfinished
products.

 

And this I will write in a sql where condition, preferably without an
execute statement. IIF and Case do not work in the condition, neither the
procedure call. (p_getmaxag)

 

How can I realize this simply?

 

Thanks in advance

 

Best regards

 

Olaf

 

 

Re: [firebird-support] Where condition by column number

[Leonardo M . Ramé]

On 2012-12-02 18:16:17 +0100, Thomas Steinmaurer wrote:
> 
> 
>>>> >> On Sat, Dec 1, 2012 at 8:09 AM, Leonardo M. Ramé
>>>> <[1][1][1]l.r...@griensu.com>
>>>> >> wrote:
>>>> >>
>>>> >> > Hi, I'm wondering if there's a way to refer columns by number in
>>>> WHERE
>>>> >> > conditions.
>>>> >> >
>>>> >> > I need this because I'm creating a generic method to create where
>>>> >> > clauses for hopefully ANY query, in FreePascal
>>>> >>
>>>> >> What you suggest, sir, is blasphemy. One of the major creeds of
>>>> relational
>>>> >> theory is the separation of the logical from the physical. Codd and
>all
>>>> >> his minions will strike you down.
>>>> >>
>>>> >> On the other hand, why not just use consistent aliases?
>>>> >>
>>>> >> select
>>>> >> case
>>>> >> when (a.IDADICIONAL is null) then 'TITULAR'
>>>> >> else 'ADICIONAL'
>>>> >> end as col1,
>>>> >> c.idcliente as col2, c.apellido as col3, c.nombres as col4
>>>> >> from clientes c
>>>> >> left join adicionales a on a.IDADICIONAL = c.IDCLIENTE
>>>> >>
>>>> >> >
>>>> >> >
>>>> >
>>>> > Ann, column name is not the problem. What I wanted to do is to use
>the
>>>> > alias in the WHERE clause, there's no solution for this in a simple
>>>> > select.
>>>>
>>>> You can use a derived table.
>>>>
>>>
>>> Yes Thomas, I ended up using a derived table.
>>>
>>> I have one doubt regarding derived tables. Imagine an inner query that
>>> returns millions of records, and the outer table with a where clause
>that
>>> limits the results to just a few records, how this affects to
>>> server performance?.
>>
>> I don't have an answer out-of-the box, but this can be easily checked by
>> inspecting the execution plan and I/O statistics.
> 
>Answering myself: The following derived table query on the primary key
>on a table with ~108.000 records results in one indexed read.
> 
>select * from (
>select
>id as id1
>from
>accommodation
>)
>where
>id1 = 29
> 
That's great! thank you.
-- 
Leonardo M. Ramé
http://leonardorame.blogspot.com

Re: [firebird-support] Where condition by column number

[Thomas Steinmaurer]

>>>  >> On Sat, Dec 1, 2012 at 8:09 AM, Leonardo M. Ramé
>>>  <[1][1]l.r...@griensu.com>
>>>  >> wrote:
>>>  >>
>>>  >> > Hi, I'm wondering if there's a way to refer columns by number in
>>>  WHERE
>>>  >> > conditions.
>>>  >> >
>>>  >> > I need this because I'm creating a generic method to create where
>>>  >> > clauses for hopefully ANY query, in FreePascal
>>>  >>
>>>  >> What you suggest, sir, is blasphemy. One of the major creeds of
>>>  relational
>>>  >> theory is the separation of the logical from the physical. Codd and 
>>> all
>>>  >> his minions will strike you down.
>>>  >>
>>>  >> On the other hand, why not just use consistent aliases?
>>>  >>
>>>  >> select
>>>  >> case
>>>  >> when (a.IDADICIONAL is null) then 'TITULAR'
>>>  >> else 'ADICIONAL'
>>>  >> end as col1,
>>>  >> c.idcliente as col2, c.apellido as col3, c.nombres as col4
>>>  >> from clientes c
>>>  >> left join adicionales a on a.IDADICIONAL = c.IDCLIENTE
>>>  >>
>>>  >> >
>>>  >> >
>>>  >
>>>  > Ann, column name is not the problem. What I wanted to do is to use 
>>> the
>>>  > alias in the WHERE clause, there's no solution for this in a simple
>>>  > select.
>>>
>>>  You can use a derived table.
>>>
>>
>> Yes Thomas, I ended up using a derived table.
>>
>> I have one doubt regarding derived tables. Imagine an inner query that
>> returns millions of records, and the outer table with a where clause that
>> limits the results to just a few records, how this affects to
>> server performance?.
>
> I don't have an answer out-of-the box, but this can be easily checked by
> inspecting the execution plan and I/O statistics.

Answering myself: The following derived table query on the primary key 
on a table with ~108.000 records results in one indexed read.

select * from (
   select
 id as id1
   from
 accommodation
)
where
   id1 = 29


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Re: [firebird-support] Where condition by column number

[Thomas Steinmaurer]

>> >> On Sat, Dec 1, 2012 at 8:09 AM, Leonardo M. Ramé
>> <[1][1]l.r...@griensu.com>
>> >> wrote:
>> >>
>> >> > Hi, I'm wondering if there's a way to refer columns by number in
>> WHERE
>> >> > conditions.
>> >> >
>> >> > I need this because I'm creating a generic method to create where
>> >> > clauses for hopefully ANY query, in FreePascal
>> >>
>> >> What you suggest, sir, is blasphemy. One of the major creeds of
>> relational
>> >> theory is the separation of the logical from the physical. Codd and 
>> all
>> >> his minions will strike you down.
>> >>
>> >> On the other hand, why not just use consistent aliases?
>> >>
>> >> select
>> >> case
>> >> when (a.IDADICIONAL is null) then 'TITULAR'
>> >> else 'ADICIONAL'
>> >> end as col1,
>> >> c.idcliente as col2, c.apellido as col3, c.nombres as col4
>> >> from clientes c
>> >> left join adicionales a on a.IDADICIONAL = c.IDCLIENTE
>> >>
>> >> >
>> >> >
>> >
>> > Ann, column name is not the problem. What I wanted to do is to use the
>> > alias in the WHERE clause, there's no solution for this in a simple
>> > select.
>>
>> You can use a derived table.
>>
>
> Yes Thomas, I ended up using a derived table.
>
> I have one doubt regarding derived tables. Imagine an inner query that
> returns millions of records, and the outer table with a where clause that
> limits the results to just a few records, how this affects to
> server performance?.

I don't have an answer out-of-the box, but this can be easily checked by 
inspecting the execution plan and I/O statistics.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Re: [firebird-support] Where condition by column number

[Leonardo M . Ramé]

On 2012-12-02 16:59:24 +0100, Thomas Steinmaurer wrote:
> 
> 
>>> On Sat, Dec 1, 2012 at 8:09 AM, Leonardo M. Ramé
><[1][1]l.r...@griensu.com>
>>> wrote:
>>>
>>> > Hi, I'm wondering if there's a way to refer columns by number in
>WHERE
>>> > conditions.
>>> >
>>> > I need this because I'm creating a generic method to create where
>>> > clauses for hopefully ANY query, in FreePascal
>>>
>>> What you suggest, sir, is blasphemy. One of the major creeds of
>relational
>>> theory is the separation of the logical from the physical. Codd and all
>>> his minions will strike you down.
>>>
>>> On the other hand, why not just use consistent aliases?
>>>
>>> select
>>> case
>>> when (a.IDADICIONAL is null) then 'TITULAR'
>>> else 'ADICIONAL'
>>> end as col1,
>>> c.idcliente as col2, c.apellido as col3, c.nombres as col4
>>> from clientes c
>>> left join adicionales a on a.IDADICIONAL = c.IDCLIENTE
>>>
>>> >
>>> >
>>
>> Ann, column name is not the problem. What I wanted to do is to use the
>> alias in the WHERE clause, there's no solution for this in a simple
>> select.
> 
>You can use a derived table.
> 

Yes Thomas, I ended up using a derived table.

I have one doubt regarding derived tables. Imagine an inner query that
returns millions of records, and the outer table with a where clause that
limits the results to just a few records, how this affects to
server performance?.



Regards,
-- 
Leonardo M. Ramé
http://leonardorame.blogspot.com

Re: [firebird-support] Where condition by column number

[Thomas Steinmaurer]

>> On Sat, Dec 1, 2012 at 8:09 AM, Leonardo M. Ramé <[1]l.r...@griensu.com>
>> wrote:
>>
>> > Hi, I'm wondering if there's a way to refer columns by number in WHERE
>> > conditions.
>> >
>> > I need this because I'm creating a generic method to create where
>> > clauses for hopefully ANY query, in FreePascal
>>
>> What you suggest, sir, is blasphemy. One of the major creeds of 
>> relational
>> theory is the separation of the logical from the physical. Codd and all
>> his minions will strike you down.
>>
>> On the other hand, why not just use consistent aliases?
>>
>> select
>> case
>> when (a.IDADICIONAL is null) then 'TITULAR'
>> else 'ADICIONAL'
>> end as col1,
>> c.idcliente as col2, c.apellido as col3, c.nombres as col4
>> from clientes c
>> left join adicionales a on a.IDADICIONAL = c.IDCLIENTE
>>
>> >
>> >
>
> Ann, column name is not the problem. What I wanted to do is to use the
> alias in the WHERE clause, there's no solution for this in a simple
> select.

You can use a derived table.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Re: [firebird-support] Where condition by column number

[Leonardo M . Ramé]

On 2012-12-01 14:14:50 -0500, Ann Harrison wrote:
> 
> 
>On Sat, Dec 1, 2012 at 8:09 AM, Leonardo M. Ramé <[1]l.r...@griensu.com>
>wrote:
> 
>> Hi, I'm wondering if there's a way to refer columns by number in WHERE
>> conditions.
>>
>> I need this because I'm creating a generic method to create where
>> clauses for hopefully ANY query, in FreePascal
> 
>What you suggest, sir, is blasphemy. One of the major creeds of relational
>theory is the separation of the logical from the physical. Codd and all
>his minions will strike you down.
> 
>On the other hand, why not just use consistent aliases?
> 
>select
>case
>when (a.IDADICIONAL is null) then 'TITULAR'
>else 'ADICIONAL'
>end as col1,
>c.idcliente as col2, c.apellido as col3, c.nombres as col4
>from clientes c
>left join adicionales a on a.IDADICIONAL = c.IDCLIENTE
> 
>>
>>

Ann, column name is not the problem. What I wanted to do is to use the
alias in the WHERE clause, there's no solution for this in a simple
select.


-- 
Leonardo M. Ramé
http://leonardorame.blogspot.com

Re: [firebird-support] Where condition by column number

[Ann Harrison]

On Sat, Dec 1, 2012 at 8:09 AM, Leonardo M. Ramé  wrote:

> Hi, I'm wondering if there's a way to refer columns by number in WHERE
> conditions.
>
> I need this because I'm creating a generic method to create where
> clauses for hopefully ANY query, in FreePascal


What you suggest, sir, is blasphemy.  One of the major creeds of relational
theory is the separation of  the logical from the physical.  Codd and all
his minions will strike you down.

On the other hand, why not just use consistent aliases?

select
  case
when (a.IDADICIONAL is null) then 'TITULAR'
else 'ADICIONAL'
  end as col1,
  c.idcliente as col2, c.apellido as col3, c.nombres as col4
from clientes c
left join adicionales a on a.IDADICIONAL = c.IDCLIENTE

>
>


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

RE: [firebird-support] Where condition by column number

[Svein Erling Tysvær]

>Hi, I'm wondering if there's a way to refer columns by number in WHERE 
>conditions.
>
>I need this because I'm creating a generic method to create where clauses 
>for hopefully ANY query, in FreePascal.
>
>I need this:
>
>select col1, col2, colN
>from ...
>join ...
>where
>  col1 = condition1, col2 = condition2
>
>But now I need to know the name of the column in advance, and some times 
>the column name does refer to a real column, but a CASE statement or a 
>COALESCE.

Hi Leonardo!

I'm not aware of any way to directly do this, but you could of course do 
similar things by encapsulating into common table expressions (CTEs):

WITH TMP1(col1, col2, colN) as
(select 
 from ...
 join ...)

SELECT * FROM TMP1
WHERE col1 = condition1 
  AND col2 = condition2

However, I'm uncertain how well this kind of usage will be able to use indexes 
(of course indexes will be used for JOIN, but not necessarily for WHERE).

HTH,
Set

Re: [firebird-support] Where condition by column number

[Leonardo M . Ramé]

On 2012-12-01 10:09:15 -0300, Leonardo M. Ramé wrote:
> 
> 
>Hi, I'm wondering if there's a way to refer columns by number in WHERE
>conditions.
> 
>I need this because I'm creating a generic method to create where
>clauses for hopefully ANY query, in FreePascal.
> 
>I need this:
> 
>select col1, col2, colN
>from ...
>join ...
>where
>col1 = condition1, col2 = condition2
> 
>But now I need to know the name of the column in advance, and some times
>the column name does refer to a real column, but a CASE statement or a
>COALESCE.
> 

Let me try to explain what kind of queries I'm working with:

For example:

select 
  case 
when (a.IDADICIONAL is null) then 'TITULAR'
else 'ADICIONAL'
  end as Tipo,
  c.idcliente, c.apellido, c.nombres
from clientes c
left join adicionales a on a.IDADICIONAL = c.IDCLIENTE

I would like to use the "Tipo" column in a where statement.

One way to do this is to create a View, but sometimes I can't alter the
DLL of some databases.

Another way, is to do this

select tipo, idcliente, apellido, nombres from 
(
  select 
case 
  when (a.IDADICIONAL is null) then 'TITULAR'
  else 'ADICIONAL'
end as Tipo,
c.idcliente, c.apellido, c.nombres
  from clientes c
  left join adicionales a on a.IDADICIONAL = c.IDCLIENTE
) as foo
where foo.tipo like 'AD%'

Here I don't know if the subquery will get ALL the records and then
apply a filter on it. What if the subquery contains millions of
records?.

-- 
Leonardo M. Ramé
http://leonardorame.blogspot.com

[firebird-support] Where condition by column number

[Leonardo M . Ramé]

Hi, I'm wondering if there's a way to refer columns by number in WHERE
conditions.

I need this because I'm creating a generic method to create where
clauses for hopefully ANY query, in FreePascal.

I need this:

select col1, col2, colN 
from ...
join ...
where
  col1 = condition1, col2 = condition2

But now I need to know the name of the column in advance, and some times
the column name does refer to a real column, but a CASE statement or a
COALESCE.

Regards,
-- 
Leonardo M. Ramé
http://leonardorame.blogspot.com