Probles with mulitipart indetifiers in APLLY function

i just get thi error, and i cant figure out why?
Can youhelp apoor soul?





	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
		(
		--TABLE VALUED FUNCTION 
		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 = B.BRANSJE 
			GROUP BY BRANSJEKODE
			ORDER BY COUNT(*)
		--END TABLE FUNCTION
		) 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()

Open in new window

NettkatalogenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please put the exact error message you get?
0
NettkatalogenAuthor Commented:
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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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()

Open in new window

0
NettkatalogenAuthor Commented:
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()

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the database compatibility version of this database, please?
0
NettkatalogenAuthor Commented:
80 , but it did not helt convert to 90...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
with compatibility 80, you cannot use CROSS APPLY.
it has to be 90 or higher
0
NettkatalogenAuthor Commented:
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
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hmmm... I see.
this should fix it
select *,newid() from 
        (


        select 
                top 25 
                        b.bransje,
                        bransjekode,
                        count(*) as count 
                        iTVF.ALT_BRANSJEKODE,
                        iTVF.COUNT_ALT_BRANSJEKODE
        from    dbo.bransjer b
        JOIN    hoved h 
          ON    h.orgnr = b.orgnr 
        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 <> ''
                
        group by 
                bransje,
                bransjekode 
                ALT_BRANSJEKODE,
                COUNT_ALT_BRANSJEKODE
        order by 
                count(*) desc
        
) as Wordlist
  order by newid()

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.