We help IT Professionals succeed at work.

iSeries SQL subselect

Hi, I'm trying to retrieve order number, status, customer#, customer name from our order master and customer master when the customer is on our preferred customer file.  This runs but returns nothing when it should.  Where am I going wrong?   Note: CXSPRF is a preferred level then multiple customer are assigned to it.  So I want all custest1 level where the customer number from the order master is assigned to it and active.

Select a.CHONUM,a.CHOSTS,a.CHOCUS,b.CCNAME                              
from CORDER a, CUSTMST b                                        
where a.CHOCUS = b.CCNUM                                        
  and (a.CHODTE between 20190101 and 20190931)                  
  and EXISTS                                                    
    (SELECT * FROM CCXCPRF c WHERE ((c.CXSPRF = 'CUSTEST1' AND  
                                                                        a.CHOCUS = c.CXSCUS)  AND  
                                                                        c.CXSSTS = 'A'))          


Thank you in advance!!
Lynn
Comment
Watch Question

Database Administrator / Software Engineer
CERTIFIED EXPERT
Commented:
Hi,

Does the columns a.CHOCUS,  b.CCNUM   and  c.CXSCUS in each table hold the same data ?
Do one simple select DISTINCT <column> from <table a,b,c> , one for each table to see if you have any matching data.
Regards,
    Tomas Helgi
Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT

Commented:
Test each component:

Select a.CHONUM,a.CHOSTS,a.CHOCUS,b.CCNAME                              
from CORDER a, CUSTMST b                                        
where a.CHOCUS = b.CCNUM
and a.CHOCUS = 'CUSTEST1'

then

Select a.CHONUM,a.CHOSTS,a.CHOCUS,b.CCNAME                              
from CORDER a, CUSTMST b                                        
where a.CHOCUS = b.CCNUM
and (a.CHODTE between 20190101 and 20190931)                  
and a.CHOCUS = 'CUSTEST1'

then

SELECT * FROM CCXCPRF c WHERE (c.CXSPRF = 'CUSTEST1' AND c.CXSSTS = 'A')

Did you get a row for each?

Author

Commented:
Hi Gary,

Thank you.  I did get a row for each in once session.  Which promoted me to check my library list.  It was correct in one session and not the other ... (eye roll)!  Sorry to have wasted you time.  When set correctly it runs.  

Thank YOU!
Lynn
Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT

Commented:
Lol.  If I had a dollar for every time the library list got me, I'd ... well, I'd have a lot of dollars.

Author

Commented:
=)