>vp_cClient_Account_Number = "" >vp_cLast = "GREEN%" >vp_cFirstName = "" >vp_cMiddle = "" >vp_cSSN = "" >vp_cAreaCode = "" >vp_cExchange = "" >vp_cFour = "" >vp_cExchange_Four = "" > >SELECT MIN(Trans.ctrans_pk) AS ctrans_pk, ; > Trans.cclient_account_number, ; > MIN(Patients.ddob) AS ddob, ; > MIN(Trans.tadmit_date) AS dos, ; > MIN(PADR(NVL(ALLTRIM(Names.cl_name)+", ", "")+NVL(ALLTRIM(Names.cf_name)+" ", "")+NVL(Names.cm_initial, ""), 50)) AS cpatient_name, ; > MIN(Product_lines.cproduct_line) AS cproduct_line, ; > MIN(Trans.cresolution_codes_fk) AS cresolution_codes_fk, ; > MIN(Trans_status_codes.cstatus_codes_fk) AS cstatus_codes_fk, ; > MIN(PADR(NVL(Phones.carea_code, "")+NVL(Phones.cexchange, "")+NVL(Phones.clast_four, ""), 10)) AS cphone ; > FROM mmviscollect!patients INNER ; > JOIN mmviscollect!trans ; > ON Patients.cpatients_pk = Trans.cpatients_fk ; > LEFT JOIN mmviscollect!phones ; > ON Patients.cpatients_pk = Phones.cpointer_fk INNER ; > JOIN mmviscollect!trans_status_codes ; > ON Trans.ctrans_pk = Trans_status_codes.ctrans_fk INNER ; > JOIN mmviscollect!product_lines ; > ON Product_lines.cproduct_lines_pk = Trans.cproduct_lines_fk INNER ; > JOIN mmviscollect!names ; > ON Patients.cpatients_pk = Names.cpointer_fk ; > WHERE (?vp_cClient_Account_Number=="" OR Trans.cclient_account_number LIKE ?vp_cClient_Account_Number ) ; > AND Trans_status_codes.iactive_flag = 1 ; > AND (?vp_cLast=="" OR Names.cl_name LIKE ?vp_cLast) ; > AND (?vp_cFirstName=="" OR Names.cf_name LIKE ?vp_cFirstName ) ; > AND (?vp_cMiddle=="" OR Names.cm_initial LIKE ?vp_cMiddle ) ; > AND (?vp_cSSN=="" OR Patients.cssn = ?vp_cSSN ) ; > AND (?vp_cAreaCode=="" OR Phones.carea_code = ?vp_cAreaCode ) ; > AND (?vp_cExchange_Four=="" OR Phones.cexchange+Phones.clast_four = ?vp_cExchange_Four ) ; > GROUP BY Trans.cclient_account_number>
>>>SELECT MIN(Trans.ctrans_pk) AS ctrans_pk,; >>> Trans.cclient_account_number, MIN(Patients.ddob) AS ddob,; >>> MIN(Trans.tadmit_date) AS dos,; >>> MIN(PADR(NVL(ALLTRIM(Names.cl_name)+", ","")+NVL(ALLTRIM(Names.cf_name)+" ","")+NVL(Names.cm_initial,""),50)) AS cpatient_name,; >>> MIN(Product_lines.cproduct_line) AS cproduct_line,; >>> MIN(Trans.cresolution_codes_fk) AS cresolution_codes_fk,; >>> MIN(Trans_status_codes.cstatus_codes_fk) AS cstatus_codes_fk,; >>> MIN(PADR(NVL(Phones.carea_code,"")+NVL(Phones.cexchange,"")+NVL(Phones.clast_four,""),10)) AS cphone; >>> FROM ; >>> mmviscollect!patients ; >>> INNER JOIN mmviscollect!trans ; >>> ON Patients.cpatients_pk = Trans.cpatients_fk ; >>> LEFT OUTER JOIN mmviscollect!phones ; >>> ON Patients.cpatients_pk = Phones.cpointer_fk ; >>> INNER JOIN mmviscollect!trans_status_codes ; >>> ON Trans.ctrans_pk = Trans_status_codes.ctrans_fk ; >>> INNER JOIN mmviscollect!product_lines ; >>> ON Product_lines.cproduct_lines_pk = Trans.cproduct_lines_fk ; >>> INNER JOIN mmviscollect!names ; >>> ON Patients.cpatients_pk = Names.cpointer_fk; >>> WHERE ( ( ( ( ( ( Trans.cclient_account_number LIKE ( ?vp_cClient_Account_Number ); >>> AND Trans_status_codes.iactive_flag = ( 1 ) ); >>> AND Names.cl_name LIKE ( ?vp_cLast ) ); >>> AND Names.cf_name LIKE ( ?vp_cFirstName ) ); >>> AND Names.cm_initial LIKE ( ?vp_cMiddle ) ); >>> AND Patients.cssn LIKE ( ?vp_cSSN ) ); >>> AND ( Phones.carea_code LIKE ( ?vp_cAreaCode ); >>> OR Phones.carea_code IS NULL ) ); >>> AND ( ( Phones.cexchange+Phones.clast_four ) LIKE ( ?vp_cExchange_Four ); >>> OR ( Phones.cexchange+Phones.clast_four ) IS NULL ); >>> GROUP BY Trans.cclient_account_number>>>