I need help with fixing this query please!



  • I need to figure out what is wrong with this query. I get this error when running the query: "Msg 8169, Level 16, State 2, Line 1 Conversion failed when converting from a character string to uniqueidentifier."

    SELECT p.first_name, 
        p.last_name, 
        p.date_of_birth, 
        REPLACE(LTRIM(REPLACE(PT.med_rec_nbr,0,' ')),' ',0), 
        PE.enc_timestamp, 
        PD.document_desc,PD.template_file, 
        PE.rendering_provider_id, 
        PM.description, 
        FX.fax_status, 
        FX.to_fax, 
        FX.to_name, 
        isnull(R.efRule,0) 
    

    FROM patient_encounter PE
    INNER JOIN patient_documents PD
    ON PE.enc_id=PD.enc_id
    INNER JOIN person P
    ON PE.person_id=p.person_id
    INNER JOIN patient PT
    ON PE.person_id=PT.person_id
    AND PE.practice_id=PT.practice_id
    INNER JOIN provider_mstr PM
    ON PE.rendering_provider_id=PM.provider_id
    LEFT JOIN (
    SELECT to_name, to_fax, doc_id,fax_status from fax_result
    UNION ALL
    SELECT fax_to_name, 'TASKED', document_id, 'SENT'
    FROM NGPlugin_Auto_Fax_Hist_
    WHERE job_type = 'T'
    ) FX
    ON PD.document_id=FX.doc_id
    LEFT JOIN (
    SELECT FAX.provider_id,FA.document_name,
    '1' as 'efRule'
    FROM NGPlugin_Auto_Fax_Assign_ FA
    INNER JOIN NGPlugin_AF_Assign_Xref_ FAX
    ON FA.rule_id=FAX.rule_id
    ) R
    ON PE.rendering_provider_id=R.provider_id
    AND PD.template_file=R.document_name



  • We can't help you without knowing your tables structures.

    What might be wrong, is either:

    1. You're joining on fields of two different types, eg. maybe patient_encounter.enc_id is a GUID and patient_documents.enc_id is a varchar or vice versa, or template_file is a GUID while document_name is a varchar, etc.

    OR

    1. Problem is in UNION if types from fax_result are not matching types from NGPlugin_Auto_Fax_Hist_



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2