Incorrect syntax near ''.0''.

• Gregg Griffin-470539

  SSC Enthusiast

  Points: 139

  More actions

  September 6, 2007 at 8:21 am

  #174620

  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,

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738063

  More actions

  September 6, 2007 at 8:40 am

  #731869

  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.