asked on

TSQL CASE, CHECK DATA, CONVERT, TRIM

How do you compare & replace in TSQL while converting and trimiming? I am in the midst of a vary large migration conversion between and older table with different formatting and rules on the data from the target output. Here is an example:

IF OBJECT_ID('TEMPDB..#LTS_FIX') IS NOT NULL
BEGIN DROP TABLE #LTS_FIX END
SELECT

....(VARIOUS CONVERT DATA ELEMENT STATEMENTS)...
Old data:
LOAN_NUMBER 0123456789 MORTGAGOR_LAST_NAME: OSHEA
LOAN_NUMBER 0234567891 MORTGAGOR_LAST_NAME: OCONNELL

NEW DATA:
LOAN_NUMBER 0123456789 MORTGAGOR_LAST_NAME: O'SHEA
LOAN_NUMBER 0234567891 MORTGAGOR_LAST_NAME: O'CONNELL

In addition to this issue the old data is not left adjusted, rather it is free form and sometimes the MORTGAGOR_LAST_NAME is missing.

Here is the code I have so far:

,CASE WHEN (cMortgLastName IS NOT NULL) AND (cMortgLastName <> ' ')
THEN CONVERT(VARCHAR(10), nullif(LTRIM(RTRIM(cMortgLastName)),'')) END as MORTGAGOR_LAST_NAME

.. MORE DATA ELEMENT CONVERT STATEMENT..

INTO #LTS_FIX
FROM LTSArchive.dbo.LoanMaster_0609

I am thinking if I do an INNER JOIN on the from statement like this:

INTO #LTS_FIX
FROM LTSArchive.dbo.LoanMaster_0609
INNER JOIN dbo.BDES_MAP

The BDES_MAP table contains new data with the correct format. Then I will of course of the same field element in the new table to match against the old.

I need to keep what I have and add to to the same statement the ability to check & replace the old input data against new data with the proper format, i.e, an apostrophe where needed.

Help is most appreciated!

Leogal

ASKER

Still researching and trying - here is the code I have now in the select statement:

,CASE WHEN (cMortgLastName IS NOT NULL) AND (cMortgLastName <> ' ')
THEN CONVERT(VARCHAR(10), nullif(LTRIM(RTRIM(cMortgLastName)),''))
ELSE REPLACE('cMortgLastName','cMortgLasttName','B.MORTGAGOR_LAST_NAME')
END as MORTGAGOR_LAST_NAME

I think if I can get the join to work, then I may have it.

Here is the join:
INTO #LTS_FIX
FROM LTSArchive.dbo.LoanMaster_0609
INNER JOIN dbo.BDES_MAP B

here is the error returned when parse is performed:
Msg 102, Level 15, State 1, Line 212
Incorrect syntax near 'B'.

Leogal

ASKER

Okay I modified the select code to look like this:
select
,field,
,field,
more fields,

,CASE WHEN (cMortgLastName IS NOT NULL) AND (cMortgLastName <> ' ') AND (cMortgLastName = B.MORTGAGOR_LAST_NAME)
THEN CONVERT(VARCHAR(10), nullif(LTRIM(RTRIM(cMortgLastName)),''))
ELSE REPLACE('cMortgLastName','cMortgLasttName','B.MORTGAGOR_LAST_NAME')
END as MORTGAGOR_LAST_NAME
,field
,field,
more fields...
INTO #LTS_MAP
FROM LTSArchive.dbo.LoanMaster_0609 L
INNER JOIN TEST.dbo.bdes_MAP B on (L.cLoanNo = B.LOAN_NUMBER)

no errors, just got something fouled with the replace as the field name from the old table is replacing the MORTGAGOR_LAST_NAME as opposed to populating with the new field data

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

I don't fully understand exactly what you're trying to do.

Please post table defs / sample *full* rows for each of the "before" and "after" tables, identifying the link column(s) to the other table you mentioned.