Probles with mulitipart indetifiers in APLLY function

can you please put the exact error message you get?

Whops, sorry I htougt I did:

Server: Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "B.BRANSJE" could not be bound.

please create this function

CREATE FUNCTION dbo.GetTopBRANSJEKODE ( @BRANSJE int )
RETURNS @res TABLE ( BRANSJEKODE INT, records INT )
AS
  INSERT INTO @res
                SELECT 
                        top 1
                        BRANSJEKODE as ALT_BRANSJEKODE,count(*) as COUNT_ALT_BRANSJEKODE
                FROM 
                        HOVED ALT_H, BRANSJER ALT_B 
                WHERE   
                        ALT_B.ORGNR = ALT_B.ORGNR
                        AND BRANSJEKODE IS NOT NULL
                        AND BRANSJEKODE <> '' 
                        AND artnr = 106
                        AND ALT_B.BRANSJE = @BRANSJE 
                        GROUP BY BRANSJEKODE
                        ORDER BY COUNT(*)
   RETURN
END

Select allOpen in new window

and change your code into:

declare @bransjekode varchar(512)
select @bransjekode = bransjekode from hoved where firma = 'Tove´s fotpleie'
        

select *,newid() from 
        (


        
        
        
        select 
                top 25 
                        b.bransje,
                        bransjekode,
                        count(*) as count, 
                        iTVF.ALT_BRANSJEKODE,
                        iTVF.COUNT_ALT_BRANSJEKODE
        from 
                dbo.bransjer b, 
                hoved h 
        CROSS
        APPLY dbo.GetTopBRANSJEKODE( B.BRANSJE ) iTVF

        where 
                b.orgnr in 
                (
                select orgnr from hoved where bransjekode = @bransjekode and coalesce(artnr,0)<>0
                )
                and bransje is not null
                and bransje <> ''
                and h.orgnr = b.orgnr 
        group by 
                bransje,
                bransjekode, 
                ALT_BRANSJEKODE,
                COUNT_ALT_BRANSJEKODE
        order by 
                count(*) desc
        
) as Wordlist
  order by newid()

Select allOpen in new window

You suggestions gives this error message.
'
Server: Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'iTVF'.
Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'and'.

I've tested the function it works fine (excpet that bransjekode is a varchar, but thats an easyfix)

declare @bransjekode varchar(512)
select @bransjekode = bransjekode from hoved where firma = 'Tove´s fotpleie'
       

select *,newid() from 
        (


        select 
                top 25 
                        b.bransje,
                        bransjekode,
                        count(*) as count 
                        iTVF.ALT_BRANSJEKODE,
                        iTVF.COUNT_ALT_BRANSJEKODE
        from 
                dbo.bransjer b, 
                hoved h 
        CROSS
        APPLY dbo.GetTopBRANSJEKODE( B.BRANSJE )  iTVF

        where 
                b.orgnr in 
                (
                select orgnr from hoved where bransjekode = @bransjekode and coalesce(artnr,0)<>0
                )
                and bransje is not null
                and bransje <> ''
                and h.orgnr = b.orgnr 
        group by 
                bransje,
                bransjekode 
                ALT_BRANSJEKODE,
                COUNT_ALT_BRANSJEKODE
        order by 
                count(*) desc
        
) as Wordlist
  order by newid()

Select allOpen in new window

what is the database compatibility version of this database, please?

80 , but it did not helt convert to 90...

with compatibility 80, you cannot use CROSS APPLY.
it has to be 90 or higher

That is not correct, i've used cross apply with 80, the problem seesm to be where there is two tables in the previous select:

    select
                top 25
                        b.bransje,
                        bransjekode,
                        count(*) as count
                        iTVF.ALT_BRANSJEKODE,
                        iTVF.COUNT_ALT_BRANSJEKODE
        from
                dbo.bransjer b,
                hoved h <---- If i remove this table it works perfectly, but of course i canot.... :P