RE: SQL pat my own back -- MIDRANGE-L

Looks good to me Jeff.

Charles

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

OK, I decided on outer joins with coalesce. Haven't tried it
yet (keep getting interrupted) but how does this look:

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 Coalesce(i.QSUN2, 'UN')
Else Coalesce(i.QSUN1, 'CS')
End As QTYUOM,
0,
s.ITNSA/s.QTYSH,
s.ITNSA,
s.ITNBR,
Coalesce(i.ITDSC, 'Discontinued Item'),
Coalesce(i.PACKX, 1),
Coalesce(i.SIZEX, ' '),
Coalesce(i.BRAND, 'Unknown'),
Coalesce(v.VNDNM, 'Discontinued Vendor'),
Coalesce(i.VNITM, 'Unknown'),
Cast(Coalesce(i.ADUNQ, 0) As Char(1)) ConCat
Cast(Coalesce(i.VUPCD, 00000) As Char(5)) ConCat
Cast(Coalesce(i.IUPCD, 00000) As Char(5)),
0,
c.PRGCODE
From SAHISTW S
Join INSTLCTL c
On '1' = c.KEYID
Left Outer Join DMITMMST I
On s.ITNBR = i.ITNBR
Left Outer 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);

Note that it's still an inner join to INSTLCTL as that
control file just absolutely, positively has to be there.
And am I correct that if I want an outer join I cannot do the
join in the WHERE clause, correct?

--
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.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Wednesday, August 29, 2007 8:50 AM
To: Midrange Systems Technical Discussion
Subject: 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.

--
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 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.