CODEWITHSUNDEEP
sql-server
Alberto
Alberto

Reputation: 15

Filter record based on a column using SQL Server

I have a query like this that will return two records, one with NULL in VendorCode and VendoroffsetAccount and another with valid values. I want a way to get the non null record if there are two records.Otherwise the record with NULL without using temp tables.Any help will be appreciated.

    SELECT DISTINCT  ift.BatchGuid AS ProcessId ,
                'H' AS RecordType ,
                ISNULL(ift.SuperBranch, cd.SuperBranch) AS SuperBranch ,
                ISNULL(ift.ProcessName, cd.ProcessName) AS ProcessName ,
                VendorOffsetAccount = CASE WHEN ift.OffsetType = 'V'
                THEN ISNULL(ift.VendorOffsetAccount,
                cd.VendorOffsetAccount)
                END ,
                VendorCode = CASE WHEN ift.OffsetType = 'V'
                THEN ISNULL(ift.VendorCode, cd.VendorCode)
                END                    
                FROM    IFP.InboundFileTransaction ift
                CROSS APPLY IFP.ctlConfigDefault cd
                WHERE   CAST(BatchGuid AS CHAR(36)) = '67C6A9C3-F8B7-45ED-8DDD-0AB6701BED34'

Here is a sample output of this query.

     Id   Type      SB   Name    VendorOffsetAccount        VendorCode
      1     H       XYZ  UPLOAD  NULL                       NULL
      1     H       XYZ  UPLOAD  XYZ000123                  DFEE

Thanks in Advance

Upvotes: 1

Views: 42

Answers (1)

SqlZim
SqlZim

Reputation: 38063

If you want one record, and prefer the non null value then you can use top and order by:

select top 1
    ift.BatchGuid as ProcessId
  , 'H' as RecordType
  , isnull(ift.SuperBranch, cd.SuperBranch) as SuperBranch
  , isnull(ift.ProcessName, cd.ProcessName) as ProcessName
  , VendorOffsetAccount = case when ift.OffsetType = 'V' then isnull(ift.VendorOffsetAccount, cd.VendorOffsetAccount) end
  , VendorCode = case when ift.OffsetType = 'V' then isnull(ift.VendorCode, cd.VendorCode) end
from ifp.InboundFileTransaction ift
  cross apply ifp.ctlConfigDefault cd
where cast(BatchGuid as char(36)) = '67C6A9C3-F8B7-45ed-8ddd-0ab6701bed34'
order by case when ift.OffsetType = 'V' then isnull(ift.VendorCode, cd.VendorCode) end desc

Upvotes: 2

Related Questions