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?
 
tliottaConnect With a Mentor Commented:
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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
All Courses

From novice to tech pro — start learning today.