look in other table give *

[goransaler]

If i have two tables
like

Doing
doingtime

and i would like to query Doing table
and if one or some datetime values in one field in doingtime table is null i like a value of *
I use sql2000

how will i do please help

 

[Ankor]

Could you be more descriptive: list the fields in each table and show how these two tables relate to each other? Also, what is "value of *"? Does it mean that you want to select all values from one of the tables, and join two tables?

 

[goransaler]

What i want is

Doing

ID Activity

Doingtime

ID Doid Starttime Endtime Actpartinfo

ID joined whith Doid

and when i list Doing table if any endtime is empty
i whant a expression of *
Endime is datetime format

 

[Ankor]

Oh, so I guess the join itself is not a problem. If you just need to replace a date with a character '*', try this:
select isnull(convert(varchar(20),Endime),'*')
from Doingtime

 

[goransaler]

I am trying with

SELECT Doing.Activity,(SELECT isnull(CONVERT(varchar(20), Endtime), '*') FROM Doingtime) AS expr1 FROM Doing INNER JOIN Doingtime ON Doing.ID = Doingtime.Doid

but i get Subquery returnd more than one value

 

[swampBoogie]

SELECT Doing.Activity,
isnull(CONVERT(varchar(20), Endtime),'*') AS expr1
FROM Doing INNER JOIN Doingtime
ON Doing.ID = Doingtime.Doid

 

[goransaler]

Thanks
but then if i have five rows in Doingtime table
i get five rows in answer
But i just want one row whith activity and expr1 as * if there is any
field that is empty and not the fields whith date

 

[swampBoogie]

SELECT Doing.Activity,
case when exists (select * from Doingtime
where Doid = doing.id and endtime is null ) then '*'
else (select cast(max(endtime) as varchar(10))
from Doingtime where Doid = doing.id ) end AS expr1
FROM Doing


It still a bit unclear what you want

doing:

ID
--
1
2


doingtime:

doid ! endtime
--------------
1 ! 2002-02-12
1 ! <null>
2 ! 2003-03-12
2 ! 2003-04-23

What result do you expect in those cases.

Giving some sample data and the expected result is usually a better method than a lot of verbal foliage for explaining what you want.

 

[goransaler]

i want like this

1 *
2

i like to look in doingtime and if it exist a empty row in endtime i like a *
and if there is more than one empty i still like one *
and if all have date i like an empty field

 

[swampBoogie]

ok, makes things easier

SELECT Doing.Activity,
case when exists (select * from Doingtime
where Doid = doing.id and endtime is null ) then '*'
else '' end AS expr1
FROM Doing

 

[goransaler]

Perfect thanks a lot