Maximo Open Forum

Looking for some assistance in creating a SQL query to split the GL Account field Value into separate columns by GL segment.  For example:  If my code block consists of 6 segments split by a - as a delimiter and I would like to query and return the information into individual columns by segment.  

The SQL query varies a little based on which database you are using. Are you on Oracle/SQL Server/DB2.Based on your database,I can send you a sample script.

Thanks

SQL Server

Hi,

I may have misunderstood your request, but when I run the following query in SQL Server, I get the screenshot result, which shows each GL Component in separate columns.

SELECT *
FROM chartofaccounts c
where active = 1 AND glaccount LIKE 'p4%'

You could join the main table like workorder with the chartofaccounts table to retrieve the records.
Example:

select wo.glaccount,ca.glcomp01,ca.glcomp02,ca.glcomp03,ca.glcomp04 from workorder wo 
inner join chartofaccounts ca on wo.glaccount=ca.glaccount and wo.orgid=ca.orgid
where wo.wonum='WO12345';

Try this:

SELECT GLACCT, 
    PARSENAME(REPLACE(GLACCT,'-','.'),6)AS PART1,
    PARSENAME(REPLACE(GLACCT,'-','.'),5)AS PART2,
    PARSENAME(REPLACE(GLACCT,'-','.'),4)AS PART3,
    PARSENAME(REPLACE(GLACCT,'-','.'),3)AS PART4,
    PARSENAME(REPLACE(GLACCT,'-','.'),2)AS PART5,
    PARSENAME(REPLACE(GLACCT,'-','.'),1)AS PART6
FROM TABLE 

Gail, thanks for the help, the function does work as expected, but we have "." characters within our component values and the parsename function appears to use the . to split which is causing it to successfully execute.  Any other ideas?  I did add an example of a code structure

test1-test2-test3-test4-test5.100.10-test6