Why doesn't this sql work?
I have tried this on IDS Versions 11.70.FC5, 11.70.FC7 and 12.10.FC4.
select count(unique lea.ps_case_number) from traffic:le_activity lea
where lea.ps_case_number not in (
select jlea.ps_case_number fromjtraffic:le_activity jlea);
always returns 0.
I change the 'not in' to 'in' and it returned 102698; what I expected.
This is what's in the db:
select count(unique ps_case_number) from traffic:le_activity;returned 1639381
select count(unique ps_case_number) from jtraffic:le_activity;returned 103058
so I would expect the problem child to return something around 1.5Million.
And I'm the second person to look at this. I feel like a total idiot. Am I/we
missing something obvious?
What you are trying should work. However, here is an alternative query
that I think will be faster anyway:
select count(*)
from (
select unique lea.ps_case_number
from traffic:le_activity as lea
left outer join jtraffic:le_activity as jlea
on lea.ps_case_number = jlea.ps_case_number
where jlea.ps_case_number is null
);
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Wed, Jul 9, 2014 at 5:56 PM, BEVIS KENNEDY <bkennedy@utah.gov> wrote:
> I have tried this on IDS Versions 11.70.FC5, 11.70.FC7 and 12.10.FC4.
>
> select count(unique lea.ps_case_number) from traffic:le_activity lea
> where lea.ps_case_number not in (
> select jlea.ps_case_number from> jtraffic:le_activity jlea);
>
> always returns 0.
>
> I change the 'not in' to 'in' and it returned 102698; what I expected.
>
> This is what's in the db:
> select count(unique ps_case_number) from traffic:le_activity;> returned 1639381
> select count(unique ps_case_number) from jtraffic:le_activity;> returned 103058
>
> so I would expect the problem child to return something around 1.5Million.
>
> And I'm the second person to look at this. I feel like a total idiot. Am
> I/we
> missing something obvious?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e01493ffe4d9f7904fdc9dfa6
Art you rock! As always. Your query returned 1536999. Thank you.
Table definitions....?
On Jul 9, 2014 11:03 PM, "Art Kagel" <art.kagel@gmail.com> wrote:
> What you are trying should work. However, here is an alternative query
> that I think will be faster anyway:
>
> select count(*)
> from (>
> select unique lea.ps_case_number>
> from traffic:le_activity as lea
>
> left outer join jtraffic:le_activity as jlea
>
> on lea.ps_case_number = jlea.ps_case_number
>
> where jlea.ps_case_number is null
> );
>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on the IIUG, nor any other organization with which I am
> associated either explicitly, implicitly, or by inference. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity with which I am affiliated nor those of the entities themselves.
>
> On Wed, Jul 9, 2014 at 5:56 PM, BEVIS KENNEDY <bkennedy@utah.gov> wrote:
>
> > I have tried this on IDS Versions 11.70.FC5, 11.70.FC7 and 12.10.FC4.
> >
> > select count(unique lea.ps_case_number) from traffic:le_activity lea
> > where lea.ps_case_number not in (
> > select jlea.ps_case_number from> > jtraffic:le_activity jlea);
> >
> > always returns 0.
> >
> > I change the 'not in' to 'in' and it returned 102698; what I expected.
> >
> > This is what's in the db:
> > select count(unique ps_case_number) from traffic:le_activity;> > returned 1639381
> > select count(unique ps_case_number) from jtraffic:le_activity;> > returned 103058
> >
> > so I would expect the problem child to return something around
> 1.5Million.
> >
> > And I'm the second person to look at this. I feel like a total idiot. Am
> > I/we
> > missing something obvious?
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --089e01493ffe4d9f7904fdc9dfa6
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c211942a6f2304fdcb7a33
create table 'informix'.le_activity (
activity_idx SERIAL8 not null,
submitted_dttime DATETIME YEAR TO SECOND default CURRENT YEAR TO SECOND
not null,
submitting_oriid VARCHAR(10) not null,
occurred_dttime DATETIME YEAR TO SECOND not null,
reporting_oriid VARCHAR(10) not null,
case_number VARCHAR(20),
ps_case_number INT8,
is_reviewable boolean default 'f',
owner_agency CHAR(6),
activity_type CHAR(1),
submitter_recordid VARCHAR(64)
)
extent size 127532 next size 43724
lock mode row;
On Wed, Jul 9, 2014 at 5:58 PM, Fernando Nunes <domusonline@gmail.com>
wrote:
> Table definitions....?
> On Jul 9, 2014 11:03 PM, "Art Kagel" <art.kagel@gmail.com> wrote:
>
> > What you are trying should work. However, here is an alternative query
> > that I think will be faster anyway:
> >
> > select count(*)
> > from (> >
> > select unique lea.ps_case_number> >
> > from traffic:le_activity as lea
> >
> > left outer join jtraffic:le_activity as jlea
> >
> > on lea.ps_case_number = jlea.ps_case_number
> >
> > where jlea.ps_case_number is null
> > );
> >
> > Art
> >
> > Art S. Kagel, Principal Consultant
> > ASK Database Management
> >
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on the IIUG, nor any other organization with which I
> am
> > associated either explicitly, implicitly, or by inference. Neither do
> > those opinions reflect those of other individuals affiliated with any
> > entity with which I am affiliated nor those of the entities themselves.
> >
> > On Wed, Jul 9, 2014 at 5:56 PM, BEVIS KENNEDY <bkennedy@utah.gov> wrote:
> >
> > > I have tried this on IDS Versions 11.70.FC5, 11.70.FC7 and 12.10.FC4.
> > >
> > > select count(unique lea.ps_case_number) from traffic:le_activity lea
> > > where lea.ps_case_number not in (
> > > select jlea.ps_case_number from> > > jtraffic:le_activity jlea);
> > >
> > > always returns 0.
> > >
> > > I change the 'not in' to 'in' and it returned 102698; what I expected.
> > >
> > > This is what's in the db:
> > > select count(unique ps_case_number) from traffic:le_activity;> > > returned 1639381
> > > select count(unique ps_case_number) from jtraffic:le_activity;> > > returned 103058
> > >
> > > so I would expect the problem child to return something around
> > 1.5Million.
> > >
> > > And I'm the second person to look at this. I feel like a total idiot.
> Am
> > > I/we
> > > missing something obvious?
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --089e01493ffe4d9f7904fdc9dfa6
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a11c211942a6f2304fdcb7a33
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Bevis Kennedy
DBA (Public Safety)
Department of Technical Services
801-641-8192
--047d7bfe9ea23ed34604fdd709c6