Solved

If/Then Statement in Query400

Posted on 2004-08-04
29
609 Views
Last Modified: 2007-12-19
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
Comment
Question by:Vishnevetsky
  • 13
  • 10
  • 3
  • +2
29 Comments
 
LVL 1

Expert Comment

by:SuperCCA
Comment Utility
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

by:Vishnevetsky
Comment Utility
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

by:SuperCCA
Comment Utility
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

by:Vishnevetsky
Comment Utility
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

by:SuperCCA
Comment Utility
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

by:Vishnevetsky
Comment Utility
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

by:SuperCCA
Comment Utility
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

by:Vishnevetsky
Comment Utility
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

by:SuperCCA
Comment Utility
To answer your direct question:

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

by:Vishnevetsky
Comment Utility
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

by:SuperCCA
Comment Utility
Please read my answer earlier timed at 5.51 AM PST
0
 

Author Comment

by:Vishnevetsky
Comment Utility
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

by:SuperCCA
Comment Utility
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

by:Vishnevetsky
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Expert Comment

by:SuperCCA
Comment Utility
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

by:Vishnevetsky
Comment Utility
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

by:SuperCCA
Comment Utility
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

by:Vishnevetsky
Comment Utility
Thank you very much for all your help.
I will try to figure it out .
0
 
LVL 32

Expert Comment

by:shalomc
Comment Utility
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

by:Vishnevetsky
Comment Utility
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

by:Vishnevetsky
Comment Utility
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 32

Expert Comment

by:shalomc
Comment Utility
Hey,
As I mentioned before, run the STRQM command.
0
 
LVL 1

Expert Comment

by:SuperCCA
Comment Utility
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

by:Vishnevetsky
Comment Utility
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 32

Expert Comment

by:shalomc
Comment Utility
What OS version do you have?
0
 

Author Comment

by:Vishnevetsky
Comment Utility
I have AS400 and Query 400
0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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

by:
modulo earned 0 total points
Comment Utility
PAQed with no points refunded (of 500)

modulo
Community Support Moderator
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains in simple steps how to renew expiring Exchange Server Internal Transport Certificate.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now