Streng + Streng

Kan vi se hele din kode ?

du replacer NULL med \'\' (altså en tom streng) og smækker dem sammen dernæst!

et lille eks mæ en cursor:


declare @var1 varchar(100)
declare @var2 varchar(100)
declare @var3 varchar(100)
declare @next_id int
declare c1 cursor for
select id, email, hjemmeside from testtabel
open c1
fetch next from c1 into @next_id, @var1, @var2
while @@fetch_status = 0
begin
    if @var1 is null
    begin
        select @var1 = \' \'
    end
    if @var2 is null
    begin
        select @var2 = \' \'
    end
    select @var3 = @var1+@var2
    print @var3
    fetch next from c1 into @next_id, @var1, @var2
end
close c1
deallocate c1

mjallow - er man gået død?

dette er min forespørgsel (kommentar til det om lidt):
                LEFT OUTER JOIN
                (SELECT \'0-3 år\' as varighed, isin_code from intr_duration where datepart(dy,intr_duration.stamp) = datepart(dy,@duration_maxstamp)
                and datepart(yy,intr_duration.stamp) = datepart(yy,@duration_maxstamp)
                and intr_duration.duration >= 0 and intr_duration.duration <= 3)
                as temp_intr_duration_varighed_1 ON temp_intr_duration_varighed_1.isin_code = intr_instr.isin_code_official
               
                LEFT OUTER JOIN
                (SELECT \'3-6 år\' as varighed, isin_code from intr_duration where datepart(dy,intr_duration.stamp) = datepart(dy,@duration_maxstamp)
                and datepart(yy,intr_duration.stamp) = datepart(yy,@duration_maxstamp)
                and intr_duration.duration > 3 and intr_duration.duration <= 6)
                as temp_intr_duration_varighed_2 ON temp_intr_duration_varighed_2.isin_code = intr_instr.isin_code_official
               

jeg har som du måske kan se 2 temp tabeller.

temp_intr_duration_varighed_1 samt temp_intr_duration_varighed_2

hvis jeg prøver at sige

(replace(temp_intr_duration_varighed_1.varighed,null,\'\')+replace(temp_intr_duration_varighed_2.varighed,null,\'\')) as varigheds_gruppe

returnerer den blot null alle steder, og der er mindst én record der findes noget i

mystisk

jeg er ikke gået død ;)

pt. er jeg blank... man du kunne dog prøve at lægge dine varigheder over i en @var og teste for null ligesom ved mit cursoreks!

kom evt. mæ noget mere kode til denne:
(replace(temp_intr_duration_varighed_1.varighed,null,\'\')+replace(temp_intr_duration_varighed_2.varighed,null,\'\')) as varigheds_gruppe

mjoh det kunne jeg godt, men det er ca. en 400 linjer i min Stored Procedure, så jeg har valgt at vise det essentielle...

declare @off_curr_maxstamp smalldatetime
declare @portkey int
set @portkey = 7

declare @stamp smalldatetime
set @stamp = null

declare @off_price_maxstamp smalldatetime
declare @bloomberg_maxstamp smalldatetime
declare @duration_maxstamp smalldatetime
declare @liabilities_maxstamp smalldatetime
declare @assets_maxstamp smalldatetime
declare @msci_maxstamp smalldatetime
select @off_curr_maxstamp = (SELECT MAX(val_stamp) FROM intr_official_curr_price)
select @off_price_maxstamp = (SELECT MAX(val_stamp) FROM intr_official_price)
select @stamp = (SELECT MAX(stamp) FROM intr_instr_port)
select @bloomberg_maxstamp = (SELECT MAX(stamp) FROM intr_bloomberg_stockinfo)
select @duration_maxstamp =(SELECT MAX(stamp) FROM intr_duration)
select @liabilities_maxstamp =(SELECT MAX(stamp) FROM intr_liabilities)
select @assets_maxstamp =(SELECT MAX(stamp) FROM intr_assets)
select @msci_maxstamp =(SELECT MAX(stamp) FROM intr_msci_world_isin)



select temp_intr_duration_varighed_1.varighed as test1,temp_intr_duration_varighed_2.varighed as test2 from

(intr_port

    inner join
   
    (select intr_instr_port.* from intr_instr_port where DATEPART(dy,stamp) = DATEPART(dy,@stamp)
    AND DATEPART(yy,stamp) = DATEPART(yy,@stamp) and port_key = @portkey
    ) as temp_intr_instr_port
    on temp_intr_instr_port.port_key = intr_port.[id]
   
    INNER JOIN intr_instr on temp_intr_instr_port.isin_code = intr_instr.isin_code
   
   
    LEFT OUTER JOIN
   
    (SELECT \'0-3 år\' as varighed, isin_code from intr_duration where datepart(dy,intr_duration.stamp) = datepart(dy,@duration_maxstamp)
    and datepart(yy,intr_duration.stamp) = datepart(yy,@duration_maxstamp)
    and intr_duration.duration >= 0 and intr_duration.duration <= 3)
    as temp_intr_duration_varighed_1 ON temp_intr_duration_varighed_1.isin_code = intr_instr.isin_code_official
   
    LEFT OUTER JOIN
   
    (SELECT \'3-6 år\' as varighed, isin_code from intr_duration where datepart(dy,intr_duration.stamp) = datepart(dy,@duration_maxstamp)
    and datepart(yy,intr_duration.stamp) = datepart(yy,@duration_maxstamp)
    and intr_duration.duration > 3 and intr_duration.duration <= 6)
    as temp_intr_duration_varighed_2 ON temp_intr_duration_varighed_2.isin_code = intr_instr.isin_code_official
   
    LEFT OUTER JOIN
   
    (SELECT \'6-9 år\' as varighed, isin_code from intr_duration where datepart(dy,intr_duration.stamp) = datepart(dy,@duration_maxstamp)
    and datepart(yy,intr_duration.stamp) = datepart(yy,@duration_maxstamp)
    and intr_duration.duration > 6 and intr_duration.duration <= 9)
    as temp_intr_duration_varighed_3 ON temp_intr_duration_varighed_3.isin_code = intr_instr.isin_code_official
   
    LEFT OUTER JOIN
   
    (SELECT \'9-? år\' as varighed, isin_code from intr_duration where datepart(dy,intr_duration.stamp) = datepart(dy,@duration_maxstamp)
    and datepart(yy,intr_duration.stamp) = datepart(yy,@duration_maxstamp)
    and intr_duration.duration > 9)
    as temp_intr_duration_varighed_4 ON temp_intr_duration_varighed_4.isin_code = intr_instr.isin_code_official
    )

som du kan se vælger jeg:
temp_intr_duration_varighed_1.varighed as test1,temp_intr_duration_varighed_2.varighed as test2

disse giver:

test1    test2
0-3 år  NULL
NULL    3-6 år
NULL    3-6 år
0-3 år  NULL

disse 2 skal slås sammen således:

-test-
0-3 år
3-6 år
3-6 år
0-3 år

Det kan jeg som sagt ikke få til at virke... any ideas?

kunne dette virke:

--*** extra variabler***---
declare @test1 varchar(20)
declare @test2 varchar(20)
declare @test3 varchar(40)


select @test1 = temp_intr_duration_varighed_1.varighed, @test2 = temp_intr_duration_varighed_2.varighed from (intr_port

    inner join
   
    (select intr_instr_port.* from intr_instr_port where DATEPART(dy,stamp) = DATEPART(dy,@stamp)
    AND DATEPART(yy,stamp) = DATEPART(yy,@stamp) and port_key = @portkey
    ) as temp

BLA. BLA. BLA.

and intr_duration.duration > 9)
    as temp_intr_duration_varighed_4 ON temp_intr_duration_varighed_4.isin_code = intr_instr.isin_code_official
    )


if @test1 is null
begin
  select @test1 = \'NULL\' //for test skrives \'NULL\'
end
if @test2 is null
begin
  select @test2 = \'NULL\'
end

select @test3 = @test1+@test2

Kan funktionen ISNULL ikke bruges?

f.eks.:

ISNULL(temp_intr_duration_varighed_1.varighed, \'\')

Hvis temp_intr_duration_varighed_1.varighed er NULL retures en tom streng ellers returneres temp_intr_duration_varighed_1.varighed-

-sunedh

Nej jeg har fundet ud af det, jeg lavede min sætning om til at kunne bruge UNION, så virkede det fint...

slash, tak for forsøget i hvert fald :)