December 13, 2005 at 10:38 am
I'm new to SQL, this is only my second time. I'm trying to join two tables, I'm picking only some fields from the two tables and adding them to a third table. I can get it to work with no problem. The catch comes in because I need to update a field that is only in the result table. It is updated based on a value in one of the from tables. Here is an example of the statement I used to get the data into the result table with out the credit field it is the one that is only in the result table.
INSERT INTO INVTEST ( SLCTK1, SLCTK2, SLBTYP, SLBJOB, SLBCUS,
SLBFLG, SLBDNM, SLBDES, CREDIT ) SELECT SLPBXLT.SLCTK1,
SLPBXLT.SLCTK2, SLPBXLT.SLBTYP, SLPBXLT.SLBJOB, SLPBXLT.SLBCUS,
SLPBXLT.SLCSLB, SLPBXLT.SLCSLN, SLPBXLT.SLBPO#, SLPBXLT.SLBPOL,
SLPBXLT.SLBCST, SLPBXLT.SLBCO#, SLPBXLT.SLBJBC, SLPBXLT.SLBQ#,
SLPBXLT.SLCMSH, SLPBXLT.SLCDSH, SLPBXLT.SLCYSH, SLPBXLT.SLBOTK,
SLPBXLT.SLBFLG, SLPBXLT.SLBLTX, SLPBXLT.SLBSTX, SLPBXLT.SLBHC$,
SLPBXLT.SLBDS$, SLPBXLT.SLBPHA, SLPBXLT.SLCBLY, SLPBXLT.SLCBLM,
SLPBXLT.SLCBLD, SLPBXLT.SLBDNM, SLPBXLD.SLBSTK, SLPBXLD.SLBDES,
SLPBXLD.SLBQTO, SLPBXLD.SLBQTS, SLPBXLD.SLBXSL, SLPBXLD.SLBUSL FROM
SLLIB/SLPBXLT, SLLIB/SLPBXLD WHERE slpbxlt.slctk1 = slpbxld.slctk1
and slpbxlt.slctk2 = slpbxld.slctk2 and slpbxlt.slbcus = '81800'
and slpbxlt.slbdnm = ' ' and slpbxlt.slbflg = '1'
INVTEST is the result table. This is on an ISeries if it make any difference.
The CERDIT field would get updated with a 'CR' if SLPBXLT.SLBTYP is = to a '7' '8' or '9'
Thanks for any help. I think I can do this, but i'm not sure. Bill
December 13, 2005 at 1:25 pm
case SLPBXLT.SLBTYP when 7 then 'CR'
when 8 then 'CR'
when 9 then 'CR'
else 'not credit' end Credit
December 13, 2005 at 1:36 pm
Thanks Tom. This may seam dumb but where would I insert the CASE statement, I did try that before I posted this question but I keep getting an error. I tried it after the WHERE and Before the WHERE but I get an error "Token SLPBXLT was not valid. Valid tokens: , FROM INTO.". So maybe I just put in the wrong place. Bill
December 13, 2005 at 4:30 pm
INSERT INTO INVTEST ( SLCTK1, SLCTK2, SLBTYP, SLBJOB, SLBCUS,
SLBFLG, SLBDNM, SLBDES, CREDIT ) SELECT SLPBXLT.SLCTK1,
SLPBXLT.SLCTK2, case SLPBXLT.SLBTYP when 7 then 'CR'
when 8 then 'CR'
when 9 then 'CR'
else 'not credit' end Credit, SLPBXLT.SLBJOB, SLPBXLT.SLBCUS,
SLPBXLT.SLCSLB, SLPBXLT.SLCSLN, SLPBXLT.SLBPO#, SLPBXLT.SLBPOL,
SLPBXLT.SLBCST, SLPBXLT.SLBCO#, SLPBXLT.SLBJBC, SLPBXLT.SLBQ#,
SLPBXLT.SLCMSH, SLPBXLT.SLCDSH, SLPBXLT.SLCYSH, SLPBXLT.SLBOTK,
SLPBXLT.SLBFLG, SLPBXLT.SLBLTX, SLPBXLT.SLBSTX, SLPBXLT.SLBHC$,
SLPBXLT.SLBDS$, SLPBXLT.SLBPHA, SLPBXLT.SLCBLY, SLPBXLT.SLCBLM,
SLPBXLT.SLCBLD, SLPBXLT.SLBDNM, SLPBXLD.SLBSTK, SLPBXLD.SLBDES,
SLPBXLD.SLBQTO, SLPBXLD.SLBQTS, SLPBXLD.SLBXSL, SLPBXLD.SLBUSL FROM
SLLIB/SLPBXLT, SLLIB/SLPBXLD WHERE slpbxlt.slctk1 = slpbxld.slctk1
and slpbxlt.slctk2 = slpbxld.slctk2 and slpbxlt.slbcus = '81800'
and slpbxlt.slbdnm = ' ' and slpbxlt.slbflg = '1'
December 14, 2005 at 11:51 am
Tom, I gave that a try and I got further but it was writing the 'CR' into the SLBTYP field instead of the CREDIT field. Would you
have another idea? Thanks for you help. Bill
December 14, 2005 at 12:15 pm
Your original question listed 9 columns in the INSERT list, but many more in the SELECT list. The number of columns in both must match, so we should start by nailing that issue, then move on to the expression, because I'm not sure how your statement even runs without an error due mismatched column counts.
The ordinal position of columns in the SELECT must match the ordinal postion of the intended destination column in the INSERT. If you have the CASE ... WHEN at ordinal position 3, it ain't going to find its way into column CREDIT which is at ordinal position 9
December 14, 2005 at 12:28 pm
Yes I did shorted things down so it wasn't so long I'll post the statement I used and the file layout.
INSERT INTO INVTEST
(SLCTK1,SLCTK2,SLBTYP,SLBJOB,SLBCUS,SLCSLB,SLCSLN,SLBPO#,SLBPOL,
SLBCST,SLBCO#,SLBJBC,SLBQ#,SLCMSH,SLCDSH,SLCYSH,SLBOTK,SLBFLG,
SLBLTX,SLBSTX,SLBHC$,SLBDS$,SLBPHA,SLCBLY,SLCBLM,SLCBLD,SLBDNM,
SLBSTK,SLBDES,SLBQTO,SLBQTS,SLBXSL,SLBUSL,CREDIT)
SELECT SLPBXLT.SLCTK1,
SLPBXLT.SLCTK2, case SLPBXLT.SLBTYP when 7 then 'CR'
when 8 then 'CR'
when 9 then 'CR'
else 'NOT CREDIT' end CREDIT, SLPBXLT.SLBJOB, SLPBXLT.SLBCUS,
SLPBXLT.SLCSLB, SLPBXLT.SLCSLN, SLPBXLT.SLBPO#, SLPBXLT.SLBPOL,
SLPBXLT.SLBCST, SLPBXLT.SLBCO#, SLPBXLT.SLBJBC, SLPBXLT.SLBQ#,
SLPBXLT.SLCMSH, SLPBXLT.SLCDSH, SLPBXLT.SLCYSH, SLPBXLT.SLBOTK,
SLPBXLT.SLBFLG, SLPBXLT.SLBLTX, SLPBXLT.SLBSTX, SLPBXLT.SLBHC$,
SLPBXLT.SLBDS$, SLPBXLT.SLBPHA, SLPBXLT.SLCBLY, SLPBXLT.SLCBLM,
SLPBXLT.SLCBLD, SLPBXLT.SLBDNM, SLPBXLD.SLBSTK, SLPBXLD.SLBDES,
SLPBXLD.SLBQTO, SLPBXLD.SLBQTS, SLPBXLD.SLBXSL, SLPBXLD.SLBUSL FROM
SLLIB/SLPBXLT, SLLIB/SLPBXLD WHERE slpbxlt.slctk1 = slpbxld.slctk1
and slpbxlt.slctk2 = slpbxld.slctk2 and slpbxlt.slbcus = '81800'
and slpbxlt.slbdnm = ' ' and slpbxlt.slbflg = '1'
Here is the file
SLCTK1 2A COLHDG('BR')
SLCTK2 5A COLHDG('NBR')
SLBTYP 1A COLHDG('TY')
SLBJOB 3A COLHDG('JOB')
SLBCUS 5A COLHDG('CUST#')
SLCSLB 2A COLHDG('BR')
SLCSLN 2A COLHDG('SN#')
SLBPO# 5A COLHDG('P.O.')
SLBPOL 2A COLHDG('BR')
SLBCST 20A COLHDG('SHIP TO')
SLBCO# 16A COLHDG('CUST ORD#')
SLBJBC 11A COLHDG('JOB/CALL')
SLBQ# 5A COLHDG('QUOTE#')
SLCMSH 2S 0 COLHDG('MON')
SLCDSH 2S 0 COLHDG('DAY')
SLCYSH 2S 0 COLHDG('YR')
SLBOTK 8A COLHDG('ORG TKT#')
SLBFLG 1A COLHDG('*=FLAGGED,' -
'1=FINALLED')
SLBLTX 7P 2 COLHDG('LOC TAX')
SLBSTX 7P 2 COLHDG('ST TAX')
SLBHC$ 9P 2 COLHDG('HDLG CHG')
SLBDS$ 9P 2 COLHDG('DISCNT')
SLBPHA 1A COLHDG('PH')
SLCBLY 2S 0 COLHDG('YR')
SLCBLM 2S 0 COLHDG('MON')
SLCBLD 2S 0 COLHDG('DAY')
SLBDNM 1A COLHDG('X=DO NOT MAIL')
SLBSTK 6A COLHDG('STOCK#')
SLBDES 35A COLHDG('DESCRIPTION')
SLBQTO 9P 2 COLHDG('QTY ORD')
SLBQTS 9P 2 COLHDG('QTY SHIP')
SLBXSL 9P 2 COLHDG('EXT SELL')
SLBUSL 9P 4 COLHDG('UNIT PRICE')
CREDIT 2A COLHDG('Credit Invoice ind'
I don't know if this will help or not. The last time I ran it i did change the field CREDIT with a 2 char field from the file SLPBXLT.SLBLOC becouse it was giving an error about the field cerdit not being in the file. Thanks for you help Bill
December 14, 2005 at 12:40 pm
CREDIT is the last column in your INSERT list
The expression that you have added to add the logic of 7,8,9 = "CR" is the third column in your SELECT list.
Ordinal positions must match between the INSERT and SELECT lists. Third column in the SELECT goes into third column of INSERT, which is currently SLBTYP.
December 14, 2005 at 12:44 pm
OK, Can I update the field CREDIT with a 'CR' based on the field SLBTYP? and if so how can I do it. Thanks Bill
December 14, 2005 at 12:59 pm
Yes. You use the expression in the SELECT list. The expression is the CASE ... WHEN ... END construct:
case SLPBXLT.SLBTYP when 7 then 'CR'
when 8 then 'CR'
when 9 then 'CR'
else 'not credit' end Credit
>>and if so how can I do it.
As mentioned, you do it by placing the expression at the same ordinal position in the SELECT list as the column CREDIT in the INSERT list. You know what I'm referring to by ordinal position, right ?
December 14, 2005 at 1:40 pm
Thanks PW. Yes I know about the position now, what i can't get right is using the case statement, how to update the last column based on the third column. Now I know how to update the column where the case statement is based on its own value but not how to do it from another column. I'm not sure if this is clear or not. I can do this easily in a program, but i'm hopping to learn SQL to not have to write a program where a few lines in SQL will do the job. Thanks for you help Bill
December 14, 2005 at 9:41 pm
You can have the original column and use that column in a case statement else where in the query. I moved credit to the end and left the original slbtyp at the strart. You could create every column with a different case for the same column.
ie: select case author when 'bill' then 'ed' end firstname, case author when 'bill' then 'sam' end lastname from whatever
INSERT INTO INVTEST ( SLCTK1, SLCTK2, SLBTYP, SLBJOB, SLBCUS,
SLBFLG, SLBDNM, SLBDES, CREDIT ) SELECT SLPBXLT.SLCTK1,
SLPBXLT.SLCTK2, SLPBXLT.SLBTYP , SLPBXLT.SLBJOB, SLPBXLT.SLBCUS,
SLPBXLT.SLCSLB, SLPBXLT.SLCSLN, SLPBXLT.SLBPO#, SLPBXLT.SLBPOL,
SLPBXLT.SLBCST, SLPBXLT.SLBCO#, SLPBXLT.SLBJBC, SLPBXLT.SLBQ#,
SLPBXLT.SLCMSH, SLPBXLT.SLCDSH, SLPBXLT.SLCYSH, SLPBXLT.SLBOTK,
SLPBXLT.SLBFLG, SLPBXLT.SLBLTX, SLPBXLT.SLBSTX, SLPBXLT.SLBHC$,
SLPBXLT.SLBDS$, SLPBXLT.SLBPHA, SLPBXLT.SLCBLY, SLPBXLT.SLCBLM,
SLPBXLT.SLCBLD, SLPBXLT.SLBDNM, SLPBXLD.SLBSTK, SLPBXLD.SLBDES,
SLPBXLD.SLBQTO, SLPBXLD.SLBQTS, SLPBXLD.SLBXSL, SLPBXLD.SLBUSL,
case SLPBXLT.SLBTYP when 7 then 'CR'
when 8 then 'CR'
when 9 then 'CR'
else 'not credit' end Credit
FROM
SLLIB/SLPBXLT, SLLIB/SLPBXLD WHERE slpbxlt.slctk1 = slpbxld.slctk1
and slpbxlt.slctk2 = slpbxld.slctk2 and slpbxlt.slbcus = '81800'
and slpbxlt.slbdnm = ' ' and slpbxlt.slbflg = '1'
Hope this helps.
Tom
December 15, 2005 at 9:23 am
Thanks Tom and PW. I did get it to work. I guess after I put the statement in the right place it was just a matter of syntax. Below is the one that worked. Again Thanks Bill
INSERT INTO INVTEST
(SLCTK1,SLCTK2,SLBTYP,SLBJOB,SLBCUS,SLCSLB,SLCSLN,SLBPO#,SLBPOL,
SLBCST,SLBCO#,SLBJBC,SLBQ#,SLCMSH,SLCDSH,SLCYSH,SLBOTK,SLBFLG,
SLBLTX,SLBSTX,SLBHC$,SLBDS$,SLBPHA,SLCBLY,SLCBLM,SLCBLD,SLBDNM,
SLBSTK,SLBDES,SLBQTO,SLBQTS,SLBXSL,SLBUSL,SLBLOC)
SELECT
SLPBXLT.SLCTK1, SLPBXLT.SLCTK2, SLPBXLT.SLBTYP, SLPBXLT.SLBJOB,
SLPBXLT.SLBCUS, SLPBXLT.SLCSLB, SLPBXLT.SLCSLN, SLPBXLT.SLBPO#,
SLPBXLT.SLBPOL, SLPBXLT.SLBCST, SLPBXLT.SLBCO#, SLPBXLT.SLBJBC,
SLPBXLT.SLBQ#, SLPBXLT.SLCMSH, SLPBXLT.SLCDSH, SLPBXLT.SLCYSH,
SLPBXLT.SLBOTK, SLPBXLT.SLBFLG, SLPBXLT.SLBLTX, SLPBXLT.SLBSTX,
SLPBXLT.SLBHC$, SLPBXLT.SLBDS$, SLPBXLT.SLBPHA, SLPBXLT.SLCBLY,
SLPBXLT.SLCBLM, SLPBXLT.SLCBLD, SLPBXLT.SLBDNM, SLPBXLD.SLBSTK,
SLPBXLD.SLBDES, SLPBXLD.SLBQTO, SLPBXLD.SLBQTS, SLPBXLD.SLBXSL,
SLPBXLD.SLBUSL, case when SLPBXLT.SLBTYP = '7' then 'CR'
when SLPBXLT.SLBTYP = '8' then 'CR'
when SLPBXLT.SLBTYP = '9' then 'CR'
else ' '
end SLBLOC
from SLLIB/SLPBXLT, SLLIB/SLPBXLD
WHERE slpbxlt.slctk1 = slpbxld.slctk1
and slpbxlt.slctk2 = slpbxld.slctk2 and slpbxlt.slbcus = '81800'
and slpbxlt.slbdnm = ' ' and slpbxlt.slbflg = '1'
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply