I'm dealing with an issue, I have a stored procedure to create on a database.

By entering an id, I need to receive data about a product. However this product can be separated in two and at this moment we add a letter to the ID (A or B or ...).

The problem is that this additional letter is managed in a column for each table in the database.

And some separate products, (so with ID letter) do not necessarily have a corresponding value in the database for ID letter, in this case I need at least the result of the ID (without the letter)

I wonder if it is possible to use a CASE in the FROM part of a query, or if another solution is available.

--Brief example but is working 
SELECT 
CASE
    WHEN RandomTab1WithLetter.RandomValue1 IS NOT NULL THEN
    RandomTab1WithLetter.RandomValue1
    ELSE RandomTab1.RandomValue1
END AS WantedValue1    
FROM RandomView1
LEFT JOIN RandomTab1 ON RandomView1.ID = RandomTab1.ID 
LEFT JOIN RandomTab1WithLetter
ON COALESCE(RandomView1.ID,'')   COALESCE(RandomView1.ID_BIS,'') =
COALESCE(RandomTab1WithLetter.ID,'')   COALESCE(RandomTab1WithLetter.ID_BIS,'') 

Example of what I'm looking for

RandomView

RandomTab1

Results wanted with ID IDBIS on parameters

CodePudding user response：

The basic approach is to use a left join to the table twice, and then use COALESCE to pick the best available match

with cteView as ( --SAMPLE DATA: Your "View"
    SELECT * 
    FROM (VALUES ('1','A','Widgets'), ('1','C','Widgets'), ('1','','Widgets')
        , ('2','B','Gadgets'), ('2','C','Gadgets'), ('2','','Gadgets')
    ) as ViewDef(ID, ID_BIS, ProdName)
), cteTab1 as (--SAMPLE DATA: Your "View"
    SELECT * 
    FROM (VALUES 
        ('1','A','Type A Widgets'), ('1','B','Type B Widgets'), ('1','','Generic Widgets')
        , ('2','A','Type A Gadgets'), ('2','B','Type B Gadgets'), ('2','','Generic Gadgets')
        , ('3','A','Type A Gizmos'), ('3','B','Type B Gizmos'), ('3','','Generic Gizmos')
    ) as ViewDef(ID, ID_BIS, ProdDescript)
)--Your real query begins here
SELECT V.* --Show what' sin your VIEW, then choose the bast match available
    , COALESCE(Best.ProdDescript, Weak.ProdDescript,'Product not found') as ReturnDescription
    , '-----------' as Alternates --This and below for debugging so you can see choices
    , Best.ProdDescript as BestProdDescript, Weak.ProdDescript as WeakProdDescript
FROM cteView as V --Basic view
    --This is non-null if you have an exact match
    LEFT OUTER JOIN cteTab1 as Best on V.ID = Best.ID 
            AND V.ID_BIS = Best.ID_BIS --IF USING EMPTY use this
            --AND (V.ID_BIS = Best.ID_BIS OR (V.ID_BIS IS NULL AND Best.ID_BIS IS NULL)) --IF USING NULL use this
    --This is non-null if you have a weak match
    LEFT OUTER JOIN cteTab1 as Weak on V.ID = Weak.ID 
            AND V.ID_BIS != '' AND  Weak.ID_BIS = '' --IF USING EMPTY use this
            --AND (V.ID_BIS IS NOT NULL AND Weak.ID_BIS IS NULL)--IF USING NULL use this

gives the following result

EDIT: Plugging in the data you supplied does give the results you listed as desired:

;with cteView as ( --SAMPLE DATA: Your "View"
    SELECT * 
    FROM (VALUES ('666665','','Widgets'), ('666666','A','Widgets'), ('666666','B','Widgets')
        , ('666667','A','Gadgets'), ('666667','B','Gadgets')
    ) as ViewDef(ID, ID_BIS, ProdName)
), cteTab1 as (--SAMPLE DATA: Your "View"
    SELECT * 
    FROM (VALUES 
        ('666665','','12'), ('666666','A','14'), ('666666','B','16')
        , ('666667','','30')
    ) as ViewDef(ID, ID_BIS, ProdDescript)
)--Your real query begins here
SELECT V.* --Show what' sin your VIEW, then choose the bast match available
    , COALESCE(Best.ProdDescript, Weak.ProdDescript,'Product not found') as ReturnDescription
    , '-----------' as Alternates --This and below for debugging so you can see choices
    , Best.ProdDescript as BestProdDescript, Weak.ProdDescript as WeakProdDescript
FROM cteView as V --Basic view
    --This is non-null if you have an exact match
    LEFT OUTER JOIN cteTab1 as Best on V.ID = Best.ID 
            AND V.ID_BIS = Best.ID_BIS --IF USING EMPTY use this
            --AND (V.ID_BIS = Best.ID_BIS OR (V.ID_BIS IS NULL AND Best.ID_BIS IS NULL)) --IF USING NULL use this
    --This is non-null if you have a weak match
    LEFT OUTER JOIN cteTab1 as Weak on V.ID = Weak.ID 
            AND V.ID_BIS != '' AND  Weak.ID_BIS = '' --IF USING EMPTY use this
            --AND (V.ID_BIS IS NOT NULL AND Weak.ID_BIS IS NULL)--IF USING NULL use this

Gives result