Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# If/Then Statement in Query400

Posted on 2004-08-04
Medium Priority
873 Views
I need to use If/Then statement in Query 400 in the following contest:
If one field equals 'C' then then another field should be equal the difference between two other fields.
If the same field equals 'R' then another filed should equal 0.
can you show me how to do it in Query400?
0
Question by:Vishnevetsky
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 13
• 10
• 3
• +2

LVL 1

Expert Comment

ID: 11724694
Hi

1. Define a result field as the difference between the two fields.

2. I presume we dealing with record selection and you want to extract the records where these conditions aren't met? (Otherwise reverse this logic):

If FieldA ='C' and FieldA<> NewField
or FieldA ='R' and FieldB<>0

Hope this helps.
0

Author Comment

ID: 11725050
I cannot find a place in Query400 to put the If/Then statement.
I tried in Define Result Fields and in Selecting Records but it does not work.
I do not want to to extarct any records.
I just wanted to put the condition depends on the data in one field.

If  fieldA='C' then fieldB=fieldD - fieldG
else
if FieldA='R' then fieldB = 0

Where and how can I write this statement in Query400?
0

LVL 1

Expert Comment

ID: 11725169
As you suspected what I described previously goes in the Result Field and Select Record panels. Perhaps we should take a step back.

Are you trying to update these records via Query400? If so you will be diappointed as it is a data extraction and reporting utility.

I would suggest you use SQL along the lines of the following (as you may be knew to this I suggest you use 2 separate statements)

UPDATE LIB/FILE SET FieldB=FieldD-FieldG WHERE FieldA='C'

UPDATE LIB/FILE SET FieldB=0 WHERE FieldA='R'
0

Author Comment

ID: 11725222
I do not need to update the records.
I would like to run the report and have data in FieldB depends on data in FieldA.
0

LVL 1

Expert Comment

ID: 11725353
OK think I'm with you now.

What you need are three queries:

Query 1 Extract to file where FieldA='C' and creating a result field called Fred which is defined as FieldD-FieldG.

Query 2 Extract to the same file where FieldA='R' and creating a result field called Fred set to 0

Query 3 This is your reportiung Query with Fred holding the data required.

But please consider what you will do with records where FieldA is not 'C' and is not 'R'.

0

Author Comment

ID: 11725421
How can I extract the file?
I created two queries for different conditions.
When I tried to create the result query I could not find the two previous queries in my file list.
0

LVL 1

Expert Comment

ID: 11725484
OK i'm guessing that you didn't run your first 2 queries to file which you specify on:

"Select output type and output form" where you take option 3=Database file.

On the following panel specify a target file and library. For the second query remember to take option 5=Add to member to avoid overwriting the results of the first query.

0

Author Comment

ID: 11725753
I can create the result query. Thank you.

I have FieldA in two queries:T01.FieldA and T02.FieldA.
T01.FieldA has data for one If/Then condition.
T02.FieldA has another If/Then condition.
How can I have one FieldA with different conditions in my result query?
How can I put results of different conditions in one field?
0

LVL 1

Expert Comment

ID: 11725836

If your final query create a new result field that is the sum of T01.FieldA + T02.FieldA

However it sounds like your first 2 queries created two separate files, both of which you have reference in your final query. What I was suggesting was that the results of the second query should be appended to the results of your first query. This will give you a single table as the source for your final query.

Hope this helps.
0

Author Comment

ID: 11726104
The sum does not work.
Please explain how can I append the result of second query to the results of first query.
0

LVL 1

Expert Comment

ID: 11726131
0

Author Comment

ID: 11726196
I did exactly what you recommend.
For the first query I selected Option3=Database.
For the second query I selected option 5=Add to member.
I got two queries with different data.
The data from Query2 did not append ro Query1.
What I am doing wrong?
0

LVL 1

Expert Comment

ID: 11726590
It's very hard to say without being there. Did you specify the same file name as the output target for both queries?
0

Author Comment

ID: 11726688
In Define Database File Output I put for file CBDEP2 , library name  and for Data in File -1 (New file) for first query.

For the second gquery I put for file CBDEP3, library name and for Data in File - 5 (Add to member).

I did not mention to which member, becuase I did not know where to put it.

Where can I specify the target for bothe queries?
0

LVL 1

Expert Comment

ID: 11726763
Make the output file name for both queries the SAME other wise the 5=Add to member has no effect. So make the file name Lib/CBDEP2 in for BOTH queries.
0

Author Comment

ID: 11726812
But I need to append CBDEP3 to CBDEP2.
If I will not mention CBDEP3 how it will know which file to append?
0

LVL 1

Expert Comment

ID: 11726910
If you make the output file name and library from the first two queries the same and specify append on the second - the results will be merged together ready for your final query.

It does not matter if the input file for each query is the same or different.

I'm sorry I'll be in meetings for the rest of my day but will try and look later tonight. If anybody can help if the meantime I'm sure it will be appreciated.

Good luck.
0

Author Comment

ID: 11726986
Thank you very much for all your help.
I will try to figure it out .
0

LVL 33

Expert Comment

ID: 11728349
Hey,
The AS/400 supports 2 types of queries: Query400 and QM queries.
QM queries are managed by the STRQM command. (option 1)
There are 2 modes of QM queries: prompt based, which are most similar to the regular qyeries you are familiar with, and SQL queries.
F19 toggles between Prompt and SQL.
In the SQL mode, you can write any SQL select statement to get results which can be printed, displayed, or saved to a file.

The following SQL statement will do the job for you:

select fld01, fld02, fld03,
case when fieldA='C' then fieldD - fieldG
when FieldA='R' then 0
else 0  /* is it possible to have other values?  */
end as fieldB
from yourfile
where .....

Cheers,
ShalomC
0

Author Comment

ID: 11728393
I understand about the SQL. I do not think we have QM queries.
How can I check out if we do have them?
How  can I switch to SQL mode?
0

Author Comment

ID: 11729233
Dear SuperCCA!
If you can tell me exactly what I need to put on Define Database File Output  for both queries I will be able to append the second query correctly.
I looked for some help, but canot find anywhere about the append queries.

Thank you.
0

LVL 33

Expert Comment

ID: 11732994
Hey,
As I mentioned before, run the STRQM command.
0

LVL 1

Expert Comment

ID: 11733625
G'Day

My time is limited today but I have just run a test as described below:

1. Create QUERY1 over a file called LIB1/TEST creating a work field called FRED which is set to 0. I specified database output to a file called QTEMP/HARRY all other values left as defaults.

2. Create QUERY2 over a file called LIB2/TEST creating a work field called FRED which is set to 1. I specified database output to the same file called QTEMP/HARRY with Member option set to 5, all other values left as defaults.

3. Create QUERY3 over the QTEMP/HARRY file which shows all records from both source files with the FRED field set to either 0 or 1 as described above.

If this doesn't work for you I can only presume I haven't understood the problem. (Do not be concerned about OS/400 release or PTF levels - this functionality has been in place since the year dot)
0

Author Comment

ID: 11736256
Dear ShalomC,
The case statement does not work, it has incorrect syntax.
My syntax is:

case when Field1='C' then field3-field4
when Field1='R' then 0
end as Field5

I am getting error: token field3 was not valid. Valid tokens:END

Can you suggest anything?
Thank you.
0

LVL 33

Expert Comment

ID: 11739706
What OS version do you have?
0

Author Comment

ID: 11739726
I have AS400 and Query 400
0

LVL 27

Expert Comment

ID: 11837390
Vishnevetsky:

It is possible that you do not have the STRQM command. This command is only available if you have the DB2 Query Mgr and SQL Devkit product installed. However, you _do_ have the CRTQMQRY command.

CRTQMQRY comes with all AS/400s. It allows you to enter an SQL statement into a source file member and then compile that SQL statement into a *QMQRY object.

Also, if you are running multiple Query/400 queries and you need to append with each query, be sure you either run the queries in the same job or you don't create the workfiles in QTEMP. If the workfiles are in QTEMP and the queries run individually in batch, the second query will not find the workfile from the first query; the workfile will be automatically deleted as soon as the first query ends.

Tom
0

Accepted Solution

modulo earned 0 total points
ID: 12808365
PAQed with no points refunded (of 500)

modulo
Community Support Moderator
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 hâ€¦
Without even knowing it, most of us are using web applications on a daily basis. Â In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us dailyâ€”and they are web applications. We generally confuse these web applications toâ€¦
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can reâ€¦
Weâ€™ve all felt that sense of false security beforeâ€”locking down external access to a database or component and feeling like weâ€™ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many wâ€¦
###### Suggested Courses
Course of the Month6 days, 23 hours left to enroll