• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1085
  • Last Modified:

SQL Error - An expression of non-boolean type

Complete error:
An expression of non-boolean type specified in a context where a condition is expected, near 'and'.
SQL Code:
set nocount on
 
Select
Record_Type = 'UD',
right('00000000000' + cast (row_number() over (order by DrugIdentifier.Identifier) as varchar (11)),11) Line_number,
Data_Level = 'PP',
Plan_ID_Qualifier = 'C',
Plan_ID_Code = right(PrimaryPlan.ThirdPartyId,17), -- short id for ins company length of 17 alpha/numeric
Plan_Name = left(cast(PrimaryPlan.PlanName as char (30))+'                              ',30), --length of 30 alpha/numeric
Pharmacy_ID_Qualifier = 'N',
Pharmacy_ID_Code = cast(StoreProfile.ProviderId as char (17)), -- length of 17
Pharmacy_Zip_Code = cast(StoreProfile.Zip as char (9)), -- length of 9 numeric
Product_Code_Qualifier = 'N',
Product_Code = cast(DrugIdentifier.Identifier as char (17)),  -- length of 17
Prduct_Description = cast(Drug.Description as char (30)), --length of 30
DAW = ' ',
Total_Units = RIGHT(REPLACE('00000000000000' + cast(disp.DispensedQuantity as varchar(13)),'.',''),14),  -- length of 14 total number of vials per script numeric + sign = 15
Total_Units_sign = 
Case
       when disp.status = 7 then ' '
       when disp.status = 10 then '-'  
end, 
Unit = 'EA',
Dosage = '  ', -- will be blank
Diagnosis = '      ', -- will be blank
Rebate_Days = 
Case
      When disp.DaysSupply < 28  then '028'
      else right('000' + cast(disp.DaysSupply as char (2)),3)
end,
Rebate_Days_Sign = 
Case 
        when claim.ClaimState = 2 and claim.RequestTransactionCode = 'B1' then ' '
        when claim.ClaimState = 3 and claim.RequestTransactionCode = 'B2' then '-'
end,
Prescription_Type =   --- checks for new/refill = 1 , if reversal then -1 numeric
Case 
		When claim.ClaimState = 2 and claim.RequestTransactionCode = 'B1' then '1'
        When claim.ClaimState = 3 and claim.RequestTransactionCode = 'B2' then '-1'
    end,
Num_of_script_sign = 
Case
       when claim.ClaimState = 2 and claim.RequestTransactionCode = 'B1' then ' '
       when claim.ClaimState = 3 and claim.RequestTransactionCode = 'B2' then '-'  
end, 
Number_of_Prescriptions = '0000001',  -- length of 7 + sign = 8
Script_num_sign = 
Case
       when claim.ClaimState = 2 and claim.RequestTransactionCode = 'B1' then ' '
       when claim.ClaimState = 3 and claim.RequestTransactionCode = 'B2' then '-'  
end, 
 
Prescription_Number = right('0000000' + cast(rx.RxNumber as char (7)),7),  -- length of 7 + sign = 8
convert(varchar,disp.DateFilled,112) as Date_Filled,
Reimbursement_Date = '        ', -- will be blank (8)
Therapeutic_Class_Qualifier = ' ', -- will be blank (1)
Therapeutic_Class = '                 ',  -- length of 17 blank
Therapeutic_Class_Description = '                              ',  --length of 30 blank
Plan_Reimbursement = ' ', -- will be blank
Plan_Reimbursement_Amount = '            ',  -- length of 12 blank
Patient_Amount = '            ', -- length of 12 blank
New_Refill_Code = 
Case 
		when (disp.refillnumber = 0) then  '00'
		else right('00' + cast(disp.refillnumber as char (1)),2) 
    end,
Record_Purpose_Ind = 'R',
Rebate_Per_Unit = '            ',    -- LENGTH OF 12 blank
Requested_Rebate = '            ',   -- length of 12 blank
Formulary_Code = '                 ', --length of 17 blank
Prescriber_ID_Qualifier = ' ', -- will be blank
Prescriber_ID = '          ',    -- length of 10 blank
Encrypted_Patient_ID_Code = '                 ',    -- length of 17 blank
Claim_Number = '                    '    -- length of 20 blank
 
 From rx rx
      Join refill disp on rx.rxid = disp.rxid
      Join claim claim on disp.refillid = claim.refillid
      join drug on drug.drugid = disp.drugid
      join drugidentifier on drugidentifier.drugid = drug.drugid and identifiercode = 3
      join vw_Reporting_FirstPaidClaim PrimaryClaim on PrimaryClaim.refillid = disp.refillid  
      join ThirdParty PrimaryPlan on PrimaryPlan.ThirdPartyId = PrimaryClaim.ThirdPartyId
      left outer join vw_Reporting_LastPaidClaim SecondaryClaim on SecondaryClaim.refillid = disp.refillid and 
                                                             SecondaryClaim.ClaimId <> PrimaryClaim.ClaimId
      left outer join ThirdParty SecondaryPlan on SecondaryPlan.ThirdPartyId = SecondaryClaim.ThirdPartyId
      Inner Join StoreProfile on StoreProfile.IsPrimary = 1
Where dateadd(mm,-3,cast(cast(month(getdate()) as varchar)+'/01/'+cast(year(getdate()) as varchar) as datetime))  --first of 3 months ago  
  and cast(cast(month(getdate()) as varchar)+'/01/'+cast(year(getdate()) as varchar) as datetime) --first of this month
  and drugidentifier.Identifier in ('57844071319')
  and claim.IsRelevant = 1 
and (PrimaryPlan.PlanName not like 'medco%' 
and PrimaryPlan.PlanName not like 'optima%' 
and PrimaryPlan.PlanName not like 'PCS%' 
and PrimaryPlan.PlanName not like 'tricare%'
and PrimaryPlan.PlanName not like 'Wellcare%')
 
order by disp.DateFilled

Open in new window

0
bwanagat
Asked:
bwanagat
  • 3
  • 3
1 Solution
 
Craig YellickDatabase ArchitectCommented:
I am a major fan of very, very careful formatting of complex queries. If you break down the WHERE clause into widely separated parts you'll see that the two date-related clauses are not compared to anything. You've performed a DateAdd and now need to compare them to something. See "need comparison" comments below.
Where 
 
dateadd(mm,-3,cast(cast(month(getdate()) as varchar)
+'/01/'
+cast(year(getdate()) as varchar) as datetime))  --<< Need comparison
 
  and 
 
cast(cast(month(getdate()) as varchar)
+'/01/'
+cast(year(getdate()) as varchar) as datetime)  --<< Need comparison
 
  and drugidentifier.Identifier in ('57844071319')
  and claim.IsRelevant = 1 
  and (
    PrimaryPlan.PlanName not like 'medco%' 
and PrimaryPlan.PlanName not like 'optima%' 
and PrimaryPlan.PlanName not like 'PCS%' 
and PrimaryPlan.PlanName not like 'tricare%'
and PrimaryPlan.PlanName not like 'Wellcare%'
)

Open in new window

0
 
bwanagatAuthor Commented:
i'm  trying to systematically compare data that falls within a querter period of time
0
 
Craig YellickDatabase ArchitectCommented:
You'll have to introduce a comparison along the lines of:

   where SomeDate between ( your calc #1 ) and ( your calc #2 )

You are constructing two dates but not comparing them to anything. That is the source of the error.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bwanagatAuthor Commented:
can you show me example
0
 
Craig YellickDatabase ArchitectCommented:
I don't have your database schema handy, so here's a working example that uses the Northwind sample database. I edited one of the Orders records to use a date of 3/1/2009 so it would show up in the results.
select * from orders 
where 
  OrderDate between 
 
dateadd(mm,-3,cast(cast(month(getdate()) as varchar)
+'/01/'
+cast(year(getdate()) as varchar) as datetime))  
 
  and 
 
cast(cast(month(getdate()) as varchar)
+'/01/'
+cast(year(getdate()) as varchar) as datetime)  

Open in new window

0
 
bwanagatAuthor Commented:
THank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now