-
Kurt Triebe
- PowerBuilder
- Monday, 17 July 2023 03:35 PM UTC
Hi all,
I'm a dev on a very large cross-DB-platform PB application. I'm trying to solve an issue that has plagued us as long as I can remember. The problem is with computed columns, specifically ones that are defined as two single ticks, with nothing in between them, like test_string_col in the following query:
SELECT acct.acct_id,
acct.acct_full_nm,
acct_loc.term_type,
'' test_string_col,
cast(0.0 as decimal(17,4)) test_dec_col,
'AB' test_two_char_str_col,
' ' test_one_space_str_col,
acct_loc.tax_exempt
FROM acct,
acct_loc
WHERE ( acct.acct_id = acct_loc.loc_acct_id )When the SQL is edited while connected to an Oracle database, this gets translated to a char(0) column in the DW syntax. However, if the SQL is edited while connected to any other kind of DB/driver, from what I can tell (ODBC/SQL Server is really the only one I care about, though) it always ends up being char(1).
The SQL Server result is a disaster for 2 reasons.
First, it results in a field that is hard-capped at a single character, regardless of what edit mask is applied- whereas char(0) results in a column that can have any amount of text entered. We use this technique frequently as a dummy text entry field for something connected to a decimal ID value, and only being able to enter a single character here is a Very Bad Thing.
Second, let's say we've got 2 datawindows that we do RowsCopy back and forth on. If one gets edited while connected to SQL Server, and the other Oracle- the RowsCopy will fail, as there's a conflict between the column types of char(1) and char(0). Very often, a change just needs to be made to one of the DWs- and if the dev doesn't remember to connect to an Oracle DB first, they unknowingly create DWs that will eventually cause a RowsCopy problem.
This ends up being a problem that easily slips through the cracks on testing, and is also very difficult to identify via a search. If I could get all of our devs to ONLY connect to Oracle DBs while edited DWs, we'd be fine- but that has proven to be quite difficult, as NONE of our clients use Oracle- they ALL use SQL Server- and thus, our devs are frequently connected to SQL Server DBs, and I can't get them to remember to pop over to an Oracle DB for DW edits.
So, for the time being- we HAVE to keep an Oracle server around JUST for this reason- we HAVE to get any DWs that use this syntax to come out as char(0), not char(1). And, for any of you that are familiar with Oracle DBs... well... I'm sure you know just how big of a PITA those are. Everything about Oracle DBs is a thousand times harder than necessary, and to have to put up with this hassle JUST to keep this one DB around for DW edit purposes... gigantic waste of time & effort.
Ideally speaking, I would love to find some way to get SQL Server to create char(0) for computed string columns like this.
The second best option would be finding an alternative DB platform besides Oracle, that's not as much of a PITA to deal with as Oracle, that creates char(0) instead of char(1). It's not a true solution, but it's at least a less painful situation from an administrative standpoint- getting to dump the Oracle instance would be a happy day.
Any ideas?
Thanks,
Kurt
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.