left outer join > change values when is null
-
SELECT s.valor, r.rolename as description
FROM Split( '14;0;-1;-2;', ';' ) s
LEFT OUTER JOIN roles r ON s.valor = r.roleid
it results:
id description
----------------------
14 Administrators
0 Administrators
-1 NULL
-2 NULL
i have a litle list of IFs :
if id = -1 description = Users
if id = -2 description = Host
etc.
Any possibility to put theses 'ifs' into the query?
[]s
-
case S.Valor
when -1 then 'Users'
when -2 then 'Host'
else r.RoleName
end as Description
-
Can the rows for Users and Hosts be added to the Roles table ?
If not:
SELECT s.valor, r.rolename as description
FROM Split( '14;0;-1;-2;', ';' ) s
LEFT OUTER JOIN
(select role_id, rolename
from roles
union all
select -1 , 'Users'
union all
select -2 , 'Host'
) as r
ON s.valor = r.roleid
SQL = Scarcely Qualifies as a Language
-
thats the point. these negative ids are not inside the roles tables ( who knows the guy who did that please kill him for me! (dotnetnuke's crazy people) )
my darling! the first example worked just like i need!
SELECT
s.valor,
CASE s.valor
WHEN '0' THEN 'Administrators'
WHEN '-1' THEN 'All Users'
WHEN '-2' THEN 'Host'
WHEN '-3' THEN 'Unauthenticated Users'
ELSE r.rolename
END AS description
FROM Split( '14;0;-1;-2;', ';' ) s
LEFT OUTER JOIN roles r ON s.valor = r.roleid
all working fine!
now I gotta think how can i put all together hehe everything works at the main query with 1 row. When I do with all rows, boy... It takes forever and simply doesn't end.
but thats another issue for another thread isnt it?
/kiss
/thankyouverymuch
/anotherkiss
[]s
[]s
-
We'll give a shot at the speed issue but don't expect this code to ever run lightning fast...
-
it can run slowly if it at least run hehehe the problem is when i put all functions together ( are 3 ), and call the functions caller into the main query which is already a bunch of left outer joins, it simply doesn't end the query.
seems like an endless loop, and i have no clue where it is 
when i finally put all to work for the main query i will show all of them for you guys have a look!
i started learning tsql on monday, so I might be overcomplicating somewhere =^.^=
( have i told u I love u today? i love u! =^.^= )
-
If you look like you're pic then it's fine... if you look like me then I'll pass on the love
. -
i look like the picture =^.^=
-
Great
. -
hmm now I put all into a function. the split() works fine.
but something at the last rows are not right. It error:
"Server: Msg 2010, Level 16, State 1, Procedure testList, Line 29
not possible to alter testList for it be an incompatible type of object."
what do u think?
alter FUNCTION testList( @IDList VARCHAR(1000), @Delimiter CHAR(1) )
RETURNS VARCHAR(1000)
--RETURNS @result TABLE( description VARCHAR(1000) )
AS
BEGIN
DECLARE @tmp_result TABLE( valor VARCHAR(1000) )
DECLARE @tmp_distinct TABLE( valor VARCHAR(1000), description VARCHAR(1000) )
DECLARE @tmp_description TABLE( description VARCHAR(1000) )
DECLARE @result VARCHAR(1000)
/* get a table with splited data - each id in a row */
INSERT INTO @tmp_result SELECT valor FROM Split(@IDList, @Delimiter )
INSERT INTO @tmp_distinct
SELECT
s.valor ,
CASE s.valor
WHEN '0' THEN 'Administrators'
WHEN '-1' THEN 'All Users'
WHEN '-2' THEN 'Host'
WHEN '-3' THEN 'Unauthenticated Users'
ELSE r.rolename
END AS description
FROM @tmp_result s
LEFT OUTER JOIN roles r ON s.valor = r.roleid
INSERT INTO @tmp_description SELECT DISTINCT(description) FROM @tmp_distinct
SELECT @result = ISNULL( @result + ' - ', '' ) + description FROM @tmp_description
--INSERT INTO @result SELECT * FROM @var
RETURN @result
END
-- select valor from Split( '14;0;-1;-2;', ';' )
-- SELECT * from dbo.testList( '14;0;-1;-2;', ';' )
-
Try dropping the function and recreating it with your code (swapping alter with create).
-
O.o
it worked!!!!
SELECT dbo.testList( '14;0;-1;-2;', ';' ) as rolenames
rolenames
---------------------------------
Administrators - All Users - Host
\o/
i know it might be a stupid question, but why this error happens sometimes and sometimes not happens?
[]s
-
You probabely tried to change the type of the function (some return tables, other table variables and the last type a scalar value). And it is forbiden to alter the function in a way that changes the type of the function.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply