Solved

TSQL looping I need help!

Posted on 2014-12-16
5
142 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
  • 2
  • 2
5 Comments
 
LVL 33

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

830 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