Hi pls help for below query,how to write the sp in sql. when i try i am getting syntax error.
function getCorruptFilesList($file_date,$account_id=NULL,$client_id=NULL) - Start Here
{
$account_id_condition = ($account_id)?' AND cca.account_id = '.$account_id:NULL;
$client_id_condition = ($client_id)?' AND cca.client_id = '.$client_id:NULL;
$query="SELECT cca.account_id, cca.account_name, avf.file_name , CASE WHEN avf.is_corrupt IS TRUE THEN 'Corrupted'::text WHEN avf.is_notrequired IS TRUE THEN 'Not Required'::text END AS status , vvc.company_name as vendor FROM admin.voice_filedetails avf JOIN admin.voice_filetrans avt ON avf.file_id = avt.file_id AND avt.thead_id IN (1,2,4) JOIN client.client_accounts cca USING(account_id) LEFT JOIN vendor.vendor_contacts vvc ON vvc.vendor_id = avt.vendor_id WHERE avf.file_date = '".$file_date."' ".$client_id_condition.$account_id_condition." AND (avf.is_corrupt = 'TRUE' OR avf.is_notrequired ='TRUE') ORDER BY cca.account_name ";
$arr = $this->returnQueryArray($query);
return $arr;