错误：由作为前pression一个子查询返回多行 [英] ERROR: more than one row returned by a subquery used as an expression

问题描述

我已经搜查，搜查..我有一个用于运行PSQL查询，每天都在通过电子邮件发送结果的bash脚本。该数据库不更新，直到午夜，我的bash脚本之前传递到查询当天的变量。当我使用通过可变否则，如果我把日期查询手动运行良好，我只收到此错误。不太清楚，因为我还在学习的psql和bash。

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.

下面是bash脚本：

#!/bin/bash
NOWDATE=`date +%Y-%m-%d -d "yesterday"`
SUBDATE=`date '+%B %e, %G'`
DIR=/file/report/
FILE=file-$NOWDATE.csv
RECIPIENT=[e-mail]

PGPASSWORD=passwrod psql -w -h host -p 5432 -d database -U user -o $DIR/$FILE <<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">'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") >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")>'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 [e-mail] $RECIPIENT

这里的SQL，重新格式化为可读性。

Here's the SQL, reformatted for readability.

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">'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") > 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")>'123456'  
   and extrTable('day' from '$NOWDATE'::timestamp) = ac."DesiredBillingDate" 
   and ac."ID" = als."Table_AccountID"
 ORDER BY "RollUp" ASC

在从服务器上的命令行运行就这样它吐出错误：
错误：由作为前pression一个子查询返回多行

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

我AP preciate的帮助下，这个社会是最好的..很抱歉的格式，它来自于复制粘贴。

I appreciate the help, this community is the best.. Sorry for the formatting, it came from the copy paste.

推荐答案

当在SELECT部分​​子查询，例如，（ SELECT A，B（选择D C ...）），则使用此必须返回有一个值。会返回一个错误，因为一个子查询返回多行更多。检查所有的子查询，以确保这些不返回多个行。添加 LIMIT 1 条款，如果它是可以接受的，不止一个值存在，但只有一个是采取。

When a subquery in the SELECT part, e.g., (SELECT a, b, (SELECT c from d ...)) of a query is used then this must return one value. An error is returned because one the subqueries returns more than one row. Check all the subqueries to ensure that these do not return more than one row. Add a LIMIT 1 clause if it is acceptable that more than one value exists, but only one is taken.

这篇关于错误：由作为前pression一个子查询返回多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！