Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Separate 1 row value to multiple rows

Status
Not open for further replies.

TheLazyPig

Programmer
Sep 26, 2019
94
PH
Hi!

If remarks is not null...
remarksss_k22tkv.png


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



Thank you!
 
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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"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!
 
Ok I tried to use UNION

Code:
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:
resultss_tkq8yf.png


Now my problem is what if the cntrlno does not have the remarks.
Code:
SELECT pacode,fngetrefdesc(pavariant) AS planDesc FROM pa_mst;

passss_jahooe.png


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
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top