RE: Sql insert multiple records

Sorry Colin - it's not a spooled file, it's messages in your job log - QWMxxxx 
are the IDs.

Benefits are many, in my view. SQL SELECT statements are so much more flexible 
than Query/400 definitions. Rob mentioned the use of parentheses - wonderful!

But on top of that, you can run almost any SQL statement - you can't say INSERT 
INTO FILE VALUES(....) in Query/400 - yes, you can make it use a file for 
output. But try to do an UPDATE or a DELETE.

You CAN still use your Query/400 queries in the STRQMQRY command - but will not 
get what you expect if you use type 3 or type2 joins.

You have some wonderful reporting options - like word wrap within the width of 
a column - that is not found in Query/400 and is a pain in RPG. You have more 
heading lines possible and more column heading lines - up to 9 i think - of 
course, you probably don't need that many.

You also have easily set up substitution variables you can use - of course, if 
you have used merge variables in your Query/400 query, you can use that the 
same way.

Just a couple thoughts.
Vern

-------------- Original message -------------- 
From: colin reid <colinreid2000@xxxxxxxxxxx> 

I cannot get this darned splf from anzqry, I shall persevere! 
I know i am going off track now, not to mention getting pretty basic for this 
list, but i have always used query for reporting my data and am kind of 
getting 
the impression i am missing a trick with QMqrys. Is there some real benifit 
to 
be had using these for reporting/analysing over qry? 


Vernon Hamberg wrote: 
Hi Colin 

ANZQRY gives you a spooled file that lists the things that could not 
be brought over from a QRYDFN to a QMQRY. You might want to look in 
the Query Management Programming manual - there is a good chapter 
there on all the things that don't get retrieved exactly. 

I hear what Rob is saying - the syntax does not say JOIN, but they 
are like joins in the old style of using a WHERE clause of the 
join-fields. I think IBM could, if they wanted, generate the 
equivalent - it is not hard - in SQL. Pity!! 

I love QMQRYs and QMFORMs for quick report design. 

HTH 
Vern 

At 02:18 AM 2/19/2007, you wrote: 

Thanks for the responses folks. 
I have kjust looked at rtvqmqry and it looks useful, pity about 
the lack of 'join' info as you described. 
Not quite figured out anzqry yet, what it does or where it 
outputs to, i will have a play.... 
Many thanks, 
Colin. 




rob@xxxxxxxxx wrote: 
Some of the ways Query does stuff is a little crude. For example, Query 
doesn't use any JOINs - it links files together only by using WHEREs. 
Therefore when you use options in Query like the matching records with 
primary file, kind of stuff what you get may not be what query actually 
uses. Hence the command ANZQRY. It tells you when RTVQMQRY will lie. 

Also, your record selection can often be prettied up with the use of 
parenthesis - something Query sorely lacks. For example how would you do 
the following in Query? 
where (field1>100 and (field2>200 or field3<700)) 
And if you did figure out how to do it in Query, what would a RTVQMQRY of 
that query show you? 

Rob Berendt 
-- 
Group Dekko Services, LLC 
Dept 01.073 
PO Box 2000 
Dock 108 
6928N 400E 
Kendallville, IN 46755 
http://www.dekko.com 





"Jonathan Mason \(Tiscali\)" 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx 
02/16/2007 11:13 AM 
Please respond to 
RPG programming on the AS400 / iSeries 


To 
"'RPG programming on the AS400 / iSeries'" 
cc 

Fax to 

Subject 
RE: Sql insert multiple records 






Hi Colin 

On another matter, I don't suppose anyone knows if it is possible to 
convert AS400 query definitions into their equivalent SQL commands (a 

bit 

of a long shot I think.!) 

I haven't tried it on "complicated" queries, but you can use the RTVQMQRY 
command to get back the QMQRY definition of a Query/400 query. The QMQRY 
definition does contain the SQL commands for the query which you can cut 
and 
paste quite happily. For example: 

H QM4 05 Q 01 E V W E R 01 03 07/16/02 11:41 
V 1001 050 
V 5001 004 *HEX 
SELECT 
ALL XROBNM, XROBLB, XROBTP, XROBTX, XRRFOB, XRRFLB, XRRFTP 
FROM A10UTLLIB/AUKP225A T01 
WHERE XRRFOB NOT LIKE 'Q%' 
ORDER BY XROBNM ASC, XRRFTP ASC, XRRFOB ASC 

You will need to specify ALWQRYDFN(*YES) on the RTVQMQRY command to pull 
back the definition from the Query/400 object. 

Hope it helps 

All the best 

Jonathan 

--------------------------------- 
What kind of emailer are you? Find out today - get a free analysis of 
your 
email personality. Take the quiz at the Yahoo! Mail Championship. 
-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list 
To post a message email: RPG400-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l 
or email: RPG400-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/rpg400-l. 



-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list 
To post a message email: RPG400-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l 
or email: RPG400-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/rpg400-l. 


-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list 
To post a message email: RPG400-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l 
or email: RPG400-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/rpg400-l. 




--------------------------------- 
Inbox full of unwanted email? Get leading protection and 1GB 
storage with All New Yahoo! Mail. 
-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list 
To post a message email: RPG400-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l 
or email: RPG400-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/rpg400-l. 

-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list 
To post a message email: RPG400-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l 
or email: RPG400-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/rpg400-l. 




--------------------------------- 
Copy addresses and emails from any email account to Yahoo! Mail - quick, easy 
and free. Do it now... 
-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list 
To post a message email: RPG400-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l 
or email: RPG400-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/rpg400-l.