Prev: WIndows Authentication Connection String
Next: Can I index this kind of view?
From: rafaelSC on 13 Jan 2010 16:22
select b.casoTipoId,b.origenId,b.codigoTipoId,b.codigoId
from bma.dbo.tblIEInstruccionItem a
join bma.dbo.tblIE01 b on
(b.casoTipoId in (select casoTipoId from BMA.dbo.fnCasoTipo(a.casoTipoId))
and
(b.origenId COLLATE Latin1_General_CI_AI=a.origen or a.origen is null) and
(b.codigoTipoId COLLATE Latin1_General_CI_AI=a.codigoTipoId or
a.codigoTipoId is null) and
(b.codigoId=a.codigoId or a.codigoId is null)
where a.rowInstruccionId=5081 and a.excluir=0
From: Plamen Ratchev on 13 Jan 2010 17:05
If on SQL Server 2005/2008 you can use the APPLY operator to invoke the function:
http://technet.microsoft.com/en-us/library/ms175156.aspx

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 13 Jan 2010 17:06
rafaelSC (rafaelSC(a)discussions.microsoft.com) writes:
> select b.casoTipoId,b.origenId,b.codigoTipoId,b.codigoId
> from bma.dbo.tblIEInstruccionItem a
> join bma.dbo.tblIE01 b on
> (b.casoTipoId in (select casoTipoId from
> BMA.dbo.fnCasoTipo(a.casoTipoId))
> and
> (b.origenId COLLATE Latin1_General_CI_AI=a.origen or a.origen is
> null) and
> (b.codigoTipoId COLLATE Latin1_General_CI_AI=a.codigoTipoId or
> a.codigoTipoId is null) and
> (b.codigoId=a.codigoId or a.codigoId is null)
> where a.rowInstruccionId=5081 and a.excluir=0
>

select b.casoTipoId,b.origenId,b.codigoTipoId,b.codigoId
from bma.dbo.tblIEInstruccionItem a
CROSS APPLY BMA.dbo.fnCasoTipo(a.casoTipoId) AS a1
join bma.dbo.tblIE01 b on b.casoTipoId = a1.casoTipoId
...

The CROSS APPLY operator permits you include a table expression that
refers to column from another column in the query.

The operator was added in SQL 2005, if you are on SQL 2000, you can't
do this.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: rafaelSC on 13 Jan 2010 17:45
Thanks a lot, Erland

"Erland Sommarskog" wrote:

> rafaelSC (rafaelSC(a)discussions.microsoft.com) writes:
> > select b.casoTipoId,b.origenId,b.codigoTipoId,b.codigoId
> > from bma.dbo.tblIEInstruccionItem a
> > join bma.dbo.tblIE01 b on
> > (b.casoTipoId in (select casoTipoId from
> > BMA.dbo.fnCasoTipo(a.casoTipoId))
> > and
> > (b.origenId COLLATE Latin1_General_CI_AI=a.origen or a.origen is
> > null) and
> > (b.codigoTipoId COLLATE Latin1_General_CI_AI=a.codigoTipoId or
> > a.codigoTipoId is null) and
> > (b.codigoId=a.codigoId or a.codigoId is null)
> > where a.rowInstruccionId=5081 and a.excluir=0
> >
>
> select b.casoTipoId,b.origenId,b.codigoTipoId,b.codigoId
> from bma.dbo.tblIEInstruccionItem a
> CROSS APPLY BMA.dbo.fnCasoTipo(a.casoTipoId) AS a1
> join bma.dbo.tblIE01 b on b.casoTipoId = a1.casoTipoId
> ...
>
> The CROSS APPLY operator permits you include a table expression that
> refers to column from another column in the query.
>
> The operator was added in SQL 2005, if you are on SQL 2000, you can't
> do this.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
From: --CELKO-- on 13 Jan 2010 21:21
The kludge is to use the proprietary APPLY. But this code screams bad
schema design -- NULLs and incorrect data element names like
"xxxx_type_id"; a data element cannot be BOTH a type and an
identifier.

if you want REAL help, post DDL and why are you using camelCase after
all the research?? You are in trouble and do not know it.


 |  Next  |  Last
Pages: 1 2
Prev: WIndows Authentication Connection String
Next: Can I index this kind of view?