Board index » delphi » Query Challenge

Query Challenge

Hello,

I need to determine what's wrong with the following snippet of SQL. This
snippet is part of a larger select procedure.

<sql>
    FOR SELECT FT.FRTEMPLID, UPPER(FT.TEMPLATENAME),
        UPPER(FT.DESCRIPTION), MAX(FT.REVISION)
      FROM T_FRTEMPL FT
      GROUP BY FT.TEMPLATENAME
      ORDER BY FT.TEMPLATENAME ASC
      INTO :R_FRTEMPLID, :R_TEMPLNAM, :R_DESC, :R_REV
    DO BEGIN

      SUSPEND;
    END
</sql>

I am trying to use a "MAX" and a "GROUP BY" clause in a for..do select loop.
What am I doing wrong here? Or is there an alternative to this?

 

Re:Query Challenge


Quote
mpowell wrote:
> I need to determine what's wrong with the following snippet of SQL. This
> snippet is part of a larger select procedure.

Every selected field, except the Max(), has to be grouped by. At least, that is
what I can see from your query.
Can you post the error message?

hth,

dog

--
___________________________________________
IMPROART
Rua Emiliano Perneta, 297 - 6 And - Cj. 63
CEP 80050-010 - Curitiba-PR - Brasil
Tel/fax.: 55-41-323-7558

Re:Query Challenge


try with
........

GROUP BY FT.FRTEMPLID, FT.TEMPLATENAME, FT.DESCRIPTION
ORDER BY ...

-ioan

Re:Query Challenge


Yes, the error message was "multiple rows in singleton select" or something
to that effect. I will try grouping by the balance of the selected fields.
Thanks.

Quote
"Douglas Tosi" <doug...@improart.com.br> wrote in message

news:3AC12503.D10431BC@improart.com.br...
Quote
> mpowell wrote:

> > I need to determine what's wrong with the following snippet of SQL. This
> > snippet is part of a larger select procedure.

> Every selected field, except the Max(), has to be grouped by. At least,
that is
> what I can see from your query.
> Can you post the error message?

> hth,

> dog

> --
> ___________________________________________
> IMPROART
> Rua Emiliano Perneta, 297 - 6 And - Cj. 63
> CEP 80050-010 - Curitiba-PR - Brasil
> Tel/fax.: 55-41-323-7558

Re:Query Challenge


Quote
mpowell wrote:
> Yes, the error message was "multiple rows in singleton select" or something
> to that effect. I will try grouping by the balance of the selected fields.

The group by was wrong, but this error message indicates another thing. When you
do a Select into inside an SP, the SP expects your select to return only one
row. If it returns more than one, that is the error message you get.
As your example shows the FOR Select into correctly, i suppose this error comes
from another part of your SP.
Can you show us the complete SP?

hth,

dog

--
___________________________________________
IMPROART
Rua Emiliano Perneta, 297 - 6 And - Cj. 63
CEP 80050-010 - Curitiba-PR - Brasil
Tel/fax.: 55-41-323-7558

Re:Query Challenge


Here is the entire stored procedure.

<sp>
CREATE PROCEDURE "PR_SELECT_FRTEMPL"
(
  P_ALL SMALLINT
)
RETURNS
(
  R_FRTEMPLID DOUBLE PRECISION,
  R_TEMPLNAM VARCHAR(48),
  R_DESC VARCHAR(255),
  R_REV INTEGER
) AS
/* Date    : 1/15/2001
   Changed :  */
BEGIN

  /* 1. Select all frame templates
     2. Select only the most recent template revisions */

  IF (P_ALL=1)
  THEN BEGIN

    /* 1. */ FOR SELECT FT.FRTEMPLID, UPPER(FT.TEMPLNAME),
        UPPER(FT.DESCRIPTION), FT.REVISION
      FROM T_FRTEMPL FT
      ORDER BY FT.TEMPLNAME ASC, FT.REVISION DESC
      INTO :R_FRTEMPLID, :R_TEMPLNAM, :R_DESC, :R_REV
    DO BEGIN
      SUSPEND;
    END
  END
  ELSE BEGIN

    /* 2. */ FOR SELECT MAX(FT.FRTEMPLID), UPPER(FT.TEMPLNAME),
        UPPER(FT.DESCRIPTION), MAX(FT.REVISION)
      FROM T_FRTEMPL FT
      GROUP BY FT.TEMPLNAME, FT.DESCRIPTION
      ORDER BY FT.TEMPLNAME ASC
      INTO :R_FRTEMPLID, :R_TEMPLNAM, :R_DESC, :R_REV
    DO BEGIN

      SUSPEND;
    END
  END
END ^
</sp>

Quote
"Douglas Tosi" <doug...@improart.com.br> wrote in message

news:3AC24A05.C614B50@improart.com.br...
Quote
> mpowell wrote:

> > Yes, the error message was "multiple rows in singleton select" or
something
> > to that effect. I will try grouping by the balance of the selected
fields.

> The group by was wrong, but this error message indicates another thing.
When you
> do a Select into inside an SP, the SP expects your select to return only
one
> row. If it returns more than one, that is the error message you get.
> As your example shows the FOR Select into correctly, i suppose this error
comes
> from another part of your SP.
> Can you show us the complete SP?

> hth,

> dog

> --
> ___________________________________________
> IMPROART
> Rua Emiliano Perneta, 297 - 6 And - Cj. 63
> CEP 80050-010 - Curitiba-PR - Brasil
> Tel/fax.: 55-41-323-7558

Re:Query Challenge


There doesn't seem to be anything wrong with the SP code, except for that GROUP
BY issue.
Maybe the problem is in the way you Select From PR_SELECT_FRTEMPL.
Are you using this SP inside another one? Do you get the error when trying to
run the SP or when compiling it?

dog
--
___________________________________________
IMPROART
Rua Emiliano Perneta, 297 - 6 And - Cj. 63
CEP 80050-010 - Curitiba-PR - Brasil
Tel/fax.: 55-41-323-7558

Other Threads