0

So I have a table called 'WORK_ORDER' and the ID is a unique text example '16-0833' next would be 0834, 0835, etc.

In that table there are 4 user fields.. USER_1, USER_2, USER_3, USER_4..

This top work order needs x amount of other work orders to be completed.. so we will in those fields like this.

USER_1 = '16-0838' USER_2 = NULL USER_3 = '16-0834;16-0835;16-0835' USER_4 = NULL

I found a routing that will split them up and if I use this TSQL I get results.


DECLARE @UDF_1 nvarchar(100), @UDF_2 nvarchar(100), @UDF_3 nvarchar(100), @UDF_4 nvarchar(100), @ALL_UDF nvarchar(400);

SELECT        @UDF_1=USER_1, @UDF_2=USER_2, @UDF_3=USER_3, @UDF_4=USER_4
FROM            WORK_ORDER
WHERE        (BASE_ID = N'16-0833')

IF @UDF_1 IS NOT NULL
BEGIN
SET @ALL_UDF = @UDF_1
END

IF @UDF_2 IS NOT NULL
BEGIN
SET @ALL_UDF = @ALL_UDF + ';' + @UDF_2
END

IF @UDF_3 IS NOT NULL
BEGIN
SET @ALL_UDF = @ALL_UDF + ';' + @UDF_3
END

IF @UDF_4 IS NOT NULL
BEGIN
SET @ALL_UDF = @ALL_UDF + ';' + @UDF_4
END

BEGIN

SELECT
    *
FROM
    dbo.func_split(@ALL_UDF, ';')
END

With these results :

ElementID Element

1 16-0838

2 16-0834

3 16-0835

4 16-0836

I now need to take those 16-% numbers and requery the Work Order table to get their status, etc.

Just not quite sure how to accomplish this.

Any help is greatly appreciated. SQL 2012

0

Welcome to SQL, you can replace your sample code with this:

DECLARE @ParentBaseId nvarchar(100) = '16-0833'

SELECT * FROM WORK_ORDER
WHERE BASE_ID IN 
(
SELECT USER_1 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId 
UNION
SELECT USER_2 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId 
UNION
SELECT USER_3 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId 
UNION
SELECT USER_4 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId 
)

It's not the fanciest or even best way, but is the easiest to start learning with.

By the way, your code won't work if @UDF_1 is null, you need to assign '' to @ALL_UDF before working with it, or add other checks.

  • Thanks for your reply. The USER_1, etc. are already part of the main record in work order. But in USER_1-4 there could be several wo_id's delimited by a ; so I need to separate those so I can then query those WO's for status are part_id. – Mark Feb 2 '17 at 15:57
0

So this is what I ended up with that works. I am using a view to query the data. Now is it the absolute way to do it right? Not sure but I get the results I was looking for.

SELECT        A.[BASE_ID], Split.a.value('.', 'NVARCHAR(250)') AS INTMD_WO_ID
FROM            (SELECT        [BASE_ID], CAST('<M>' + REPLACE([USER_1], ';', '</M><M>') + '</M>' AS XML) AS INTMD_WO_ID
                          FROM            WORK_ORDER
                          UNION ALL
                          SELECT        [BASE_ID], CAST('<M>' + REPLACE([USER_2], ';', '</M><M>') + '</M>' AS XML) AS INTMD_WO_ID
                          FROM            WORK_ORDER
                          UNION ALL
                          SELECT        [BASE_ID], CAST('<M>' + REPLACE([USER_3], ';', '</M><M>') + '</M>' AS XML) AS INTMD_WO_ID
                          FROM            WORK_ORDER
                          UNION ALL
                          SELECT        [BASE_ID], CAST('<M>' + REPLACE([USER_4], ';', '</M><M>') + '</M>' AS XML) AS INTMD_WO_ID
                          FROM            WORK_ORDER) AS A CROSS APPLY INTMD_WO_ID.nodes('/M') AS Split(a)

Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.