Solved

TSQL looping I need help!

Posted on 2014-12-16
5
145 Views
Last Modified: 2014-12-16
I am still learning TSQL and have hit a big snag in coding for me.  I have been tasked to bring in data form a file that contains multiple lines to be concatenated into one long field of 1000 characters.

The issue is that I have no clear idea how to perform a loop in TSQL.   The file contains a purchase order field that I am thinking would be the field to break on in the Loop.   Even after reading the other questions regarding looping I am still lost.  I have tried to understand how to use WHILE in this case and still need some clarity.  Please excuse my lack of understanding. I need help.

Attached is a file (sample.txt) containing sample data of the file I need to pull the data from and concatenate into one single field.

Here is the SQL script at this time:

USE DmStagingVD

delete from [dbo].[DMFPURCHLINEENTITY2]
 insert into [dbo].[DMFPURCHLINEENTITY2]

 
SELECT  

			 ' ' as [ACCOUNTINGDISTRIBUTIONTEMPLATE]
	 		,' ' as [ACTIVITYNUMBER] 

--- Asset Info, not populating at this time.
			,' ' as [ASSETBOOKID]
			,' ' as [ASSETGROUP]
			,' ' as [ASSETID]

-- Barcode Information, not populating at this time.
			,' ' as [BARCODE]	
			,' ' as [BARCODETYPE]


			,' ' as [BLOCKED]
			,' ' as [CASETAGGING]
			,' ' as [CFOPTABLE_BR_CFOPID]
			,' ' as [COMPLETE]

-- ConfigId we need to populate ? --------------------------------------------------------------------
			,' ' as [CONFIGID]
			
----Confirmed info, not populat this time
			,0  as [CONFIRMEDDLV] -- datetime
			,0  as [CONFIRMEDTAXAMOUNT]
			,' ' as [CONFIRMEDTAXWRITECODE]


			,' ' as [COUNTYORIGDEST]
			,' ' as [COVREF]
			,' ' as [CREATEFIXEDASSET]

-- Cuurency code, USD
			,'USD' as [CURRENCYCODE]

-- Customer Information, not populating
			,' ' as [CUSTOMERREF]
			,' ' as [CUSTPURCHASEORDERFORMNUM]

-- Financial Dimension
			,AXSTRING AS [DEFAULTDIMENSION] 
 
-- Definition Group not populating right now
			,' ' as [DEFINITIONGROUP]

--- Delivery Address, this information is populataed by the migration process
			,' ' as [DELIVERYADDRESS]
			,' ' as [DELIVERYCITY]
			,' ' as [DELIVERYCOUNTRYREGIONID]
			,' ' as [DELIVERYCOUNTY]

	        ,[PECSDT] as [DELIVERYDATE] -- datetime
			,' ' as [DELIVERYLOCATIONROLE]

--- Delivery Address, this information is populataed by the migration process
			,' ' as [DELIVERYNAME]
			,' ' as [DELIVERYSTATE]
			,' ' as [DELIVERTYSTREET]
			,' ' as [DELIVERYTYPE]
			,' ' as [DELIVERYZIPCODE]


			,0   as [DEPRECIATIONSTARTDATE] -- datetime
			,0   as [DISCAMOUNT]
			,0   as [DISCPERCENT]
			,' ' as [EDITABLEINWORKFLOW]
			,' ' as [EXECUTIONID]

			,[POCATG] as [EXTERNALITEMID]
			,' ' as [GSTHSTTAXTYPE_CA]

-- Intercompany information, not populating
			,' ' as [INTERCOMPANYINVENTTRANSID]
			,' ' as [INTERCOMPANYORIGIN]

			,0   as [INTRASTATFULFILLMENTDATE_HU] -- datetime

---Inventory information, we only populate Location and Site IDs
			,' ' as [INVENTBATCHID]
			,' ' as [INVENTCOLORID]
			,0 as [INVENTINVOICENOW]
			,'TD'as [INVENTLOCATIONID]
			,0   as [INVENTRECEIVEDNOW]
			,' ' as [INVENTREFID]
			,' ' as [INVENTREFTRANSID]
			,' ' as [INVENTSERIALID]
			,'IND' as [INVENTSITEID]
			,' '  as [INVENTSIZEID]
			,' ' as [INVENTSTYLEID]

			,' ' as [ISDELETED]
			,' ' as [ISFINALIZED]
			,' ' as [ISINVOICEMATCHED]
			,' ' as [ISMODIFIED]
			,' ' as [ISPWP]
			,0   as [ISSELECTED]
			,' ' as [ITEMBOMID]
			,[PEPN] as [ITEMID]
			,' ' as [ITEMPBAID]
			,' ' as [ITEMROUTEID]
			,' ' as [ITEMTAGGING]
			,' ' as [LEDGERDIMENSION]
-- Line amount --------------------------------------------------  purchase price * qty = line amount
		    ,POQTY * POCOST as [LINEAMOUNT]
			--,0   as [LINEAMOUNT]
			,' '  as [LINEDELIVERYTYPE]
			,0  as [LINEDISC]
			,' ' as [LINEHEADER]
			,cast([PELINE] as int) as [LINENUMBER]
			,0  as [LINEPERCENT]

-- MarkUp Information, not populating
			,0  as [MARKUPTRANS_CALCULATEDAMOUNT]
			,' ' as [MARKUPTRANS_CURRENCYCODE]
			,' ' as [MARKUPTRANS_DOCUMENTSTATUS]
			,' ' as [MARKUPTRANS_INTERCOMPANYCOMPANYID]
			,' ' as [MARKUPTRANS_INTERCOMPANYINVOICEID]
			,' ' as [MARKUPTRANS_INTERCOMPANYMARKUPUSEVALUE]
			,0  as [MARKUPTRANS_INTERCOMPANYMARKUPVALUE]
			,' ' as [MARKUPTRANS_ISDELETED]
			,' ' as [MARKUPTRANS_ISMODIFIED]
			,' ' as [MARKUPTRANS_KEEP]
			,0  as [MARKUPTRANS_LINENUM]
			,' ' as [MARKUPTRANS_MARKUPCATEGORY]
			,' ' as [MARKUPTRANS_MARKUPCODE]
			,' ' as [MARKUPTRANS_MODULECATEGORY]
			,' ' as [MARKUPTRANS_MODULETYPE]
			,0   as [MARKUPTRANS_ORIGTABLEID]
			,0  as [MARKUPTRANS_POSTED]
			,0  as [MARKUPTRANS_TAXAMOUNT]
			,' ' as [MARKUPTRANS_TAXAUTOGENERATED]
			,' ' as [MARKUPTRANS_TAXGROUP]
			,' ' as [MARKUPTRANS_TAXITEMGROUP]
			,' ' as [MARKUPTRANS_TAXWRITECODE]
 			,0   as [MARKUPTRANS_TRANSDATE] --- datetime
			,0   as [MARKUPTRANS_TRANSTABLEID]
			,' ' as [MARKUPTRANS_TXT]
			,0   as [MARKUPTRANS_VALUE]
			,' ' as [MARKUPTRANS_VOUCHER]
			,' ' as [MATCHINGPOLICY]
			,0   as [MULTILNDISC]
			,0   as [MULTILNPERCENT]

			
			 ,(select 
	           ISNULL([POCOM1],'' ) 
 			      from  [INDY.ALLEGION.COM].[S1022466].[KBM400MFG].[FLSTCMST]   
			   WHERE
			    exists (select pocom1
			                   from  [INDY.ALLEGION.COM].[S1022466].[KBM400MFG].[FLSTCMST] ) )
				     

			,' ' as [OPERATIONTYPE_MX]
			,0   as [OVERDELIVERYPCT]
			,' ' as [PALLETTAGGING]

--- PDS Info, not populating
			,' ' as  [PDSCALCULATIONID]
			,0   as [PDSCWINVENTRECEIVEDNOW]
			,0.00 as [PDSCWQTY]
			,0.00 as [PDSCWREMAININVENTFINANCIAL]
			,0.00 as [PDSCWREMAININVENTPHYSICAL]


			,' ' as [PORT]
			,0   as [PRICEUNIT]

-- PROJ Information not being populated
			,' ' as [PROJCATEGORYID]
			,' ' as [PROJID]
			,' ' as [PROJLINEPROPERTYID]
			,' ' as [PROJSALESCURRENCYID]
			,0.00 as [PROJSALESPRICE]
			,' ' as [PROJSALESUNITID]
			,' ' as [PROJTAXGROUPID]
			,' ' as [PROJTAXITEMGROUPID]
			,' ' as [PPROJTRANSID]

			,' '   as [PSARETAINSCHEDULEID]
			,0.00  as [PSATOTALRETAINAMOUNT]

			,' ' as [PURCHASETYPE]
			,[PENO] as [PURCHID] 
			,0.00 as [PURCHMARKUP]
			,cast([POCOST] as decimal (12,4)) as [PURCHPRICE]
			,[POQTY1] as [PURCHQTY] 

-- Misc Purchase Info, not populating 
			,0.00 as [PURCHRECEIVEDNOW]
			,' ' as [PURCHREQID]
			,' ' as [PURCHSTATUS]
			,' ' as [PURCHUNIT]


			,0.00 as [RBOPACKAGELINENUM]
			,0.00 as [REMAINDER]

-- Req information, not populating
			,' ' as [REQATTENTION]
			,' ' as [REQPLANIDSCHED]
			,' ' as [REQPOID]

-- Retail information, not populating
			,0.00 as [RETAILLINENUMEX1]
			,' ' as [RETAILPACKAGEID]
			,0.00 as [RETAILTEMPVALUEEX2]

-- Return information, not populating
			,' ' as [RETURNACTIONID]
			,' ' as [RETURNDISPOSITIONCODEID]
			,' ' as [RETURNSTATUS]


			,0  as [ROWID]
			,' ' as [SCRAP]
			,0 as [SEARCHKEY]

-- Service info, not populating
			,' ' as [SERVICEADDRESS]
			,' ' as [SERVICEDATE] -- datetime

-- Shippping info, not populating
			,' ' as [SHIPPINGDATECONFIRMED] --datetime  ?????????????????????????????????????
			,' ' as [SHIPPINGDATEREQUESTED] -- datetime  ?????????????????????????????????????

			,' ' as [SKIPUPDATE]

-- Stats info, not populating
			,0.00 as [STATISTICVALUE_LT]
			,' ' as [STATPROCID]
			,' ' as [STATTRIANGULARDEAL]

			,0 as [STOCKEDPRODUCT]
			,0 as [SYSTEMENTRYSOURCE]

--1099 infi, not populating
			,0.00 as [TAX1099AMOUNT]
			,' ' as [TAX1099BOX]
			,' ' as [TAX1099STATE]
			,0.00 as [TAX1099STATEAMOUNT]
			,' ' as [TAX1099TYPE]

-- Misc Tax info, not populating
			,' ' as [TAXAUTOGENERATED]
			,' ' as [TAXGROUP]
			,' ' as [TAXITEMGROUP]
			,' ' as [TAXSERVICECODE_BR]
			,0.00 as [TAXWITHHOLDBASECUR_TH]
			,' ' as [TAXWITHHOLDGROUP_TH]
			,' ' as [TAXWITHHOLDITEMGROUP_TH]

-- Transfer info, not populating
			,' ' as [TRANSACTIONCODE]
			,0   as [TRANSFERSTATUS]
			,' ' as [TRANSPORT]


			,0.00 as [UNDERDELIVERYPCT]
			,' ' as [VARIANTID]
			,[ACCOUNTNUM]  as [VENDACCOUNT]
			--,'8001-00592' as [VENDACCOUNT]

			,[VENDGROUP] 
			,' ' as [WMSLOCATIONID]
			,' ' as [WMSPALLETID]
			,' ' as [WORKFLOWSTATE]
			,0 as [RECVERSION]
			,0 as [PARTITION]
			,0 as[RecId]




FROM OPENQUERY  
      ([INDY.ALLEGION.COM],

	 ' SELECT   
	      
		    b.[POCATG]
		   ,c.[PENO]
		   ,b.[POCOST]
		   ,b.[POQTY]
		   ,b.[POQTY1]
		   ,c.[PECO]
		   ,b.[POSTS]
		   ,c.[PELINE]
		   ,c.[PEPN]
		   ,b.[POCO]
		   ,c.[PECSDT] 
		   ,e.[AXSTRING]
		   ,f.[ACCOUNTNUM]
		   ,f.[VENDGROUP]
		 

 FROM  [KBM400MFG].[FLPOSUM] b   
  inner join [KBM400MFG].[FLPODET] C on b.[POCO] = C.[PECO] and b.[PONO] = C.[PENO] and b.[POLINE] = C.[PELINE]
  inner join [VD_GPL].[AX_FINDIM] e on e.[ACCOUNT] = b.[POACT] AND e.[LOCATION] = b.[POLOC]
  inner join [VD_GPL].[AX_VENDOR] f on f.[ORGNUMBER] = b.[POVEND]
 


  '
   ) AS A 
		                                                          
			  inner join [DmStagingVD].[dbo].[DMFPRODUCTENTITY]  F on  f.[ITEMID]  = a.[PEPN]  
                    where A.PECO in (1,3,5,8) and A.[POSTS] = 'O' 
 

 
 
 

Open in new window


Attached is file layout with the fields and their attributes of the table I need to use in the Sub select, FLSTCMST.

I think what I need is to read the table [FLSTCMST] and loop through until the purchase order number (PONO)  changes.  The field I would be concatenating is POCOM1 , a comment line. Then move on down the Script.  I am at a total loss how to accomplish this.
sample.txt
flstcmst-layout.txt
0
Comment
Question by:Leogal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 34

Expert Comment

by:ste5an
ID: 40502645
First of all: Avoid loops! RDBMS are built to handle sets.

Then: It looks like an export task which is better handled in SSIS.

Otherwise take a look at Bulk Import.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40502987
Having to do a LOT of guessing here.
My best guess is to replace current lines 152-158 with the lines below.

    (SELECT STUFF(
        (SELECT ';' + [FLSTCMST].[POCOM1]
         FROM [INDY.ALLEGION.COM].[S1022466].[KBM400MFG].[FLSTCMST] [FLSTCMST]
         WHERE
             [FLSTCMST].[PURCHSE ORDER #] = PENO --Is this the P/O# from the outer/main query??
         ORDER BY [FLSTCMST].[POLN#]
         FOR XML PATH('')
        ), 1, 1, '') AS POCOMMENTS
    ) AS POCOM1_Combined
0
 

Author Comment

by:Leogal
ID: 40503151
@scottPlecher, I am giving this a whirl.  I will get back to this comment string later today.
0
 

Author Comment

by:Leogal
ID: 40503303
@scottPlecher, I have implemented the code and it works well! The only issue is that some of the input comments are equal to NULL. The file that I am writing to (inserting into)  will not accept nulls.
I went in to modify the code and still am getting the error.  

Here is code that you gave me with my attempt to handle NULLS:

,(SELECT STUFF
			     (
				    (SELECT ';' + ISNULL(LTRIM(RTRIM([FLSTCMST].[POCOM1])),' ')
					      FROM [INDY.ALLEGION.COM].[S1022466].[KBM400MFG].[FLSTCMST] [FLSTCMST]
					         WHERE [FLSTCMST].[PONO] = PENO 
						ORDER BY [FLSTCMST].[POLINE]
					 FOR XML PATH('')         ), 1, 1, '') AS NAME
				) AS POCOM1_Combined

Open in new window


Here is the error received  when running the code and finding a incoming null in the field POCOM1:


(0 row(s) affected)
Msg 515, Level 16, State 2, Line 5
Cannot insert the value NULL into column 'NAME', table 'DmStagingVD.dbo.DMFPURCHLINEENTITY2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40503346
,ISNULL((SELECT STUFF
                       (
                            (SELECT ';' + LTRIM(RTRIM(ISNULL([FLSTCMST].[POCOM1], '')))
                                    FROM [INDY.ALLEGION.COM].[S1022466].[KBM400MFG].[FLSTCMST] [FLSTCMST]
                                       WHERE [FLSTCMST].[PONO] = PENO
                                    ORDER BY [FLSTCMST].[POLINE]
                               FOR XML PATH('')         ), 1, 1, '') AS NAME
                        ), '') AS POCOM1_Combined
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

739 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