September 6, 2007 at 8:21 am
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,
September 6, 2007 at 8:40 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy