× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. October 2006

RE: SQL - selecting issue

Charles,
Yes I must have had a typo of some sort.  I copied from your last email
and it is working and working and working.  I'm still waiting on
results.   This may work, but I'm not sure it is going to be the
ultimate solution.  My file has approximately 67,000 records.  
This was a recent copy as it still keeps on going- much like the
energizer bunny.   
Query running. 0 records selected, 28788150 processed. 

Michael Smith
iSeries.mySeries.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Wednesday, October 04, 2006 8:51 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL - selecting issue


Mike,

You must have a type or something as I know CTE's worked on v5r3.

Here's the copy paste from STRSQL

Type SQL statement, press Enter.                           
===> With dups as (select meter, count(distinct premise)   
                   from mike                               
                   group by meter                          
                   having count(distinct premise) > 1      
                  )                                        
     Select distinct premise, meter                        
     From mike                                             
     Where meter in (select meter from dups)               
                                                           

You want to paste yours so we can see it?


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Smith, Mike
Sent: Wednesday, October 04, 2006 8:45 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL - selecting issue

I'm on 5.3

Michael Smith
iSeries.mySeries.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Wednesday, October 04, 2006 8:43 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL - selecting issue


Mike,

What version of OS/400 are you running?  The "with dups as"
is a Common
Table Expression (CTE) and has been supported since v5r2 or so I
believe.

Try:
Select distinct premise, meter                                

From mike                                                     

Where meter in (select meter                                  
                from (select meter, count(distinct premise)   
                      from mike                               
                      group by meter                          
                      having count(distinct premise) > 1      
                     ) as dups                                
               )                                              


/* Testing on v5r4 */

select * from mike
PREMISE  SEQ   METER  
   X       1   9,999  
   X       2   9,999  
   Y       2   9,998  
   Y       1   9,998  
   Z       1   9,999  

*****************************************************
With dups as (select meter, count(distinct premise)   
              from mike                               
              group by meter                          
              having count(distinct premise) > 1      
             )                                        
Select distinct premise, meter                        

From mike                                             

Where meter in (select meter from dups)               
PREMISE  METER 
   X     9,999 
   Z     9,999 
****************************************************
Select distinct premise, meter                                

From mike                                                     

Where meter in (select meter                                  
                from (select meter, count(distinct premise)   
                      from mike                               
                      group by meter                          
                      having count(distinct premise) > 1      
                     ) as dups                                
               )                                              
PREMISE  METER
   X     9,999
   Z     9,999

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Smith, Mike
Sent: Wednesday, October 04, 2006 8:34 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL - selecting issue

2 things  
"With dups as".(I've never seen this before)   When I 

include this it

chokes running in STRSQL.  If I run
(select meter, count(distinct premise)
              from file
              group by meter
              having count(distinct premise) > 1 )

I get the list of meters attached to multiple premises.

I also need to include the premise. So that if meter 99999

is attached


to 3 premises, I want to know what those 3 premises were along with
the meter#

Michael Smith
iSeries.mySeries.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Wednesday, October 04, 2006 8:21 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL - selecting issue


Corrected (and tested ;-)

With dups as (select meter, count(distinct premise)
              from file
              group by meter
              having count(distinct premise) > 1
             )
Select distinct premise, meter

From file

Where meter in (select meter from dups)

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Smith, Mike
Sent: Wednesday, October 04, 2006 8:07 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL - selecting issue

Given the following file 
Premise   Seq #           Meter
X         01              99999
X         02              99999
Y         01              99998
Y         02              99998
Z         01              99999

I would like to create an sql statement that will identify

different

premises with the same meter# To get a result of
X                         99999
Z                         99999

Premise X has the same meter twice, but since it is only 1

premise it

would only be counted once.

Can this be done?

Michael Smith
iSeries.mySeries.

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at 
http://archive.midrange.com/midrange-l.




--
This is the Midrange Systems Technical Discussion

(MIDRANGE-L) mailing

list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, 
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at 
http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.




--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion 
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.