1
Reply

SQL

anad aramandla

anad aramandla

Jul 2 2011 3:55 AM
1.4k
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;   

Answers (1)