CROSS APPLY Issue

Not much experience with CROSS APPLY. Running into an issue LEFT JOIN would generally resolve, though not applicable here.

select
 t.refID
 z.dataPiece
from
 MyDataTable t
    CROSS APPLY myFunction(t.refID) z

t.refID may not always be what myFunction expects or is null, therefore those t.records are dropped. So if there's 100 records in MyDataTable, only 90 may result. I can't arbitrarily change t.refID if NULL or not the expected combination of characters (It's a vehicle's VIN btw). I also can't change the function to deal with it, as its used in other applications. Ideally the function would simply return NULL as a JOIN would. Thanks in advance for any assistance.
RickzzzAsked:
Who is Participating?
 
Máté FarkasConnect With a Mentor Database Developer and AdministratorCommented:
You should use OUTER APPLY instead of CROSS APPLY.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why not add a WHERE clause?
select
 t.refID
 z.dataPiece
from
 MyDataTable t
    CROSS APPLY myFunction(t.refID) z 
WHERE t.refID IS NOT NULL

Open in new window

0
 
RickzzzAuthor Commented:
Vitor - thanks for the input.
0
 
RickzzzAuthor Commented:
Thanks!
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.

All Courses

From novice to tech pro — start learning today.