extracting data from oracle fields with type Char(nn)

[bradshields]

We are extracting some data from oracle tables that contain some structure from long ago. Some fields are char(2) and contain 16 separate flags as attributes. We have managed to extract these using syntax like this

num(ord(mid(STO_DTL2, 1 , 1)), '(bin)') as Flag_1, etc

There are other fields that are much longer like char(120) these seem to be for example 6*20 character fields that we need to split up.

Questions:

Is there an approach we can use to do this?

we first stored each table in Oracle in as QVSs and are trying to work with the QVD.

I would have attached a sample of the data as a qvd but can't see a way to attach

Any suggestions greatly appreciated

[#2]

I can't help on the data problem, but attaching an example would probably be helpful.

You can add an attachment when replying to a message or when composing a new one. While editing your post, there should be three tabs across the top: Compose, Options and Preview. On the Options tab, there is a File Attachment - Add/Update button. Clicking on that button will allow you to choose a file to upload.

If you're uploading a real file, look into data scrambling to obscure private data.

If you could upload some sample data, I'm sure one of the experts here could help you out.

[Oleg_Troyansky]

Your approach seems valid - it's perfectly fine to parse long strings using function mid().

Oleg

[bradshields]

When we have char(2) fields that are a series flags we have no problem in extracting each bit as a 1 or 0

However we have longer field like char(120) that seem to be binary fields. How can we bring that into qlikview. We are currently reading the oracle table and pulling it in and storing it as a qvd. I have attached a sample of a qvd with one of the fields. Is QlikView changing the format as it writes the QVD? Is the a way to force the field to be binary? Once it is in a binary field how do I show it in another format like decimal?

I see this on pg 318 of the manual but it is not clear how to write the load statement

fieldref::= ( fieldname | @fieldnumber | @startpos:endpos[ I | U | R|
B] )

Can someone show the full script? And will specifying this field as a binary one possible in the initial load from Oracle, or from the QVD or only from a text file?

Thanks again for your help

[bradshields]

reading the manual more it seems the last post's approach of using the load statement would only work if the table being read is fixed length.

We have tried writing a javascript macro to be used for each binary field and whilst this seems to work fine elsewhere it does not generate correctly in QV

this is one attempt

function string2int(_string, _start, _length)
{
if ( _start === undefined ) {
_start = 1;
}
if ( _length === undefined ) {
_length = _string.length;
}

_end = _length - _start;
_start = _start - 1;

_ret = 0;
_pwr = _length - 1;

for (i=_start;i<=_end;i=i+1) {
_ret = _ret + _string.charCodeAt(i) * Math.pow(2,_pwr*8);
_pwr = _pwr - 1;
}

if ( _ret < Math.pow(2,(i*8)-1) ) {
return _ret;
}
return _ret - Math.pow(2,(i*8));

}

Would we be better using vbscript? Is there another approach?

[#6]

Hi Brad,

This also fails with VB Script. (See attached qvw). I think the issue is that the scripting languages are passing the bytes as strings and the null char '\0' represents the string terminator.

[rbecher]

Brad,

if this is a problem with binary chars you could use the Oralce function translate to translate chr(0) into chr(48) => '0' and chr(1) into chr(49) => '1' like this:

select translate(<column>, chr(0) || chr(1), '01') as translated_field from ...

Ralf