RE: SQL pat my own back

A pat on the back from me also.

And yes, "if your using a cursor you're probably doing something wrong" is the motto I share with SQL
newbies. Glad to see it helped you.

A couple of points: you're doing inner joins which means that if there isn't a matching record in any
one of the files, then you don't get the data in the matching records that do exist in the others. If
that isn't what you want you'd want to use outer joins and probably the coalesce function to replace
missing data with defaults.

Second, when doing inner joins, there's an alternative syntax that's popular.

You simply list all the files in the from and place the join criteria in the WHERE clause. Like so:

Exec SQL Insert into INSTLTRN
(Select :RptDate,
c.DCNBR,
s.CUSNR,
s.INVNRP,
s.INVDT,
c.INVTRNCD,
s.QTYSH,
Case S.UNITS
When 'U' Then i.QSUN2
Else i.QSUN1
End As QTYUOM,
0,
s.ITNSA/s.QTYSH,
s.ITNSA,
s.ITNBR,
i.ITDSC,
i.PACKX,
i.SIZEX,
i.BRAND,
v.VNDNM,
i.VNITM,
Cast(i.ADUNQ As Char(1)) ConCat
Cast(i.VUPCD As Char(5)) ConCat
Cast(i.IUPCD As Char(5)),
0,
c.PRGCODE
From SAHISTW S , INSTLCTL c, DMITMMST I,
DMVDRMST V
Where s.VNDNR = c.vndnr and
s.INVDT >= :BgnSlsDate and
s.INVDT <= :EndSlsDate and
s.QTYSH > 0) and
'1' = c.KEYID and
s.ITNBR = i.ITNBR and
s.VNDNR = v.VNDNR

Lastly, I noticed that the join criteria for INSTLCTL doesn't include a reference to any other file.
You sure that's what you want? If there's only one record with c.KEYID = '1', then it's probably ok.
But if there's more than one, you'll see the data from a given SAHISTW, DMITMMST, DMVDRMST duplicated
with the different records of INSTLCTL. This is not wrong as long as that's what you really want.

Now if you figured all this out ahead of time, then give yourself another pat on back as IMHO you've
wrapped your head around a pretty difficult concept.

Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Tuesday, August 28, 2007 3:59 PM
To: 'Midrange Systems Technical Discussion'
Subject: SQL pat my own back

I probably ask more SQL how-to questions here than everyone
else combined, so I had to tell you I figured out a
relatively complex one (at least for
me) by myself.

I need to set up a transaction file that's going to be FTPed
to another company. I started writing a cursor driven SQLRPG
program because the data to be gathered needed to come from 4
different files, and the fields massaged somewhat. Then I
remembered someone (Charles Wilt?) saying that if you were
using a cursor in SQL, you were probably doing something
wrong. So I stopped and pondered for a while. Then I came
up with this (below) and it works!

Exec SQL Insert into INSTLTRN
(Select :RptDate,
c.DCNBR,
s.CUSNR,
s.INVNRP,
s.INVDT,
c.INVTRNCD,
s.QTYSH,
Case S.UNITS
When 'U' Then i.QSUN2
Else i.QSUN1
End As QTYUOM,
0,
s.ITNSA/s.QTYSH,
s.ITNSA,
s.ITNBR,
i.ITDSC,
i.PACKX,
i.SIZEX,
i.BRAND,
v.VNDNM,
i.VNITM,
Cast(i.ADUNQ As Char(1)) ConCat
Cast(i.VUPCD As Char(5)) ConCat
Cast(i.IUPCD As Char(5)),
0,
c.PRGCODE
From SAHISTW S
Join INSTLCTL c
On '1' = c.KEYID
Join DMITMMST I
On s.ITNBR = i.ITNBR
Join DMVDRMST V
On s.VNDNR = v.VNDNR
Where s.VNDNR = c.vndnr and
s.INVDT >= :BgnSlsDate and
s.INVDT <= :EndSlsDate and
s.QTYSH > 0);

Host variables and all. And it runs fast.

I get an extra beer tonight.

--
Jeff Crosby
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531

The opinions expressed are my own and not necessarily the
opinion of my company. Unless I say so.


--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.