Sql view splitting field

[LadyLin]

I have a view

CREATE view
[dbo].[AGV_WO_OPR_AND_UDFS]
as

SELECT [PROGRAM_ID] as id
      ,[DOCUMENT_ID] as doc
   ,case when [id] = 'UDF-0000086' then [string_val] else null end as Machine
   ,case when [id] = 'UDF-0000087' then [string_val] else null end as Prog_Ready
   ,case when [id] = 'UDF-0000088' then [string_val] else null end as Kit_Ready
  FROM [Serials].[dbo].[USER_DEF_FIELDS]
where [program_ID] = 'VMMFGWIN_OP' AND [DOCUMENT_ID] IS NOT NULL 
GO

The document_id column has this type of data W~WO1~lot1~split1~sub2~opr
I would like to make 6 columns, naming them type,base,lot,split,sub and opr.
Can this be done when creating a view?

 

[jbenson001]

CAn you give an example of what you would like the output to be?

 

[gmmastros]

Take a look here: thread183-1451986

Especially the post where I present a split function and a ParsePart function.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LadyLin]

The data comes in like
----Doc_id----------------
W~WO1~A~0~0~100
W~WO2~b~1~0~110
I want it to be

Type WO Lot split sub opr
W WO1 A 0 0 100
W W02 b 1 0 110

I will check out the post too.

 

[LadyLin]

I figured out a solution:

SELECT     t2.id, t2.docx, t2.Machine, t2.Prog_Ready,
                 t2.Kit_Ready, t3.doc3, t3.WORKORDER_TYPE,
                 t3.WORKORDER_BASE_ID, t3.WORKORDER_LOT_ID, 
                 t3.WORKORDER_SPLIT_ID, t3.WORKORDER_SUB_ID,
                 t3.SEQUENCE_NO
FROM      
   (SELECT    id, doc AS docx, MAX(Machine) AS Machine,
                    MAX(Prog_Ready) AS Prog_Ready,
                    MAX(Kit_Ready) AS Kit_Ready
             FROM  
                  (SELECT    
                        PROGRAM_ID AS id, DOCUMENT_ID AS doc,
                        CASE WHEN [id] = 'UDF-0000086' THEN [string_val] 
                                    ELSE NULL END AS Machine, 
                        CASE WHEN [id] = 'UDF-0000087' THEN [string_val]
                                    ELSE NULL END AS Prog_Ready, 
                        CASE WHEN [id] = 'UDF-0000088' THEN [string_val]
                                    ELSE NULL END AS Kit_Ready
                    FROM     dbo.USER_DEF_FIELDS
                            WHERE   (PROGRAM_ID = 'VMMFGWIN_OP')
                                 AND (DOCUMENT_ID IS NOT NULL)) AS t1
                    GROUP BY id, doc) AS t2 
   LEFT OUTER JOIN
     (SELECT  
          WORKORDER_TYPE + '~' + WORKORDER_BASE_ID + '~' 
          + WORKORDER_SPLIT_ID + '~'
          + WORKORDER_LOT_ID + '~' + WORKORDER_SUB_ID
          + '~' + CAST(SEQUENCE_NO AS varchar(5)) AS doc3,
           WORKORDER_TYPE, WORKORDER_BASE_ID,
           WORKORDER_LOT_ID, 
           WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, 
           SEQUENCE_NO
         FROM          dbo.OPERATION
              WHERE      (WORKORDER_TYPE = 'W')) AS t3 
         ON t2.docx = t3.doc3

I used this statement in my view. Basically I had to first take the UDFs table, (which had the concatenated fields), summarize it to get the opr fields, and then join it to the Operation table to split out the key fields with the user defined fields.