Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2014
    Posts
    2

    Unanswered: Postgres sql is returning: more than one row returned by a subquery used as an expres

    Postgres sql is returning: more than one row returned by a subquery used as an expression

    The data base format is:

    rpt_rec_num | wksht_cd | line_num | clmn_num | itm_val_num
    -------------+----------+----------+----------+---------------
    515300 | B100000 | 11800 | 00505 | 33469493
    515300 | B100000 | 11800 | 00600 | 143939
    515300 | B100000 | 11800 | 00700 | 133913
    515300 | B100000 | 11800 | 00800 | 200968
    515300 | B100000 | 11800 | 00900 | 115640
    The data base has been checked with the follow query for duplicates for both query objects, clmn_num 00300 and 00700.

    select rpt_rec_num, count(*) as sum from nmrc
    where line_num = '07300' and clmn_num = '00300' and wksht_cd = 'C000001'
    group by rpt_rec_num order by sum desc limit 5;
    Each cell of interest returns a count of 1.

    The query is:

    select n1.itm_val_num as billed,
    (select n2.itm_val_num from nmrc n2
    join nmrc n1 on n1.rpt_rec_num = n2.rpt_rec_num
    where n2.wksht_cd = 'C000001' and n2.line_num = '07300'
    and n2.clmn_num = '00300')
    from nmrc as n1
    where n1.wksht_cd = 'C000001' and n1.line_num = '07300'
    and n1.clmn_num = '00700';
    Given there are no duplicates in the database is there something in the query that is causing the multiple rows to be returned.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What does the following SQL snippet return as a result set?
    Code:
    select n2.itm_val_num
       from nmrc n2 
       join nmrc n1
          on n1.rpt_rec_num = n2.rpt_rec_num 
       where n2.wksht_cd = 'C000001'
          and n2.line_num = '07300' 
          and n2.clmn_num = '00300'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2014
    Posts
    2
    It returned 19 plus million rows. One for each row in nmrc 1.
    It returned the itm_val_num for
    "n2.wksht_cd = 'C000001'
    and n2.line_num = '07300'
    and n2.clmn_num = '00300'"
    joined unconditionally with nmrc

    there are 5800 unique rpt_rec_num in the database

    Is there a way to have condtion
    "where n2.wksht_cd = 'C000001'
    and n2.line_num = '07300'
    and n2.clmn_num = '00300'"
    work on n2 itm_val_num

    and
    where n2.wksht_cd = 'C000001'
    and n2.line_num = '07300'
    and n2.clmn_num = '00700'
    work on n1 itm_val_num
    Last edited by dansawyer; 04-25-15 at 21:22.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •