question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked EchoLiu-msft edited

How to make join Table with function cross apply to join by portionkey and portionNumber ?

I work on SQL server 2012 I face issue I can't join cross apply function with join table GlobalPartNumberPortions to

check portionkey and groupid exist on table GlobalPartNumberPortions

here is my statement


  update r set r.Status='Portion Exist Before'
  from #TempImporter r
 cross apply
 dbo.Split(r.GlobalPartNumber,'$') f where CAST (r.PortionNumber AS INT) = f.Id and r.PortionKey = f.Data

I need to add to statement above another check to portionkey and groupid on table GlobalPartNumberPortions

but I don't know how to join with function


by any way are this syntax below possible and correct or not

 inner join     GlobalPartNumberPortions g on t.PortionNumber=g.GroupId  and t.PortionKey=G.PortionKey

join function with table full code as below correct or not

    update r set r.Status='Portion Exist Before'
      from #TempImporter r
     cross apply
     dbo.Split(r.GlobalPartNumber,'$') f where CAST (r.PortionNumber AS INT) = f.Id and r.PortionKey = f.Data
    inner join     GlobalPartNumberPortions g on t.PortionNumber=g.GroupId  and t.PortionKey=G.PortionKey




sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

Check and adjust this syntax:

 update r set r.Status='Portion Exist Before'
 from #TempImporter r
 cross apply dbo.Split(r.GlobalPartNumber,'$') f 
 inner join GlobalPartNumberPortions g on t.PortionNumber=g.GroupId  and t.PortionKey=G.PortionKey
 where CAST (r.PortionNumber AS INT) = f.Id and r.PortionKey = f.Data
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @ahmedsalah-1628 ,

 on t.PortionNumber=g.GroupId and t.PortionKey=g.PortionKey

whether t is incorrect, it should be r?

I did a test and the following statement seems to work:

 update r set r.Status='Portion Exist Before'
 from #TempImporter r
 inner join GlobalPartNumberPortions g 
 on r.PortionNumber=g.GroupId and r.PortionKey=g.PortionKey
 cross apply
 dbo.Split(r.GlobalPartNumber,'$') f 
 where CAST (r.PortionNumber AS INT) = f.Id and r.PortionKey = f.Data

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo


Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.