SELECT
CONCAT( "" ,
dm.demographic_no, "" ) As "Dem#",
concat ('', be.billing_no , "") as "inv#" ,
CASE bh.status
when "O" then 'BILL OHIP'
when "B" then 'SENT (SUBMITTED)'
when "S" then 'SETTLED'
END as Status,
substring(bh.comment1, 1,10) as comment,
substring(concat(dm.last_name, ", ", dm.first_name), 1 , 25) as Name,
be.hin , be.ver as oldVC, dm.ver as CurVC ,
be.code as "Code", be.code_date as "BillDate", be.claim_error as Err ,
be.code_error as CErr , bec.description as "Error Description"
FROM
billing_on_eareport be , billing_on_cheader1 bh , demographic dm
, billing_on_errorCode bec
WHERE
be.billing_no = bh.id and dm.demographic_no = bh.demographic_no
and (be.claim_error = bec.code or be.code_error = bec.code) and (
be.code_error <> "" or be.claim_error <> "" )
and be.providerohip_no LIKE "{ohip_no}"
and be.code_date >= "{dateFrom}"
and be.code_date <= "{dateTo}"
ORDER BY
be.code_date DESC;
SELECT "%" AS ohip_no, 'all providers' AS provider
UNION
SELECT ohip_no, concat(last_name,',',first_name,'(', ohip_no,')') from provider
WHERE status='1'
AND provider_type='doctor'
and ohip_no > 1
ORDER BY ohip_no;