error: multiple rows in singleton select

Martijn Tonies

unread,

Jan 27, 2003, 7:59:18 AM1/27/03

to

"RB Smissaert" <bartsm...@blueyonder.co.uk> wrote in message
news:3e351fe3$1...@newsgroups.borland.com...
> Using IB 5.6 at work and the trial version IB 7.0 at home. The dialect
> number is 1.
> I have been looking for a free SQL tool to interact with IB and one that
> can work with both versions.
> I tried IB_WISQL and IB_SQL, but they both have problems with the following
> simple query:
>
> SELECT STAFF_INITIALS GP, COUNT(STAFF_INITIALS) NUMBER
> FROM PATIENT, STAFF
> WHERE REGISTERED_GP = STAFF_ID AND MAIN_REG_TYPE = 1
> GROUP BY STAFF_INITIALS
>
> It gives the error message: ISC error code: 335544652
> Multiple rows in singleton select.
>
> Am I doing something wrong or do I have to look for another tool?

The message comes from the db engine, not the tool.

This is a plain ugly query - use the SQL92 JOIN syntax:

FROM PATIENT JOIN STAFF ON (STAFF.col = PATIENT.col)

And better alias your columns if you plan to use multiple tables in the
resultset.

--

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

Martijn Tonies

unread,

Jan 27, 2003, 12:41:37 PM1/27/03

to

Can you show us the complete DDL for the tables?

--
Martijn

> I got used to using this syntax from working with MS Query. There is no
> problem at all when using IBConsole.
> When I use the recommended syntax like this:
>
> SELECT STAFF_INITIALS GP , COUNT(PATIENT.PATIENT_ID) NUMBER
> FROM PATIENT INNER JOIN STAFF ON (STAFF.STAFF_ID = PATIENT.REGISTERED_GP)
> WHERE PATIENT.MAIN_REG_TYPE = 1
> GROUP BY STAFF_INITIALS
>
> I get exactly the same problem. So, it doesn't seem the problem is with the
> syntax.
>
>
> RBS
>
> "Martijn Tonies" <m.tonies@upscene_remove.com> wrote in message
> news:3e35...@newsgroups.borland.com...

RB Smissaert

unread,

Jan 27, 2003, 6:19:24 PM1/27/03

to

Presume you mean the MetaData tab of the table in IBConsole. I am not sure
if it is allowed to put this on the newsgroup, but I can e-mail it to you
personally if that is ok.

Paulie

unread,

Jan 28, 2003, 6:46:38 AM1/28/03

to

RB Smissaert offered this gem...

> Presume you mean the MetaData tab of the table in IBConsole. I am not sure
> if it is allowed to put this on the newsgroup, but I can e-mail it to you
> personally if that is ok.

What about your *_META_* data is there that has to be so secret?

I can readily understand that you don't wish to disclose patient ids and
names, but the meta data is just the structure of the tables with no
actual patient data.

Paul...

> RBS

Martijn Tonies

unread,

Jan 29, 2003, 10:56:55 AM1/29/03

to

Hi,

I've tried the tables - no problems with IB7...

--

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

> Here the complete DDL of the 2 tables:
>
> create table patient(
>
> PATIENT_ID integer not null,
>
> USUALLY_SEEN_GP integer,
>
> ADDED_BY integer,
>
> UPDATED_BY integer,
>
> FHSA_ID integer,
>
> DHA_ID integer,
>
> REGISTERED_GP integer,
>
> ROUTE_MARKER_TYPE smallint,
>
> MAIN_REG_TYPE smallint,
>
> GENDER_TYPE smallint,
>
> SURGERY_ID integer,
>
> ACCEPTANCE_TYPE integer,
>
> RESIDENTIAL_INSTITUTE integer,
>
> QUALIFIER_REG_TYPE smallint,
>
> CHI_NUMBER varchar(16),
>
> DATE_OF_BIRTH integer,
>