0

I have the following query to pull LOB field (HL_DELETED_RECORD which contains no more than 25 characters) content, and it works fine by itself:

SELECT  REPLACE(REPLACE( CONVERT(VARCHAR(20), HL_DELETED_RECORD), '&amp', '&'), 'ý', ' ') AS FAC_CHANGES,
   HL_REC_ID
FROM SEC_HIST WITH(NOLOCK)

When I include this query as part of a CTE there are no errors; the LOB text data does not display at all as:

WITH sec_audit AS
        (
        SELECT *...
        ),

hist_logs as
      ( 
SELECT  REPLACE(REPLACE( CONVERT(VARCHAR(20), HL_DELETED_RECORD), '&amp', '&'), 'ý', ' ') AS FAC_CHANGES,
   HL_REC_ID
FROM SEC_HIST WITH(NOLOCK) 
       )

    SELECT * 
    FROM sec_audit
    INNER JOIN hist_logs  WITH(NOLOCK) ON HL_REC_ID = HL_RECORD_ID

The LOB column is defined as: [HL_DELETED_RECORD] text NULL

And here's an HL_DELETED_RECORD data sample: 36371ý1025074ýLEC Which is converted as follows: 36371 1025074 LEC

Anyone else experience this? Thanks!

VENIH
  • 1
  • 2
  • Not enough information... Try to set up a [mcve]. Create a table (DDL) and insert some of your records for testing and try to reproduce your issue. This we can copy and paste to our machines and help you for sure. Some general remarks: [One by Aaron Bertrand about NOLOCK](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) and I'd suggest to use table aliases **always**. You last SELECT was something like `... FROM sec_audit sa INNER JOIN hist_log hl ON sa.HL_RECORD_ID=hl.HL_REC_ID`. – Shnugo Dec 05 '19 at 08:11
  • One more question/hint: What data type is `HL_DELETED_RECORDS`? The need to replace `&amp` with `&` points to some XML-like markup. But in this case the entity is wrong (should be `&`). And you state *no more than 15 characters*... Hard to believe... – Shnugo Dec 05 '19 at 08:17
  • Thanks for the comments; let me try and work up a more comprehensive example and include additional info regarding data types. – VENIH Dec 10 '19 at 16:44

0 Answers0