Link to home
Start Free TrialLog in
Avatar of Leogal
LeogalFlag for United States of America

asked on

TSQL looping I need help!

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
Avatar of ste5an
ste5an
Flag of Germany image

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.
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
Avatar of Leogal

ASKER

@scottPlecher, I am giving this a whirl.  I will get back to this comment string later today.
Avatar of Leogal

ASKER

@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.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial