Separate 1 row value to multiple rows

[TheLazyPig]

Hi!

If remarks is not null...

I'll have to separate UMA, AMR, and BAB in rows like below...

Thank you!

 

[SkipVought]

Hey Lazy,

No data???

Pictures can't be parsed, queried and analyzed!

Full row set please. Several representative row examples, please.

Anyhow, sans data, here's an answer, sans code: Perform multiple queries, each with a WHERE clause for a plandesc value and join each query with a UNION.

What happens in vagueness, stays in vagueness!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[TheLazyPig]

Ok I tried to use UNION

SELECT pol.policyno AS polNo
      ,CASE 
        WHEN (SELECT remarks FROM pa_card WHERE cntrlno = pol.policyno) LIKE '%UMA%' THEN 20000
        ELSE pac.coverageamt
       END AS faceAmt
      ,CASE 
        WHEN (SELECT remarks FROM pa_card WHERE cntrlno = pol.policyno) LIKE '%UMA%' THEN 'UMA'
        ELSE fngetrefdesc(pam.pavariant)
       END AS planDesc
		  ,'0' AS selected
		  ,'0' AS continued
  FROM xcl_claims_pol pol 
  LEFT JOIN view_search_pacard pac ON pac.controlnumber = pol.policyno 
  LEFT JOIN pa_mst pam ON pam.pacode = SUBSTR(pac.controlnumber,1,3) 
 WHERE 1 = 1 
   AND pol.policyno = 'A50449554'
UNION
SELECT pol.policyno AS polNo
      ,CASE 
        WHEN (SELECT remarks FROM pa_card WHERE cntrlno = pol.policyno) LIKE '%AMR%' THEN 2500
        ELSE pac.coverageamt
       END AS faceAmt
      ,CASE 
        WHEN (SELECT remarks FROM pa_card WHERE cntrlno = pol.policyno) LIKE '%AMR%' THEN 'AMR'
        ELSE fngetrefdesc(pam.pavariant)
       END AS planDesc
		  ,'0' AS selected
		  ,'0' AS continued
  FROM xcl_claims_pol pol 
  LEFT JOIN view_search_pacard pac ON pac.controlnumber = pol.policyno 
  LEFT JOIN pa_mst pam ON pam.pacode = SUBSTR(pac.controlnumber,1,3) 
 WHERE 1 = 1 
   AND pol.policyno = 'A50449554'
UNION
SELECT pol.policyno AS polNo
      ,CASE 
        WHEN (SELECT remarks FROM pa_card WHERE cntrlno = pol.policyno) LIKE '%BAB%' THEN 5000  
        ELSE pac.coverageamt
       END AS faceAmt
      ,CASE 
        WHEN (SELECT remarks FROM pa_card WHERE cntrlno = pol.policyno) LIKE '%BAB%' THEN 'BAB'
        ELSE fngetrefdesc(pam.pavariant)
       END AS planDesc
		  ,'0' AS selected
		  ,'0' AS continued
  FROM xcl_claims_pol pol 
  LEFT JOIN view_search_pacard pac ON pac.controlnumber = pol.policyno 
  LEFT JOIN pa_mst pam ON pam.pacode = SUBSTR(pac.controlnumber,1,3) 
 WHERE 1 = 1 
   AND pol.policyno = 'A50449554'
;

Result:

Now my problem is what if the cntrlno does not have the remarks.

SELECT pacode,fngetrefdesc(pavariant) AS planDesc FROM pa_mst;

A50449554 = Plandesc Regular 50K Coverage | faceamt 50000

The result should be a row where
POLNO = A50449555
FACEAMT = 50000
PLANDESC = Regular 50K Coverage
SELECTED = 1
CONTINUED = 0

 

[Andrzejek]

I would -
[tt]
Select cntrlno, Remarks
From MyTable
Where Remarks IS NOT NULL
[/tt]
Dump this data into Excel.
Select Remarks column -> Data - Text to Columns, 'split' your Remarks by a Space
Eliminate not needed columns
With a little VBA, read your data in Excel and Update MyTable

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

LazyPig -
I have to echo Skip's request. You have given us a partial requirement, then introduced a second problem and an unknown parameter-challenged function. You have also provided code that has hard-coded values that appear to have been made up, which completely defeats the purpose of a generalized query.

Could we please have (1) sample data from your table(s) and (2) a complete requirement?

Thank you.