0

I found this solution which I'm pretty sure will do exactly what I'm trying to accomplish, unfortunately, all my SQL is self-taught and I can't figure out how to integrate the solution into the query I'm working with.

I tried putting everything above the SELECT statement, but I couldn't set the @testString variable to the COND_NOTE.TEXT field, which makes sense, since all of that happens inside the main query select.

Apologies for essentially asking someone to give me a crash course in presumably a pretty basic application of SQL syntax.

SELECT
    CASE WHEN DM_IAM_D_I_ROOT.ZZCAP_FACILITY = 'N200' THEN 'MT'
        WHEN DM_IAM_D_I_ROOT.ZZCAP_FACILITY = 'N202' THEN 'PI'
        WHEN DM_IAM_D_I_ROOT.ZZCAP_FACILITY = 'N204' THEN 'FL'
        ELSE 'Err' END AS FAC
    ,right(DM_IAM_D_I_ROOT.ISSUE_ID,12) AS ISS_ID
     --The following line is the one that I want to use the solution from the referenced question in.
    ,REPLACE(REPLACE(COND_NOTE.TEXT, '"', '"'), 'br/>', '') NOTES
    ,COND_NOTE.LINE_COUNTER
    
FROM
  DM_IAM_D_I_ROOT
    LEFT JOIN 
    (
        SELECT
            DM_BOBF_D_TXCROOT.HOST_KEY
            ,DM_BOBF_D_TXCROOT.MANDT
            ,DM_BOBF_D_TXCTXT.TEXT_TYPE
            ,DM_BOBF_D_TXCCON.LINE_COUNTER
            ,DM_BOBF_D_TXCCON.TEXT
        FROM
            DM_BOBF_D_TXCROOT
            INNER JOIN DM_BOBF_D_TXCTXT
                ON DM_BOBF_D_TXCROOT.DB_KEY = DM_BOBF_D_TXCTXT.PARENT_KEY AND DM_BOBF_D_TXCROOT.MANDT = DM_BOBF_D_TXCTXT.MANDT
            INNER JOIN DM_BOBF_D_TXCCON
                ON DM_BOBF_D_TXCTXT.DB_KEY = DM_BOBF_D_TXCCON.PARENT_KEY AND DM_BOBF_D_TXCTXT.MANDT = DM_BOBF_D_TXCCON.MANDT
        WHERE
            DM_BOBF_D_TXCROOT.MANDT = '100'
            AND DM_BOBF_D_TXCTXT.TEXT_TYPE = 'ZCOND'
    ) COND_NOTE ON DM_IAM_D_I_ROOT.DB_KEY = COND_NOTE.HOST_KEY AND DM_IAM_D_I_ROOT.MANDT = COND_NOTE.MANDT

WHERE
    DM_IAM_D_I_ROOT.ISSUE_TYPE = 'CAP'
    AND DM_IAM_D_I_ROOT.MANDT = '100'
    AND DM_IAM_D_I_ROOT.LCYCLE_CD NOT IN ('10', '64')
    
ORDER BY ZZCAP_FACILITY, ISSUE_ID, LINE_COUNTER
ABrown78
  • 29
  • 6
  • how do you declare @testString and what do you want to do with it? – asi Oct 21 '20 at 23:28
  • Your first question is MY question. I want to use the solution provided in the referenced link to delete (replace with '') from COND_NOTE.TEXT, but I don't know how to integrate that function into my query. – ABrown78 Oct 22 '20 at 17:06
  • see detailed answer – asi Oct 23 '20 at 14:00
  • What's wrong with the `REPLACE(REPLACE(COND_NOTE.TEXT, '"', '"'), 'br/>', '')` you already have? – Joel Coehoorn Oct 23 '20 at 14:41
  • There are significantly more bad character strings than what's shown in the example. I'm still parsing the data to identify them all, but I'm at 17 and counting. – ABrown78 Oct 23 '20 at 14:44

0 Answers0