Re: SQL equivalent of query/400 needed

[donna lester <donna_lester_ny@xxxxxxxxx>]

No, not at all.
  Iam using libraries when doing SQL. I dont think this is library list issue.
   
   As per Steve Richter post 
  "If the count is high with your join query check that there is more
than 1 b row for every matching a row.
with t1 as (
  select   a.sbkrnm, b.srcdta, count(*) joinCount
  from      file1 a
  join       file2 b
  on        a.sbkrnm = b.srcdta
  group by  a.sbkrnm, b.srcdta
  )
select     * from t1 where joinCount > 1 "
   
  I ran the above SQL , I am seeing all SBKRNM & SRCDTA  displayed. I dont know why?


vhamberg@xxxxxxxxxxx wrote:
  Is there any chance of a library list issue? Also, were there any warning messages in the job log when you ran RTVQMQRY? You might try getting the output to a temporary file - like select * from instead of just the count(*) - then run the IN selection on that and see what you get. Something is not doing the same kind of join as query/400 does - remember, of the 3 join types, only one is ever converted correctly.
-------------- Original message -------------- 
From: donna lester 

> Thank you all. 
> I have done RTVQMQRY. But some how, from query/400 to SQL statement count of 
> the records is differing? 
> what could be the reason? 
>
> SELECT COUNT(*) FROM FILE1 A, FILE2 B 
> where 
> A.SBKRNM = B.SRCDTA 
> AND( A.SQCDE IN('SA', 'SB', 'SD', 'SG', 'SH')) 
>
>
> Glenn Gundermann wrote: 
> How about: 
>
> SELECT COUNT(*) FROM FILE1 A JOIN FILE2 B ON A.SBKRNM = B.SRCDTA 
> WHERE A.SQCDE IN('SA', 'SB', 'SD', 'SG', 'SH') 
>
> Glenn Gundermann 
> ggundermann@xxxxxx 
> (647) 272-3295 
>
> > I have a query between two files FILE1, FILE2 
> >
> > type of join : 1 Matched records. 
> > how to join : T01.SBKRNM EQ T02.SRCDTA 
> > SELECT RECORDS: T01.SQCDE LIST 'SA' 'SB' 'SD' 'SG' 'SH' 
> >
> > the count of records is 465,962 
> >
> > I have written equivalent SQL(I dont think its correct bcoz count is 
> > differing) as I need SQL conversion of above query. 
> >
> > SELECT COUNT(*) FROM FILE1 A JOIN 
> > FILE2 B ON A.SBKRNM = B.SRCDTA 
> > AND A.SQCDE IN('SA', 'SB', 'SD', 'SG', 'SH') 
> >
> > Count comes 299,428 
> >
> > I tried right outer join as below but count count doesnt match. 
> > SELECT COUNT(*) FROM FILE1 A RIGHT OUTER JOIN 
> > FILE23 B ON A.SBKRNM = B.SRCDTA 
> > AND A.SQCDE IN('SA', 'SB', 'SD', 'SG', 'SH') 
> >
> > Can some one advise what could be the correct SQL equivalent of my 
> > query. Sorry for my ignorance of the understanding with unions if it is 
> > something wrong with my SQL. 
> >
> >
> > Thanks, 
> >
> > Donna. 
> -- 
> 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. 
>
>
>
>
> --------------------------------- 
> Boardwalk for $500? In 2007? Ha! 
> Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. 
> -- 
> 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.