Incorrect syntax near ''.0''.

  • Help, I'm looking for help with an old query that seems rather complex to me.  Can someone please take a look.  I've been trying to reverse engineer the query to include new fields and links.

    If there is any help at all, please let me know ASAP.

    Thanks,

     

    Here is the query.

    DECLARE @BeginDate AS DECIMAL

    DECLARE @EndDate AS DECIMAL

    DECLARE @DispFee AS DECIMAL(8,2)

    DECLARE @Type AS VARCHAR

    SET @BeginDate =  '20061101'  /* <-- Start Date Range for PDE Here */

    SET @EndDate =     '20061231'  /* <-- End Date Range for PDE Here */

    SET @DispFee =      '15.00'      /* <-- Enter Fee Based on Adjustment for the Period Being Reported Field F29 */

    SET @Type =           ' '         /* <-- Credit vs. Charges (A = Credits and <blank space> = Charges) */

    /*

    TIMS PDE Revisions:

    20061204 TS

    */

    SELECT

    /* Go */     

    'DET' AS F01,

    sph.IVPATMAS.MEDRECNUM AS F04,

    CASE WHEN sph.PATCVG.POLICY = 'NONE' THEN '' ELSE sph.PATCVG.POLICY END F05,

    CASE WHEN sph.PATMAS.GENDER = 'M' THEN 1 WHEN sph.PATMAS.GENDER = 'F' THEN 2 END F07,

    sph.PBCLMDTL.FILLDATE AS F08,

    ('00' + sph.PBCLMDTL.PRESCNUM) AS F10,

    CONVERT(varchar(11),replace(convert(char(11),space(11-len(CAST(sph.PBCLMDTL.NDC1 AS VARCHAR(11)))) + CAST(sph.PBCLMDTL.NDC1 AS

    VARCHAR(11))),' ','0')) AS F12,

    '11' AS F13,

    '      930823489' AS F14,

    CONVERT(varchar(2),replace(convert(char(2),space(2-len(CAST(sph.PBCLMDTL.RFLNUM AS VARCHAR(2)))) + CAST(sph.PBCLMDTL.RFLNUM AS

    VARCHAR(2))),' ','0')) AS F15,

    ' ' AS F16,

    sph.OMPRESC.COMPDRUG AS F17,

    sph.OMPRESCRFL.DAW AS F18,

    CONVERT(varchar(10),replace(convert(char(10),space(10-len(REPLACE(sph.PBCLMDTL.DISPQTY2, '.', ''))) +

    REPLACE(sph.PBCLMDTL.DISPQTY2, '.', '')),' ','0')) AS F19,

    CONVERT(varchar(3),replace(convert(char(3),space(3-len(CAST(sph.PBCLMDTL.DAYSUP AS Varchar))) + CAST(sph.PBCLMDTL.DAYSUP AS

    Varchar)),' ','0')) AS F20,

    '12' AS F21,

    sph.DOCMAS.DEA_NUMBER AS F22,

    CASE WHEN sph.IVORALDRG.K017 = 'O' THEN 'O' WHEN sph.IVORALDRG.K017 = 'P' THEN 'O' ELSE 'C' END F23, /* Pulling OTC

    verification from MediSpan drug database */

    dbo.ADJUSTMENT(sph.PBCLMDTL.CLMTYPE) AS F24, /* Function used here & in overpunch fields to define negative vs. positive

    values */

    'P' AS F25,

    ' ' AS F26,

    ' ' AS F27,

    CONVERT(varchar(8),replace(convert(char(8),space(8-len(dbo.OVERPUNCH(sph.PBCLMDTL.COST2,

    dbo.ADJUSTMENT(sph.PBCLMDTL.CLMTYPE)))) + dbo.OVERPUNCH(sph.PBCLMDTL.COST2, dbo.ADJUSTMENT(sph.PBCLMDTL.CLMTYPE))),' ','0'))

    AS F28,

    CONVERT(varchar(8),replace(convert(char(8),space(8-len(dbo.OVERPUNCH(@DispFee, dbo.ADJUSTMENT(sph.PBCLMDTL.CLMTYPE)))) +

    dbo.OVERPUNCH(@DispFee, dbo.ADJUSTMENT(sph.PBCLMDTL.CLMTYPE))),' ','0')) AS F29,

    '0000000{' AS F30,

    '0000000{' AS F31,

    '0000000{' AS F32,

    '0000000{' AS F33,

    '0000000{' AS F34,

    '0000000{' AS F35,

    '0000000{' AS F36,

    CONVERT(varchar(8),replace(convert(char(8),space(8-len(dbo.OVERPUNCH(sph.PBCLMDTL.COST2 + @DispFee,

    dbo.ADJUSTMENT(sph.PBCLMDTL.CLMTYPE)))) + dbo.OVERPUNCH(sph.PBCLMDTL.COST2 + @DispFee,

    dbo.ADJUSTMENT(sph.PBCLMDTL.CLMTYPE))),' ','0')) AS F37,

    '0000000{' AS F38, /* OTC drugs are excluded */

    /* Stop PDE fields */

    RTRIM(sph.PATMAS.FIRST_NAME) AS PDE_Fname,

    RTRIM(sph.PATMAS.LAST_NAME) AS PDE_Lname,

    sph.PATMAS.CUSNO AS PDE_Patient,

    RTRIM(sph.INVMAS.DESCR1) AS PDE_Drug,

    sph.PBCLMDTL.DISPQTY2 AS PDE_QtyDispensed,

    sph.PBCLMDTL.DAYSUP AS PDE_DaySupply,

    sph.PBCLMDTL.COST2 AS PDE_COST,

    sph.PBCLMDTL.CLMTYPE AS PDE_ClaimType,

    sph.OMPRESCRFL.INSNUM AS PDE_PlanType,

    (Left(CAST(sph.PATMAS.PSSN AS VARCHAR(15)),3)+ '-' + SUBSTRING(CAST(sph.PATMAS.PSSN AS VARCHAR(15)),4,2) + '-' +

    RIGHT(CAST(sph.PATMAS.PSSN AS VARCHAR(15)),4)) AS PDE_SSN

     

    At this time I'm getting a "Incorrect syntax near '.0'.", from the end of the Left (case

    would anyone have a idea where I can find a good debug for syntax errors?

     

    Thanks,

  • Take out the statements in the SELECT list one at a time and see when it works. Then you'll know which line it is breaking on.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply