How to get all parts from temp table in case it return nothing ?

Question

Post reply

How to get all parts from temp table in case it return nothing ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

August 24, 2020 at 12:12 am

#3782750

I work on SQL server 2012 I face issue I can't return data not have relation with function

create table #TempPC   
            (  
                    PartNumber NVARCHAR(300),  
                    CompanyId INT,  
                    Status   nvarchar(200)  
            )  
insert into #TempPC (PartNumber,CompanyId)   
        values  
        ('9C06031A2R43FKHFT',1233345),  
                    ('VJ0805AIR5CXAMT',8433324)

when make select as below :

Select t.PartNumber,t.CompanyName,pc.FamilyName,t.Status FROM  #TempPC t    
    
cross apply  [PC].FN_PartCheck_Test( t.[PartNumber],0,1,1,t.CompanyId) pc     
    
Where pc.GroupID>-2 And pc.PortionID>-2

it return only first part because it have relation with function

but I need also to display part VJ0805AIR5CXAMT

so how to do that please ?

I need to display records or rows on temp table that not have any relation with function or return null

because it is exist

exactly as I do left join below

select * from A left join B on A.Id=B.Id

WHERE B.ID IS NULL

SO How to select nothing related to function or not return data

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

I expect you could change your function so it returns something rather than nothing when the PartNumber is a value like VJ0805AIR5CXAMT.

Failing that, UNION or a join should work to get the remaining data. Dump your results either into a CTE or a second temp table and then select * from the cte/second temp UNIONED to the original temp table where the part number doesn't exist in the second temp/cte.

NOT IN is not that efficient, but if the list is small, that should work. Failing that, add a column to #TempPC called "Familyname" and change your SELECT to an UPDATE and after the update completes, select * from #TempPC.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

You could also change from a CROSS APPLY to an OUTER APPLY. If nothing is returned from the function then use ISNULL/COALESCE to set the value as needed.

Ideally - you should be creating inline-table valued functions that are guaranteed to return a result if you are going to use CROSS APPLY unless the goal is to also use the CROSS APPLY to filter out non-matching rows.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs