Progress-ODBC-TSQL CASE Statement for Dynamic Column is Truncating Data

Question

I am using an ODBC driver to connect to a Progress database via a Windows C# application. The problem I am having is my data is being truncated.

SELECT 
    CASE 
        WHEN (table1_qty_comp = 0) THEN 'Pending' 
        ELSE 
            CASE WHEN (table1_qty_comp >= table2_qty_req) THEN 'Completed'       
            ELSE 'In-Process' 
            END
       END AS 'Status'
FROM 
    table1 LEFT JOIN table2 ON table1_part = table2_part

Only 8 characters will appear in my 'Status' column so 'In-Process' turns into 'In-Proce'.
I've tried various casts/converts such as

cast('In-Process' as varchar)
cast('In-Process' as varchar(12))
cast('In-Process' as nvarchar)
cast('In-Process' as nvarchar(12))
convert(varchar(12), 'In-Process')
convert(nvarchar(12), 'In-Process')
str('In-Process')

to no avail. How can I get the full 'In-Process' to appear in my column?

Here is how I query the Progress database from C#

DataTable dt = new DataTable();
try
{
    using (OdbcConnection conn = new OdbcConnection(GetConnectionString(db)))
    {
        OdbcCommand cmd = new OdbcCommand(qry, conn);

        conn.Open();
        OdbcDataAdapter adpt = new OdbcDataAdapter(cmd);
        adpt.Fill(dt);
    }                
 }
 catch (OdbcException e)
 {

 }

 return dt;

Update

I wanted to append my question with what may be useful information... This is ODBC driver: Vendor=DataDirect, Progress SQL92 v9.1E, version 4.10.01. Here are some resources for this particular driver provided by another user in the comments, here and here Also, I was able to solve my issue by not using nested CASE statements like:

SELECT 
        CASE 
            WHEN (table1_qty_comp = 0) THEN 'Pending'                 
            WHEN (table1_qty_comp >= table2_qty_req) THEN 'Completed'       
            ELSE 'In-Process'               
        END AS 'Status'
    FROM 
        table1 LEFT JOIN table2 ON table1_part = table2_part

Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/185277/discussion-on-question-by-smitty-werben-jager-manjenson-progress-odbc-tsql-case). — Samuel Liew, Dec 15 '18 at 00:20

T.S. · Answer 1 · 2018-12-12T20:38:10.867

2

To clarify this answer. While the OP problem could be solved with multiple WHEN in CASE, my understanding was that this is pseudo-code and OP needs sub-case. In this context there is a bug [I think] that truncates string data. The below syntax is to solve that issue. I think, value of this question is in the data cutting rather than that you can use multiple WHEN

SELECT 
    CASE 
       WHEN myCol = 0 THEN 'Pending' 
        WHEN myCol = 1 THEN 'Completed' 
        ELSE 'In-Process' 
    END  Status
FROM
  (SELECT 
       CASE 
           WHEN (table1_qty_comp = 0) THEN 0 
           ELSE 
               CASE WHEN (table1_qty_comp >= table2_qty_req) THEN 1       
               ELSE 2 
               END
       END AS myCol
    FROM 
        table1 LEFT JOIN table2 ON table1_part = table2_part) t1

edited Dec 12 '18 at 20:38

answered Dec 12 '18 at 19:59

T.S.

18,195
11
58
78

You're the best! I wasn't aware you could use multiple `WHEN` statements in a `CASE` statement. Ended up using `SELECT CASE WHEN (table1_qty_comp = 0) THEN 'Pending' WHEN (table1_qty_comp >= table2_qty_req) THEN 'Completed' ELSE 'In-Process' END Status` – Smitty-Werben-Jager-Manjenson Dec 12 '18 at 20:27
1

@Smitty-Werben-Jager-Manjenson Well, yea. I kind of let that slide because I thought, your code is PSEUDO-code, which partially true, and which just shows your NEED to have sub-case. Yea, if you don't need id - as you found out... At that point I was solving issue of some sort of bug that shortened data rather than ask you id you NEED this sub-case. I think, value of your question is in the data cutting rather than that you can use multiple WHEN – T.S. Dec 12 '18 at 20:31
@Smitty-Werben-Jager-Manjenson can you still try this syntax and see if it works? – T.S. Dec 12 '18 at 20:39
It is mostly pseudo code... I didn't want to give out the actual fields/tables of our clients' database, and there are more complex JOINS and such but the string truncation was really breaking my brain. I really appreciate all of your time and help with this problem! – Smitty-Werben-Jager-Manjenson Dec 12 '18 at 20:40
I tried the latest syntax and it gives me a syntax error. One of the bad things with the ODBC driver and the ODBC Query Tool program is they don't give you much information as to why the error occurred or what caused it. "Syntax error" is about as good as it gets. – Smitty-Werben-Jager-Manjenson Dec 12 '18 at 20:46
Well. don't want to push it but it works for me in Sql Server, Oracle and Mysql. Because this is just standard SQL – T.S. Dec 12 '18 at 21:14

score 1 · Accepted Answer · answered Dec 24 '18 at 15:58

The Progress database stores all values as variable length and does not honor any width attribute.

The Progress 4GL is perfectly happy dealing with "over stuffed" fields. This is normal for a Progress application but it gives fits to SQL clients.

You are running Progress version 9 (which is ancient, obsolete and unsupported) so your only options are to either deal with it on the client (as seen in the post and the answer by T.S.) or to run the provided "dbtool" utility. The dbtool utility will scan the database and find any fields that have been over stuffed and then adjust the "sql width" so that SQL clients have an accurate picture of the actual data width.

This might be helpful:

Fixing sql length error in progress 4gl 10.2B

(that answer is for OpenEdge 10.2b but it will work just as well with Progress 9.1e)

Progress-ODBC-TSQL CASE Statement for Dynamic Column is Truncating Data

2 Answers2