• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

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

0
Nettkatalogen
Asked:
Nettkatalogen
  • 6
  • 4
1 Solution
 
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now