select entry(x,y,z) as columnname

[schaapie]

Is it possible to select an entry of a db-column as a view-column?
We have a field that contains a delimited list in the db and I want to present these as separate values to the customer in a view.

What I would like to do is:

[LEFT]SELECT TABELALGEMEEN . CODE,
TABELALGEMEEN . IDTABELALGEMEEN,
Substring ( TABELALGEMEEN . OMSCHRIJVING , 1 , 1 ) as subid,
Entry ( 1 , TABELALGEMEEN . OMSCHRIJVING , ',' ) as subid2,
TABELALGEMEEN . OMSCHRIJVING
FROM PUB . TABELALGEMEEN
WHERE TABELALGEMEEN . TABELCODE = 'Something'[/LEFT]

The substring works, the Entry doesn't.​

Is there an Entry-function in sql?
Or is it possible to do this with a stored-procedure?

Any suggestions...?

Niek

 

[tamhas]

You could roll your own with INSTR and SUBSTR or SUBSTRING, but look up the docs on REPLACE to convert the delimiters in your field to semicolons and the PRO_ELEMENT to extract the individual entries.

So, who designed this non-normal database ... someone with a background in PICK?

 

[schaapie]

Thanks a lot, that will do the trick.

This happens because we build a release-based application, and at some time we "close" the db-scheme.
So not all the code needs to be recompiled all the time etc.

When a developer needs to build new functionality for which he would normally need a db-scheme-change, which he can't get anymore, things get put delimited into some field.

Niek

 

[tamhas]

Excuses don't make it a defensible practice.

 

[schaapie]

Why is everybody a saint on the internet?


get real

bye

 

[Casper]

Excuses don't make it a defensible practice.

Well, not if you where my boss :biggrin:

 

[tamhas]

Normalization is not a qualification for saintliness. It is self-defense. I've been delivering ABL applications to a customer base since 1986 ... this kind of stuff just comes back to haunt you.

 

[Casper]

offcourse you're right, the problem is that in most company's I know there are more powers at work then reason, best practice and long term vision.

There are situation in which something has to be ready yesterday and common sense, proper design and long term savings are hard to defend.
So solutions like ' put those in a param field and we fix it later' are pretty common, although I agree that the fix me later part is most times omitted and in the long run those things are going to haunt you.

But we are (unfortunately) not always in a position to decide those things....

Casper

 

[tamhas]

I am aware that not every programmer is in the position to insist on doing things right because he or she may have a boss that tells one to do it another way. But, one should never become inured to the wrongness. If possible, one should speak up. One should be asking oneself whether one is doing the best thing for one's career by working in an environment where one is being taught bad practice. Even if one in the end simply does what one is told, one should make sure to internally wince each and every time.

I confess that there have been times when I have gotten stumped by trying to figure out how to get something to work the right way and have reluctantly given up and used a kludge to get the job done expediently ... but I have always made sure to wince and never told myself that it was OK to do it again.