see the coding below. I am doing a UNION on two dissimilar data types, one an id and one a unique char(20) field. I find that my output for that column appears to be binary unless I do the CONCAT(ID) hack you see below. I'm concerned that has a processing cost. Is there any other way to "CAST" the id as a character? It's been a while since I have done this and also I'd value the feedback on what is the fastest processor-wise, these are some pretty big tables.
thanks!
CREATE OR REPLACE VIEW _v_subcontractors_all_tools AS
SELECT
CONCAT(a.ID) AS ID, /* <- this is what I'm referring to */
a.Relationship,
a.Category,
a.Active,
a.EditDate AS LastModified,
a.FirstName,
a.MiddleName,
a.LastName,
un_email AS Email,
a.SocSecurityNumber,
a.BirthDate,
a.Gender,
a.Race,
a.Address,
a.City,
a.State,
a.Zip,
a.Notes,
COUNT(DISTINCT b.Objects_ID) AS InHomeData,
c.EventCount AS CBCCount,
c.EventDate AS LastCBC,
d.Status,
d.PerformingAgency,
d.PerformingAgencyPhone,
d.ReferenceNumber,
d.Notes AS CBCNotes
FROM
gf_subcontractors a
LEFT JOIN bais_universal u ON a.un_username=u.un_username
LEFT JOIN gf_objects b ON a.ID=b.Subcontractors_ID AND b.ParentObject='gf_fosterhomes'
LEFT JOIN _v_subcontractors_all_tools_sub1 c ON a.ID=c._max_sub_CBC_ID_
LEFT JOIN gf_objects d ON c.EventDate=d.EventDate AND d.Objects_ID=a.ID
GROUP BY a.ID
/* staff */
UNION
SELECT
st_unusername AS ID,
'Staff' AS Relationship,
'' AS Category,
st_active AS Active,
st_editdate AS LastModified,
un_firstname AS FirstName,
un_middlename AS MiddleName,
un_lastname AS LastName,
un_email AS Email,
a.SocSecurityNumber,
a.BirthDate,
a.Gender,
a.Race,
a.Address,
a.City,
a.State,
a.Zip,
a.MisctextStaffnotes AS Notes,
COUNT(DISTINCT f.ID) AS InHomeData,
c.EventCount AS CBCCount,
c.EventDate AS LastCBC,
d.Status,
d.PerformingAgency,
d.PerformingAgencyPhone,
d.ReferenceNumber,
d.Notes AS CBCNotes
FROM
bais_staff a
LEFT JOIN bais_universal u ON a.st_unusername=u.un_username
LEFT JOIN gf_fosterhomes f ON a.st_unusername=fh_stusername
LEFT JOIN _v_subcontractors_all_tools_sub2 c ON a.st_unusername=_max_staff_CBC_ID_
LEFT JOIN gf_objects d ON c.EventDate=d.EventDate AND d.Objects_ID=a.st_unusername
GROUP BY a.st_unusername
