Help with SQL statement

[h0gg1e]

I am trying to use SQL Server 2000 to pull from Progress using OpenLink Lite 8.3e .. the query I am trying to run is

select usrw_charfld[11] format x(20) from usrw_wkfl

i can run with no problems so it is a syntax issue
select usrw_charfld[11] from usrw_wkfl

the error I recieve is
[OpenLink][ODBC][Progress Server]** Unable to understand after -- "DECLARE". (247)

format is a key word in sql ... does anyone know how I can run this command?

Thank you for any help ... been scratching my head over this for a week.

 

[tamhas]

If you ran this in the Progress editor, it might work, but that format statement is an ABL phrase, not a SQL one.

 

[h0gg1e]

this works in Progress
select usrw_charfld[11] format "x(20)" from usrw_wkfl


I just need the sql version .. the problem is, the field usrw_charfld[11] is defined as 8 characters, but we store 20 in it (I come from the SQL world, so I'm not sure exactly the terminology of being able to do that)

Whenever I try to pull that field to my sql server I only get 8 chars, but of course I need the entire 20.

I may have to have our Progress programs dump it to a csv then pull from there.

 

[tamhas]

Depending on your Progress version, there is a SQL width field in the definition of the field _field._width which controls the width returned to SQL. There is also a utility in recent versions for setting this automatically based on the data.

 

[h0gg1e]

I talked to one of our progress programmers and she is wondering if that needs to be set when the table is built or can it be set for an existing table without damaging the data? We are running progress v8.3c

Thanks for all the help so far

 

[tamhas]

It can be set at any time. The utility in recent versions scans through the data and sets it based on the actual contents of the field, but if you know what it should be, just set it and try it.

 

[h0gg1e]

thanks again ... I'll try it out

::edit::
after talking to our progress programmer she can't find on the progress side where this is set, this is what she said.

"can you find out exactly where this is set....I looked at the table definitions and do not see anything related to sql field definitions. Based on what you said, should it be set somewhere in the ODBC pull of the data ?"

i dont' see anywhere to set return width within sql server.

Is return size option set on the prgress side or sql side and where can I find it?

If its on teh progress side does the table need to be re-compiled?

 

[tamhas]

_Field._Width
If you don't have that in your database because it isn't recent enough, then you are probably out of luck.

 

[h0gg1e]

I bet we don't have it ... I know we are running a very old verison of MFG ... I guess back to having them dump out to csv and me picking it up from there.

thanks for all your help, I least know this isn't possible currently

 

[Casper]

I suspect openlink works the same as using OIB.

If that's the case changing the display format to "X(20)" will resolve your issue.

Display format will have no effect on the crc.

HTH,

Casper.

 

[JeremyGiberson]

So long as your table field length is shorter than your desired length (ie 11 vs 20) then you can use a simple pad function to get the desired result.


Example:

select RPAD(usrw_charfld,20) from usrw_wkfl

Also there is LPAD which will pad the string on the left instead of the right. Either function accepts a third parameter which is the character you want to use to pad the string.