date

How to write this select so that I get max 5 date but as one alias?
(SELECT
ISNULL(DateOpen,null) ,
ISNULL(DateSent,null) ,
ISNULL(DateRepairFinished,null) ,
ISNULL(DateReceived,null) ,
ISNULL(DateCleared, null)
FROM Repair) AS Date,can you post the hole code here...?
you need to use UNION
select column1, column2
From
(
Select column1, column2 from table where ...
union all
Select column1, column2 from table where ...
) a
order by column1
Bruno Alexandre
(a Portuguese in Kbenhanv, Danmark)
"Hrvoje Voda" <hrvoje.voda@.luatech.com> escreveu na mensagem
news:e4elhm$bq3$1@.ss408.t-com.hr...
> How to write this select so that I get max 5 date but as one alias?
> (SELECT
> ISNULL(DateOpen,null) ,
> ISNULL(DateSent,null) ,
> ISNULL(DateRepairFinished,null) ,
> ISNULL(DateReceived,null) ,
> ISNULL(DateCleared, null)
> FROM Repair) AS Date,
>|||Hi
create table #t (dt1 datetime, dt2 datetime, dt3 datetime)
insert into #t select '20060101','20060102','20060103'
select dt1 as blblbl from #t
union all
select dt2 from #t
union all
select dt3 from #t
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
news:e4elhm$bq3$1@.ss408.t-com.hr...
> How to write this select so that I get max 5 date but as one alias?
> (SELECT
> ISNULL(DateOpen,null) ,
> ISNULL(DateSent,null) ,
> ISNULL(DateRepairFinished,null) ,
> ISNULL(DateReceived,null) ,
> ISNULL(DateCleared, null)
> FROM Repair) AS Date,
>|||i get an error:
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uwIeXrYeGHA.4304@.TK2MSFTNGP05.phx.gbl.
.
> Hi
> create table #t (dt1 datetime, dt2 datetime, dt3 datetime)
> insert into #t select '20060101','20060102','20060103'
>
> select dt1 as blblbl from #t
> union all
> select dt2 from #t
> union all
> select dt3 from #t
>
>
>
> "Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
> news:e4elhm$bq3$1@.ss408.t-com.hr...
>
>|||Have you ran my script? If you provide your actual data + expected resyult i
t will much easier to suggets you something.
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:e4enhm$gmd$1@.s
s408.t-com.hr...
i get an error:
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uwIeXrYeGHA.4304@.TK2MSFTNGP05.phx.gbl.
.
> Hi
> create table #t (dt1 datetime, dt2 datetime, dt3 datetime)
> insert into #t select '20060101','20060102','20060103'
>
> select dt1 as blblbl from #t
> union all
> select dt2 from #t
> union all
> select dt3 from #t
>
>
>
> "Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
> news:e4elhm$bq3$1@.ss408.t-com.hr...
>
>|||I'm using this select inside another select that is in the UNION.
SELECT
(
select DateOpen AS Date
from RprRepair
where TestObjectID = @.AssetID
union all
select DateSent
from RprRepair
where TestObjectID = @.AssetID
union all
select DateRepairFinished
from RprRepair
where TestObjectID = @.AssetID),
FROM RprRepair rpr
LEFT OUTER JOIN MntAsset XXX
ON XXX.AssetID = rpr.TestObjectID
WHERE (XXX.AssetID = @.AssetID)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eGejY%23YeGHA.1264@.TK2
MSFTNGP05.phx.gbl...
Have you ran my script? If you provide your actual data + expected resyult i
t will much easier to suggets you something.
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:e4enhm$gmd$1@.s
s408.t-com.hr...
i get an error:
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uwIeXrYeGHA.4304@.TK2MSFTNGP05.phx.gbl.
.
> Hi
> create table #t (dt1 datetime, dt2 datetime, dt3 datetime)
> insert into #t select '20060101','20060102','20060103'
>
> select dt1 as blblbl from #t
> union all
> select dt2 from #t
> union all
> select dt3 from #t
>
>
>
> "Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
> news:e4elhm$bq3$1@.ss408.t-com.hr...
>
>|||Try
SELECT * FROM
(
select DateOpen AS Date,TestObjectID from RprRepair where TestObjectID = @.As
setID
union all
select DateSent ,TestObjectID from RprRepair where TestObjectID = @.AssetID
union all
select DateRepairFinished,TestObjectID from RprRepair where TestObjectID = @.
AssetID
) AS Der
LEFT OUTER JOIN MntAsset XXX
ON XXX.AssetID = Der.TestObjectID
WHERE XXX.AssetID = @.AssetID
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:e4ep1n$kba$1@.s
s408.t-com.hr...
I'm using this select inside another select that is in the UNION.
SELECT
(
select DateOpen AS Date
from RprRepair
where TestObjectID = @.AssetID
union all
select DateSent
from RprRepair
where TestObjectID = @.AssetID
union all
select DateRepairFinished
from RprRepair
where TestObjectID = @.AssetID),
FROM RprRepair rpr
LEFT OUTER JOIN MntAsset XXX
ON XXX.AssetID = rpr.TestObjectID
WHERE (XXX.AssetID = @.AssetID)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eGejY%23YeGHA.1264@.TK2
MSFTNGP05.phx.gbl...
Have you ran my script? If you provide your actual data + expected resyult i
t will much easier to suggets you something.
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:e4enhm$gmd$1@.s
s408.t-com.hr...
i get an error:
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uwIeXrYeGHA.4304@.TK2MSFTNGP05.phx.gbl.
.
> Hi
> create table #t (dt1 datetime, dt2 datetime, dt3 datetime)
> insert into #t select '20060101','20060102','20060103'
>
> select dt1 as blblbl from #t
> union all
> select dt2 from #t
> union all
> select dt3 from #t
>
>
>
> "Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
> news:e4elhm$bq3$1@.ss408.t-com.hr...
>
>|||When I put it into my select with union i get an error:
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:O3O2sNZeGHA.2076@.TK2MS
FTNGP04.phx.gbl...
Try
SELECT * FROM
(
select DateOpen AS Date,TestObjectID from RprRepair where TestObjectID = @.As
setID
union all
select DateSent ,TestObjectID from RprRepair where TestObjectID = @.AssetID
union all
select DateRepairFinished,TestObjectID from RprRepair where TestObjectID = @.
AssetID
) AS Der
LEFT OUTER JOIN MntAsset XXX
ON XXX.AssetID = Der.TestObjectID
WHERE XXX.AssetID = @.AssetID
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:e4ep1n$kba$1@.s
s408.t-com.hr...
I'm using this select inside another select that is in the UNION.
SELECT
(
select DateOpen AS Date
from RprRepair
where TestObjectID = @.AssetID
union all
select DateSent
from RprRepair
where TestObjectID = @.AssetID
union all
select DateRepairFinished
from RprRepair
where TestObjectID = @.AssetID),
FROM RprRepair rpr
LEFT OUTER JOIN MntAsset XXX
ON XXX.AssetID = rpr.TestObjectID
WHERE (XXX.AssetID = @.AssetID)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eGejY%23YeGHA.1264@.TK2
MSFTNGP05.phx.gbl...
Have you ran my script? If you provide your actual data + expected resyult i
t will much easier to suggets you something.
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:e4enhm$gmd$1@.s
s408.t-com.hr...
i get an error:
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uwIeXrYeGHA.4304@.TK2MSFTNGP05.phx.gbl.
.
> Hi
> create table #t (dt1 datetime, dt2 datetime, dt3 datetime)
> insert into #t select '20060101','20060102','20060103'
>
> select dt1 as blblbl from #t
> union all
> select dt2 from #t
> union all
> select dt3 from #t
>
>
>
> "Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
> news:e4elhm$bq3$1@.ss408.t-com.hr...
>
>|||please, tell me what is the string thatyou want to OUTPUT, I will transform
your code to that, but I need to know what's in the BD and what you want to
get from it
something like
column1 : DateOpen
column2 : DateSent
column3 : DateRepairFinished
is that it?
--
Bruno Alexandre
(a Portuguese in Kobenhanv, Danmark)
"Hrvoje Voda" <hrvoje.voda@.luatech.com> escreveu na mensagem news:e4eudn$3rr
$1@.ss408.t-com.hr...
When I put it into my select with union i get an error:
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:O3O2sNZeGHA.2076@.TK2MS
FTNGP04.phx.gbl...
Try
SELECT * FROM
(
select DateOpen AS Date,TestObjectID from RprRepair where TestObjectID = @.As
setID
union all
select DateSent ,TestObjectID from RprRepair where TestObjectID = @.AssetID
union all
select DateRepairFinished,TestObjectID from RprRepair where TestObjectID = @.
AssetID
) AS Der
LEFT OUTER JOIN MntAsset XXX
ON XXX.AssetID = Der.TestObjectID
WHERE XXX.AssetID = @.AssetID
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:e4ep1n$kba$1@.s
s408.t-com.hr...
I'm using this select inside another select that is in the UNION.
SELECT
(
select DateOpen AS Date
from RprRepair
where TestObjectID = @.AssetID
union all
select DateSent
from RprRepair
where TestObjectID = @.AssetID
union all
select DateRepairFinished
from RprRepair
where TestObjectID = @.AssetID),
FROM RprRepair rpr
LEFT OUTER JOIN MntAsset XXX
ON XXX.AssetID = rpr.TestObjectID
WHERE (XXX.AssetID = @.AssetID)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eGejY%23YeGHA.1264@.TK2
MSFTNGP05.phx.gbl...
Have you ran my script? If you provide your actual data + expected resyult i
t will much easier to suggets you something.
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:e4enhm$gmd$1@.s
s408.t-com.hr...
i get an error:
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uwIeXrYeGHA.4304@.TK2MSFTNGP05.phx.gbl.
.
> Hi
> create table #t (dt1 datetime, dt2 datetime, dt3 datetime)
> insert into #t select '20060101','20060102','20060103'
>
> select dt1 as blblbl from #t
> union all
> select dt2 from #t
> union all
> select dt3 from #t
>
>
>
> "Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
> news:e4elhm$bq3$1@.ss408.t-com.hr...
>
>|||Yes. That's it.(But like rows...)
I wnat to get mx 5 dates for specific @.AssetID
"Bruno Alexandre" <bruno.in.dk@.gmail.com> wrote in message news:unRyx$ZeGHA.
3348@.TK2MSFTNGP03.phx.gbl...
please, tell me what is the string thatyou want to OUTPUT, I will transform
your code to that, but I need to know what's in the BD and what you want to
get from it
something like
column1 : DateOpen
column2 : DateSent
column3 : DateRepairFinished
is that it?
--
Bruno Alexandre
(a Portuguese in Kobenhanv, Danmark)
"Hrvoje Voda" <hrvoje.voda@.luatech.com> escreveu na mensagem news:e4eudn$3rr
$1@.ss408.t-com.hr...
When I put it into my select with union i get an error:
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:O3O2sNZeGHA.2076@.TK2MS
FTNGP04.phx.gbl...
Try
SELECT * FROM
(
select DateOpen AS Date,TestObjectID from RprRepair where TestObjectID = @.As
setID
union all
select DateSent ,TestObjectID from RprRepair where TestObjectID = @.AssetID
union all
select DateRepairFinished,TestObjectID from RprRepair where TestObjectID = @.
AssetID
) AS Der
LEFT OUTER JOIN MntAsset XXX
ON XXX.AssetID = Der.TestObjectID
WHERE XXX.AssetID = @.AssetID
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:e4ep1n$kba$1@.s
s408.t-com.hr...
I'm using this select inside another select that is in the UNION.
SELECT
(
select DateOpen AS Date
from RprRepair
where TestObjectID = @.AssetID
union all
select DateSent
from RprRepair
where TestObjectID = @.AssetID
union all
select DateRepairFinished
from RprRepair
where TestObjectID = @.AssetID),
FROM RprRepair rpr
LEFT OUTER JOIN MntAsset XXX
ON XXX.AssetID = rpr.TestObjectID
WHERE (XXX.AssetID = @.AssetID)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eGejY%23YeGHA.1264@.TK2
MSFTNGP05.phx.gbl...
Have you ran my script? If you provide your actual data + expected resyult i
t will much easier to suggets you something.
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:e4enhm$gmd$1@.s
s408.t-com.hr...
i get an error:
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uwIeXrYeGHA.4304@.TK2MSFTNGP05.phx.gbl.
.
> Hi
> create table #t (dt1 datetime, dt2 datetime, dt3 datetime)
> insert into #t select '20060101','20060102','20060103'
>
> select dt1 as blblbl from #t
> union all
> select dt2 from #t
> union all
> select dt3 from #t
>
>
>
> "Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
> news:e4elhm$bq3$1@.ss408.t-com.hr...
>
>