This is my table:

As an input I get PackingNr and I need to select SerienNr which is like Rxxxxx not PNxxxxx.

So for example, if I have input PN175971, I need to get SerienNr = R005478.

How can I do this inside of select query? I tried CASE but this won't work as I don't know how many times I have to go again.

My select query is selecting also other columns from different tables.

SELECT 
    ...  ,
    CASE 
        WHEN PSPD.SerienNr LIKE '%PN%' 
            THEN 
                (SELECT SerienNr FROM PSAPacking_Det 
                 WHERE PSAPacking_Det.PackingNr = PSPD.SerienNr) 
            ELSE PSPD.SerienNr 
    END AS SerienNr
    ...
FROM 
    PSAPacking PSPD
JOIN 
     ...
WHERE 
   PSPD.PackingNr = 'PN185971'

CodePudding user response：

try this

declare @var2 varchar(max) = 'PN185971' --PUT YOUR INPUT HERE
declare @var1  varchar(max);

while(@var2 not like 'R%')
begin
    set @var1 = @var2
    set @var2  = (select max(SerienNr) from PSAPacking where PackingNr = @var1)
end

select @var2

if it doesn't find the value starting with R it returns null

CodePudding user response：

You need a recursive CTE. Something like this:

WITH cte AS (
    SELECT t.PackingNr, t.SerienNr
    FROM YourTable t
    WHERE t..PackingNr = 'YourValueHere'

    UNION ALL

    SELECT t.PackingNr, t.SerienNr
    FROM YourTable t
    JOIN cte ON cte.SerienNr = t.PackingNr
)
SELECT TOP (1)
  *
FROM cte
WHERE cte.SerienNr LIKE 'R%';