?
Solved

Zero records appending to table.

Posted on 2011-10-04
7
Medium Priority
?
297 Views
Last Modified: 2012-06-27
I am trying to insert new records into table IV00101 with TEST apended to the end of the ITEMNMBR (Ex. ATIG1234TEST) , and only adding the ITEMNMBR’s  that match the ITEMNMBR in table SSG_temp_ATIitemsadd.  My script doesn’t error but it appends zero records. I believe it has to do with the WHERE Clause. If I Change the Column name in SSG_temp_ATIitemsadd to something that doen’t match the column name in IV00101 it appends TEST to the ITEMNMBRs in IV00101 but of course it appends all the ITEMNMBRs not just the ones that match.  
declare @Itmnmbr char(21)
set @Itmnmbr = 'TEST'

INSERT INTO [IV00101]
           (ITEMNMBR, ITEMDESC, NOTEINDX, ITMSHNAM, ITEMTYPE, ITMGEDSC, STNDCOST, CURRCOST, ITEMSHWT, DECPLQTY, DECPLCUR, ITMTSHID, 
                      TAXOPTNS, IVIVINDX, IVIVOFIX, IVCOGSIX, IVSLSIDX, IVSLDSIX, IVSLRNIX, IVINUSIX, IVINSVIX, IVDMGIDX, IVVARIDX, DPSHPIDX, PURPVIDX, UPPVIDX, 
                      IVRETIDX, ASMVRIDX, ITMCLSCD, ITMTRKOP, LOTTYPE, KPERHIST, KPTRXHST, KPCALHST, KPDSTHST, ALWBKORD, VCTNMTHD, UOMSCHDL, 
                      ALTITEM1, ALTITEM2, USCATVLS_1, USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, MSTRCDTY, MODIFDT, CREATDDT, 
                      WRNTYDYS, PRCLEVEL, LOCNCODE, PINFLIDX, PURMCIDX, IVINFIDX, INVMCIDX, CGSINFLX, CGSMCIDX, ITEMCODE, TCC, PriceGroup, PRICMTHD, 
                      PRCHSUOM, SELNGUOM, KTACCTSR, LASTGENSN, ABCCODE, Revalue_Inventory, Tolerance_Percentage, Purchase_Item_Tax_Schedu, 
                      Purchase_Tax_Options, ITMPLNNNGTYP, STTSTCLVLPRCNTG, CNTRYORGN, INACTIVE, MINSHELF1, MINSHELF2, INCLUDEINDP)
    
          (SELECT    rtrim(ITEMNMBR) + @Itmnmbr, ITEMDESC, NOTEINDX, ITMSHNAM, ITEMTYPE, ITMGEDSC, STNDCOST, CURRCOST, ITEMSHWT, DECPLQTY, DECPLCUR, ITMTSHID, 
                      TAXOPTNS, IVIVINDX, IVIVOFIX, IVCOGSIX, IVSLSIDX, IVSLDSIX, IVSLRNIX, IVINUSIX, IVINSVIX, IVDMGIDX, IVVARIDX, DPSHPIDX, PURPVIDX, UPPVIDX, 
                      IVRETIDX, ASMVRIDX, ITMCLSCD, ITMTRKOP, LOTTYPE, KPERHIST, KPTRXHST, KPCALHST, KPDSTHST, ALWBKORD, VCTNMTHD, UOMSCHDL, 
                      ALTITEM1, ALTITEM2, USCATVLS_1, USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, MSTRCDTY, MODIFDT, CREATDDT, 
                      WRNTYDYS, PRCLEVEL, LOCNCODE, PINFLIDX, PURMCIDX, IVINFIDX, INVMCIDX, CGSINFLX, CGSMCIDX, ITEMCODE, TCC, PriceGroup, PRICMTHD, 
                      PRCHSUOM, SELNGUOM, KTACCTSR, LASTGENSN, ABCCODE, Revalue_Inventory, Tolerance_Percentage, Purchase_Item_Tax_Schedu, 
                      Purchase_Tax_Options, ITMPLNNNGTYP, STTSTCLVLPRCNTG, CNTRYORGN, INACTIVE, MINSHELF1, MINSHELF2, INCLUDEINDP
FROM  [IV00101]
WHERE  (ITEMNMBR in (SELECT     ITEMNMBR from SSG_temp_ATIitemsadd)) AND  (rtrim(ITEMNMBR) + @Itmnmbr not in (SELECT ITEMNMBR FROM  [IV00101]))
)

Open in new window

0
Comment
Question by:skull52
  • 4
  • 3
7 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36913552
Hi.

You are doing RTRIM(ITEMNMBR) which indicates to me you are expecting trailing spaces in ITEMNMBR in IV00101. Could the same be true for ITEMNMBR in SSG_temp_ATIitemsadd? If not, then possibly you are comparing ITEMNMBR columns with different amounts of trailing spaces.

Kevin
0
 

Author Comment

by:skull52
ID: 36916099
So are you suggesting I add rtrim to ITEMMNMBR on the SSG_temp_ATIitemsadd table?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36917114
Possibly. I was actually thinking that table may have the value w/o trailing spaces, but because of the RTRIM() elsewhere in the query that the one at top-level needs to be trimmed. It is usually safer to RTRIM() both. For performance of any index on that field, the ultimate would be to change original data type to VARCHAR and trim spaces out of the data at rest. That way you don't have to remember which needs trimming.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:skull52
ID: 36917920
I can't really change the data type on the insert to table as it is an application generated table (Dynamics Great Plains) . I modified the WHERE Clause to include the RTRIM but still zero records inserted

WHERE  (ITEMNMBR in (SELECT rtrim(ITEMNMBR) from SSG_temp_ATIitemsadd)) AND  (rtrim(ITEMNMBR) + @Itmnmbr not in (SELECT ITEMNMBR FROM  [IV00101]))
)
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36918302
Okay, I am sorry. My iPad doesn't post as EE Mobile, but I was indeed not at my PC; therefore, I probably should have been more clear.

Your original query:
FROM  [IV00101]
WHERE  (ITEMNMBR in (SELECT     ITEMNMBR from SSG_temp_ATIitemsadd))
AND  (rtrim(ITEMNMBR) + @Itmnmbr not in (SELECT ITEMNMBR FROM  [IV00101]))

The RTRIM() there makes me suspect that ITMNMBR in [IV00101] may have trailing spaces whereas the one in SSG_temp_ATIitemsadd does NOT or they have different amounts of spaces, e.g., one is a CHAR(20) and the other CHAR(30) -- varying length. Therefore, what I am suggesting is to put a RTRIM() on the ITEMNMBR from [IV00101]. To be safe, BOTH is fine too.

So:
FROM  [IV00101]
WHERE  (RTRIM(ITEMNMBR) in (SELECT ITEMNMBR from SSG_temp_ATIitemsadd))
AND  (RTRIM(ITEMNMBR) + @Itmnmbr not in (SELECT ITEMNMBR FROM  [IV00101]))

Or:
FROM  [IV00101]
WHERE  (RTRIM(ITEMNMBR) in (SELECT RTRIM(ITEMNMBR) from SSG_temp_ATIitemsadd))
AND  (RTRIM(ITEMNMBR) + @Itmnmbr not in (SELECT ITEMNMBR FROM  [IV00101]))

Hope that makes sense.

Kevin
0
 

Author Comment

by:skull52
ID: 36918982
Well I found the problem, I was using a spread sheet to populate the SSG_temp_ATIitemsadd table which indeed had excessive spaces at the end of each record, which I thought RTRIM would have removed, once I deleted the excessive spaces  from the spread sheet and re-imported the records all worked. Thanks mwvisa1 you lead me in the right direction.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36919028
You are most welcome. Yes, there are some oddities there at times. I often suspect it is Alt+Enter or whatever the keystroke is for the carriage return/line feed that appears to be a space but is really a different character. Since RTRIM() only removes spaces, it does not work. Anyway, glad you found it.

Best regards and happy coding,

Kevin
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline

807 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