Concat_WS & Coalesce

Tips submitted by PHPMaker users

Concat_WS & Coalesce

Postby randy » Sat Oct 15, 2016 2:20 am

Issue of groups for report headers wanted multiple fields in group report form only gives five or so.
used CONCAT_WS( ) and COALESCE() to make a group filed joining multiple fields and take care of Null fields - Null in any fd otherwise returns NULL only.

SELECT Concat_WS(CONVERT(Coalesce(tbl_pt_main.Dep_name, ' ') USING utf8),
Coalesce(tbl__relation_master.relation, ' '),
Coalesce(tbl__service_master.service_no, ' '),
CONVERT(Coalesce(tbl_pt_main.service_number, ' ') USING utf8),
CONVERT(Coalesce(tbl_pt_main.first_name_service_pers, ' ') USING utf8),
CONVERT(Coalesce(tbl_pt_main.last_name_service_pers, ' ') USING
utf8)) AS Details,
tbl_testmaster.Test_Name AS Test_Name,
tbl_pt_reps.Test_result AS Test_result,
tbl_testmaster.units AS units,
tbl_testmaster.nor_adult_male AS nor_adult_male,
tbl_testmaster.nor_adult_female AS nor_adult_female,
tbl_pt_reps.Remarks AS Remarks,
tbl_pt_reps.dt_order AS dt_order,
tbl_pt_main.hid AS hid,
tbl_pt_reps.id AS id,
tbl__unit_master.unit AS unit
FROM ((((tbl_pt_main
JOIN tbl_pt_reps ON tbl_pt_main.hid = tbl_pt_reps.hid)
JOIN tbl_testmaster ON tbl_testmaster.id = tbl_pt_reps.id_tbl_tdets AND
tbl_testmaster.id_path_sec = tbl_pt_reps.sec_id)
JOIN tbl__unit_master ON tbl__unit_master.id = tbl_pt_main.unit)
JOIN tbl__service_master ON tbl__service_master.sid =
tbl_pt_main.service_no_prefix)
JOIN tbl__relation_master ON tbl__relation_master.rel_no =
tbl_pt_main.relation

Works.
randy
 
Posts: 20
Joined: Fri Jan 11, 2013 10:34 pm

Return to User Submitted Tips (PHPMaker)