title: ERROR: more than one row returned by a subquery used as an expression
body: <p>I have searched and searched.. I have a bash script that is used to run a psql query and email the results on a daily basis. The DB does not update till midnight and my bash script passes a variable to the query for the day prior. I am getting this error only when I use the passed variable otherwise if I put the date in the query manually it runs fine. Not quite sure for I am still learning psql and bash.</p>
  
  <p>Here is the bash script:</p>
  
  <pre><code>#!/bin/bash
  NOWDATE=`date +%Y-%m-%d -d "yesterday"`
  SUBDATE=`date '+%B %e, %G'`
  DIR=/file/report/
  FILE=file-$NOWDATE.csv
  RECIPIENT=email@mail.com
  
  PGPASSWORD=passwrod psql -w -h host -p 5432 -d database -U user -o $DIR/$FILE &lt;&lt;EOF
  select distinct als."Table_AccountID",
     (select  "Table_val_AccountStatusID" from  "Table_log_AccountStatus"  
      where "Table_AccountID" = als."Table_AccountID" order by  "Date" desc limit 1)
      as "Table_val_AccountStatusID",
  
     CASE  
     when (select count(*) from  "Table_UsageHistory"  cfuh  
           where cfuh."Disk"&gt;'123456' and date_trunc('day',cfuh."Created") = date_trunc('day','$NOWDATE'::timestamp)
           -- -'1day':: interval
           and extrTable('day' from "Created"::timestamp) = ac."DesiredBillingDate" 
           and date_trunc('day', "Created"::timestamp) = date_trunc('day', '$NOWDATE'::timestamp)
           and cfuh."Table_AccountID" in (
               select distinct "Table_AccountID" from "Table_Usage" 
               where date_trunc('day', "Timestamp"::timestamp) = date_trunc('day','$NOWDATE'::timestamp)
               and "Table_AccountID" = cfuh."Table_AccountID")
           and cfuh."Table_AccountID" = als."Table_AccountID") &gt;0
     then 'Y'
     else 'N'
     end as "RollUp",
  
     (select distinct bc."ID" from "BIL_BillableCharge" bc, "Table_UsageHistory" cfh 
      where date_trunc('day',bc."Date"::timestamp) = date_trunc('day',cfh."Created"::timestamp)
      and bc."Table_AccountID" = cfh."Table_AccountID" and bc."BIL_val_InvoiceItemTypeID" = '23' 
      and extrTable('month' from "Created"::timestamp) = extrTable('month' from '$NOWDATE'::timestamp)
      and  extrTable('year' from "Created"::timestamp) = extrTable('year' from '$NOWDATE'::timestamp)
      and cfh."Table_AccountID" = als."Table_AccountID") as "BillableChargeID"
  
      from "Table_log_AccountStatus" als, "Table_Account" ac
      group by als."Table_AccountID",  ac."ID", ac."DesiredBillingDate"
      having (select distinct "Disk" from "Table_UsageHistory" cfu
      where  date_trunc('day', cfu."Created") = date_trunc('day','$NOWDATE'::timestamp)
      and ac."ID" = cfu."Table_AccountID")&gt;'123456'
      and extrTable('day' from '$NOWDATE'::timestamp) = ac."DesiredBillingDate"
      and ac."ID" = als."Table_AccountID"
      ORDER BY "RollUp" ASC
  EOF
  
  sed -i '2d' $DIR/$FILE |
     mailx -a $DIR/$FILE -s " Report for $SUBDATE" -r email@anotheremail.com $RECIPIENT
  </code></pre>
  
  <p>Here's the SQL, reformatted for readability.</p>
  
  <pre><code>select distinct 
    als."Table_AccountID",
  
    (select  "Table_val_AccountStatusID" 
     from  "Table_log_AccountStatus"  
     where "Table_AccountID" = als."Table_AccountID" 
     order by  "Date" desc limit 1) as "Table_val_AccountStatusID",
  
     CASE when 
          (select count(*) 
           from  "Table_UsageHistory"  cfuh  
           where  cfuh."Disk"&gt;'123456' 
             and date_trunc('day',cfuh."Created") = date_trunc('day','$NOWDATE'::timestamp) -- -'1day':: interval
             and extrTable('day' from "Created"::timestamp) = ac."DesiredBillingDate" 
             and date_trunc('day', "Created"::timestamp) = date_trunc('day', '$NOWDATE'::timestamp)
             and cfuh."Table_AccountID" in 
                (select distinct "Table_AccountID" 
                 from "Table_Usage" 
                 where date_trunc('day', "Timestamp"::timestamp) = date_trunc('day','$NOWDATE'::timestamp)
                   and "Table_AccountID" = cfuh."Table_AccountID")
                   and cfuh."Table_AccountID" = als."Table_AccountID") &gt; 0
          then 'Y'
          else 'N'
     end as "RollUp",
  
    (select distinct bc."ID" 
     from "BIL_BillableCharge" bc, "Table_UsageHistory" cfh 
     where date_trunc('day',bc."Date"::timestamp) = date_trunc('day',cfh."Created"::timestamp)
       and bc."Table_AccountID" = cfh."Table_AccountID" and bc."BIL_val_InvoiceItemTypeID" = '23' 
       and extrTable('month' from "Created"::timestamp) = extrTable('month' from '$NOWDATE'::timestamp)
       and  extrTable('year' from "Created"::timestamp) = extrTable('year' from '$NOWDATE'::timestamp)
       and cfh."Table_AccountID" = als."Table_AccountID") as "BillableChargeID"
  
  from "Table_log_AccountStatus" als, "Table_Account" ac
  group by als."Table_AccountID",  ac."ID", ac."DesiredBillingDate"
  having (select distinct "Disk" 
          from "Table_UsageHistory" cfu  
          where  date_trunc('day', cfu."Created") = date_trunc('day','$NOWDATE'::timestamp)
            and ac."ID" = cfu."Table_AccountID")&gt;'123456'  
     and extrTable('day' from '$NOWDATE'::timestamp) = ac."DesiredBillingDate" 
     and ac."ID" = als."Table_AccountID"
   ORDER BY "RollUp" ASC
  </code></pre>
  
  <p>When ran just like this from command line on the server it spits out the error:
  ERROR:  more than one row returned by a subquery used as an expression</p>
  
  <p>I appreciate the help, this community is the best.. Sorry for the formatting, it came from the copy paste.</p>