Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Infomaker query

4 posts in PB Infomaker Report Writer Last posting was on 1998-08-19 04:26:46.0Z
Lynne Strojny Posted on 1998-08-11 22:25:23.0Z
Newsgroups: sybase.public.infomaker.general
From: "Lynne Strojny" <lstrojny@mail.pcao.pima.gov>
Subject: Infomaker query
Date: Tue, 11 Aug 1998 15:25:23 -0700
Lines: 10
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <348_om3xQ2Wx9GA.245@forums.powersoft.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:6949
Article PK: 242980

I have two tables - a defendant table and a custody table. The custody
table is an outer join to the defendant because the defendant may or may not
have a custody record(s). I want to totally exclude any defendant from the
report who has an "O" status type on any custody record. Will infomaker be
able to handle this query itself or will it have to be done in a stored
procedure and returned to infomaker. If so, how do I set up the query?

TIA


Terry Dykstra (TeamPS) Posted on 1998-08-19 04:26:46.0Z
Newsgroups: sybase.public.infomaker.general
From: "Terry Dykstra (TeamPS)" <dont_spam_me_tdykstra@cfol.ab.ca>
Subject: Re: Infomaker query
Date: Tue, 18 Aug 1998 22:26:46 -0600
Lines: 27
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <348_0Z#YAAyy9GA.189@forums.powersoft.com>
References: <348_om3xQ2Wx9GA.245@forums.powersoft.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:6937
Article PK: 242967

You could use:

select * from defendant d
where defendent_id not in (
select defendent_id from custody c
where status = 'O'
and d.defendent_id = c.defendent_id )

P.S.: Have you enabled the outer-join option in the Query Governor?

Terry Dykstra (TeamPS)

Lynne Strojny wrote in message ...
>I have two tables - a defendant table and a custody table. The custody
>table is an outer join to the defendant because the defendant may or may
not
>have a custody record(s). I want to totally exclude any defendant from the
>report who has an "O" status type on any custody record. Will infomaker be
>able to handle this query itself or will it have to be done in a stored
>procedure and returned to infomaker. If so, how do I set up the query?
>
>TIA
>
>


"Ewa M." <knowacz Posted on 1998-08-13 05:58:45.0Z
Newsgroups: sybase.public.infomaker.general
Date: Thu, 13 Aug 1998 01:58:45 -0400
From: "Ewa M." <knowacz@usa.net_NoSpam>
Organization: somewhere in the bush
X-Mailer: Mozilla 3.01 (Win95; I)
MIME-Version: 1.0
To: Lynne Strojny <lstrojny@mail.pcao.pima.gov>
Subject: Re: Infomaker query
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 21
Message-ID: <348_35D28095.2AD8@usa.net_NoSpam>
References: <348_om3xQ2Wx9GA.245@forums.powersoft.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:6946
Article PK: 242976


Lynne Strojny wrote:
>
> I have two tables - a defendant table and a custody table. The custody
> table is an outer join to the defendant because the defendant may or may not
> have a custody record(s). I want to totally exclude any defendant from the
> report who has an "O" status type on any custody record. Will infomaker be
> able to handle this query itself or will it have to be done in a stored
> procedure and returned to infomaker. If so, how do I set up the query?
>

hi,
I would use the following function in the Where clause:
select ... where
decode(custody.status,"O",1,2) > 1

Of course you can use "T","F" instead of zero's and one's - this is just
a matter of identifying the matching value:
decode(custody.status,"0","T","F")<>"T"
HTH.
Greetings,
Eva


Rick Marden Posted on 1998-08-12 20:44:54.0Z
Newsgroups: sybase.public.infomaker.general
From: "Rick Marden" <mardens@cybertours.com>
Subject: Re: Infomaker query
Date: Wed, 12 Aug 1998 16:44:54 -0400
Lines: 27
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_ZZ4gkfix9GA.276@forums.powersoft.com>
References: <348_om3xQ2Wx9GA.245@forums.powersoft.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:6948
Article PK: 242978

I assume that you still want to see defendants that have no matching custody
records. As long as the status column in your custody table cannot have a
null value then you can create the outer join and do something like this in
the WHERE clause:

WHERE (custody.status <> 'O' OR custody.status IS NULL)

So you will get any custody record that does not have a status of "O" as
well as defendant records that do not have a matching defendant record.

Rick Marden
Primavera Systems

Lynne Strojny wrote in message ...
>I have two tables - a defendant table and a custody table. The custody
>table is an outer join to the defendant because the defendant may or may
not
>have a custody record(s). I want to totally exclude any defendant from the
>report who has an "O" status type on any custody record. Will infomaker be
>able to handle this query itself or will it have to be done in a stored
>procedure and returned to infomaker. If so, how do I set up the query?
>
>TIA
>
>