Split a Field Using an SQL View

V

Vexed

New Member
Hi Guys,

I have a problem with a field I have in one of my budget tables. The field is called GL_Account, but unfortunately it contains the GL account code and the budget key, separated using a column ":"

For example: 104000:C0

Now my problem is that I can't link the budget table to the GL Accounts table.

Is there a way to split this field based on the delimiter in an SQL view to have two fields.
GL_Account ====>>> GL_Account_Code and Budget_Key.

Your help is much appreciated.

Thanks,
Bob
 
Try this

SELECT SUBSTRING(GL_Account,1,INDEX(GL_Account,':') - 1),
SUBSTRING(GL_Account,INDEX(GL_Account,':') + 1) FROM budget
 
Thanks mrobles,
I'll give it a try and will let you know how it goes.
I do realize that the database design is bad :(
I'm a business intelligence specialist working on this old database, and I'm hitting serious issues with it, but changing the schema is not an option. What the developers did in the past was fixing all these issues in their 4GL programs (and they kept building on it) not in the database it self, and now I'm struggling with SQL.

Thanks a lot guys.

Cheers
Bob
 
Hi Guys,

Unfortunately I'm getting a syntax error while trying this code:

SELECT SUBSTRING(TESTSQL."Tenant_Suite_Budget"."GL_account_key",1,INDEX(TESTSQL."Tenant_Suite_Budget"."GL_account_key",':') - 1),
SUBSTRING(TESTSQL."Tenant_Suite_Budget"."GL_account_key",INDEX(TESTSQL."Tenant_Suite_Budget"."GL_account_key",':') + 1) FROM TESTSQL."Tenant_Suite_Budget";

I just can't figure out what's wrong, but going back to the progress sql reference, it doesn't look like it is a supported function.

Any ideas?

Thanks,
Bob
 
Well, not being a supported SQL function is a pretty clear indicator of what is wrong.

Have you looked at PREFIX and SUFFIX?

Or, if there is more than one colon in there, consider TRANSLATE wrapped with PRO_ELEMENT to pick off pieces.
 
I've just found an interesting function: INSTR().
It fixed my problem, I'm using this code now:

SELECT SUBSTRING(TESTSQL."Tenant_Suite_Budget"."GL_account_key",1,INSTR (TESTSQL."Tenant_Suite_Budget"."GL_account_key",':')- 1), SUBSTRING(TESTSQL."Tenant_Suite_Budget"."GL_account_key",INSTR (TESTSQL."Tenant_Suite_Budget"."GL_account_key",':')+ 1) FROM TESTSQL."Tenant_Suite_Budget" ;

Tamhas,
Excuse my ignorance mate, I'm new to Progress SQL.

Thanks Guys.
Cheers,
Bob
 
I do recommend the manual. If there is only one colon, PREFIX and SUFFIX will give you a much shorter and more reable statement.

While there, check out SUBSTRING vs SUBSTR
 
Hey Tamhas,

I will try to use PREFIX and SUFFIX, I'll let you know how it goes.

I'm looking at SUBSTR vs SUBSTRING. The look identical except one is a progress extension, and the other is ODBC compatible.

I'm a bit confused. Which one should i be using ? Would it affect performance?

Thanks,
Bob
 
I doubt there is any difference at all, but caution suggests leaning toward compatability.
 
You must log in or register to reply here.
Back
Top