SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
SELECT d.* FROM ( SELECT C.CLIENT_ID, [NRIC] = isnull(n.nric, ''), [OTHER_ID] = isnull(o.other_id, '') FROM ( SELECT DISTINCT client_id FROM #database ) c LEFT JOIN ( SELECT DISTINCT CLIENT_ID, [NRIC] = CASE WHEN nric LIKE '%-%' OR len(nric) > 10 OR nric = '907865' THEN nric WHEN other_id LIKE '%-%' OR len(other_id) > 10 THEN other_id END FROM #database ) n ON c.client_id = n.client_id LEFT JOIN ( SELECT CLIENT_ID, [OTHER_ID] = nullif(( replace(( CASE WHEN len(other_id) < 10 AND other_id <> 'na' THEN other_id WHEN len(nric) < 10 AND other_id <> 'na' AND nric <> '907865' THEN nric END ), 'NA', '') ), '') FROM #database WHERE nullif(( replace(( CASE WHEN len(other_id) < 10 AND other_id <> 'na' THEN other_id WHEN len(nric) < 10 AND other_id <> 'na' AND nric <> '907865' THEN nric END ), 'NA', '') ), '') IS NOT NULL ) o ON c.client_id = o.client_id ) d LEFT JOIN ( SELECT DISTINCT [nric] = CASE WHEN nric = '907865' OR ( len(nric) > 10 AND nric <> 'na' ) THEN nric WHEN other_id = '907865' OR ( len(other_id) > 10 AND other_id <> 'na' ) THEN other_id END FROM #fraudlisting ) n ON replace(d.nric, '-', '') = replace(n.nric, '-', '') LEFT JOIN ( SELECT DISTINCT [other_id] = CASE WHEN nric <> '907865' AND len(nric) < 10 AND nric <> 'na' THEN nric WHEN other_id <> '907865' AND len(other_id) < 10 AND other_id <> 'na' THEN other_id END FROM #fraudlisting ) o ON d.other_id = o.other_id WHERE n.nric IS NOT NULL OR o.other_id IS NOT NULL ORDER BY d.client_id
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear