Leading Zeros, load text(...) and functions in sql select(...)

[#1]

Hello everyone,

sorry for opening the nth Thread about coping with leading zeros, but i have trouble on the finishing line with the information i found so far.

I found a few threads concerning loading data when keys can be "1", "01" or "001" an mean different things.

The (almost) canonical answer to the question i found is:

"load text(column) as column;

sql select column from table;"

For this example (extracting a column 1:1 into qlikview), it works perfectly.

However, i have a slight problem in my script, as a lot of functions (especially nvl, decode, ltrim/rtrim) are called in sql.

For example, one line in my script right now is:

sql select nvl(possible_entry1,possible_entry2) as used_entry from table;

(nvl is an oracle function that gives possible_entry1 if it is not NULL and possible_entry2 otherwise).

Now i have the same problem again: possible_entry1 and possible_entry2 can both contain "1" and "001" meaning different things.

however:

load text(used_entry) as used_entry;

sql select nvl(possible_entry1,possible_entry2) as used_entry from table;

gives an error when executing the "sql"-Line:

Field not found - <used_entry>

How can I apply the Text-Function in this example?

"load text (nvl(...))" does not work because the Qlik-View does not know about nvl, this part is done on the oracle-side.

[#2]

you can use:

if(isNull(used_entry), 'value', text(used_entry)) as used_entry

[CELAMBARASAN]

Hi,

     Try with this

load text([used_entry]) as UsedEntry;

sql select nvl(possible_entry1,possible_entry2) as used_entry from table;

May be its because same field name usage.

Celambarasan

[#4]

load text([used_entry]) as UsedEntry did not work, sorry.

I got the same error as before.

As for moving the logic out of the sql part into the load-statement as Federico Levi suggested, this would be the last resort. The nvl(..) line is just an example from the whole script, and i would rather avoid rewriting every sql-statement in corresponding functions in qlikview.

What I do not understand is that the error comes up when executing the sql select, and not the load-statement.

If i leave the load-Statement out completeley, the sql executes without error (but mixes the '1' and '001' up).

[jagan]

Hi,

What error you are getting, attach the script or sample file.

Regards,

jagan.

[#6]

The error i get is "Field not found"

The script (reduced to the important part):

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YY';
SET TimestampFormat='DD.MM.YY hh:mm:ss[.fff]';

CONNECT TO [Provider=MSDAORA.1;Password=xxxx;User ID=xxxx;Data Source=m1db_tcp_entw];

// works
load TEXT(ORT_VORWAHL) as ORT_VORWAHL;
sql select ORT_VORWAHL from m1ort;

// does not work
load TEXT(ziffer) as ziffer;
sql select nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer from m1bhlst;

[jagan]

Hi,

select nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer from m1bhlst;

is the above query executes correctly in the db.  Make sure. 

There is nothing wrong in your script;

Regards,

Jagan.

[#8]

sql executes correctly (see below), and if i leave out the "load"-statement, the scripts executes without error (but mixing up 0003 and 3).

SQL> select nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer from m1bhlst;

ZIFFER                                                                         
-------                                                                        
31138                                                                          
31101                                                                          
0003TK                                                                         
0003                                                                           
0003TK                                                                         
0003                                                                           
3
[....]

1617 Zeilen wurden ausgewählt.

SQL> spool off

[jagan]

Hi,

Try like this

TempTable:

sql select

          nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer,

          1 AS Temp

     from m1bhlst;

TableName:

LOAD

     Text(ziffer) AS ziffer

RESIDENT TempTable;

DROP TABLE TempTable;

Hope this helps you.

Regards,

Jagan.

[#10]

I thought that this way the values with leading zeros would be thrown together in "TempTable" already, but I tried it anyway, and now i get the same error "Field not found <ziffer>" during the load-Statement in "TableName:".

Right now i am really confused, i even get this error with:

TempTable:

select 'test' as a from dual;

load a as b resident TempTable;

... which is almost straight out of the QV-Help to the "Load"-Command. I think I am doing something really wrong, but i cannot see it.