We help IT Professionals succeed at work.

TSQL looping I need help!

184 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
Comment
Watch Question

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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

Author

Commented:
@scottPlecher, I am giving this a whirl.  I will get back to this comment string later today.

Author

Commented:
@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.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.