How to create a logical file with multiple select statements?

Hi,
I'm trying to create a logical file that selects records from the physical file based on two criteria - and the result is that I am getting matches for either scenario, but not both.  For example, I want all records with a D in one field and a 935 in the other.  When I compile the logical and view the results, I have all records that have  D in one field OR a 935 in the other.
Is there a way to add an "AND" to this select?
thanks
wendelinaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:

This should do it ...

create view aNewLogicl as (
select *
from   deleteme
where  aField = 'D'
  and  aField2 = 935
)
 
HTH,
DaveSlash

Open in new window

0
wendelinaAuthor Commented:
Hi,
Thank you for the reply - but - I am seeking a DDS solution.  We have not moved into the SQL world on our 400 yet...but thank you!
Wendy
0
tliottaCommented:
Wendy:

This should probably be in one of the AS/400-specific areas, but...

DDS allows 'and'/'or' to be indicated. In your case, you want to 'S'elect when the first and second conditions are true:

00070A          S FLD1                      COMP(EQ 'D')
00080A            FLD2                      COMP(EQ 935)

By leaving the 'S' column blank in the second condition, it indicates that it's connected to the first 'S'elect. That becomes the 'and' between the two.

00070A          S FLD1                      COMP(EQ 'D')
00080A          S FLD2                      COMP(EQ 935)

In this example, I have 'S' on both conditions. That indicates that they're separate 'S'elects and there'd be 'or' between the two conditions.

Tom
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

wendelinaAuthor Commented:
Solution was EXACTLY what we needed.  Haven't found this documented anywhere.  Results are just what is needed - thanks.
Wendy
0
tliottaCommented:
Wendy:

Documentation for V5R4:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/rzakb/selectomit.htm

Read a page or two down the screen. Figures 1 & 2 show examples.

This can also be found in the various .PDFs for DDS. Page numbers might change slightly. The DDS .PDF for V5R2 has a similar section on pages 18 & 19 with figures 13 & 14. (Those are printed page numbers. The relative page numbers are 26 & 27 appararently.) The topic name is 'Select or omit field name'.

Tom
0
wendelinaAuthor Commented:
Thank you for the info.  That looks like a helpful link - I will add that to our documentation folder.
Wendy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Mainframe Languages

From novice to tech pro — start learning today.