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:
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
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'
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
Microsoft SQL ServerSQL
Last Comment
Scott Pletcher
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].[S1022
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
My best guess is to replace current lines 152-158 with the lines below.
(SELECT STUFF(
(SELECT ';' + [FLSTCMST].[POCOM1]
FROM [INDY.ALLEGION.COM].[S1022
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
ASKER
@scottPlecher, I am giving this a whirl. I will get back to this comment string later today.
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:
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.DMFPURCHL
The statement has been terminated.
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
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.DMFPURCHL
The statement has been terminated.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Then: It looks like an export task which is better handled in SSIS.
Otherwise take a look at Bulk Import.