Same script, two files, one dropping preceeding 0's

[adamdavi3s]

Hi,

I have a basic SQL script to load cost centres into two separate dashboards:

load costc_code,
 costc_code_description,
 costc_description;

SQL select

d1.dim_value AS 'costc_code',
d1.dim_value + '-'+d1.description AS costc_code_description,
d1.description AS 'costc_description'
FROM agldescription d1
WHERE d1.client='SD' and d1.attribute_id='C1';

In one dashboard it loads the codes correctly e.g.

00080

00090

00091

However in the second it is loading as:

80

90

91

In BOTH cases it is loading the CORRECT value into the concatenated description. e.g. 00080-COSTCENTRE1

The code in the script is EXACTLY the same in both dashboards and both are using the same ODBC connection so I can't understand why I am getting different results...

[marcel_olmo]

Could you upload a QV document with your example? It would be easier to understand why i'ts not working.

Regards.

[Anonymous]

Data is the same, but formatting may be different.

[adamdavi3s]

Hi Both,

I just created a blank file with the load script and it loads the items correctly (WITH the preceding 0), which suggests there must be something in one dashboard causing it to load in the wrong format, but I don't know what this could be??

[adamdavi3s]

Test file now uploaded.

I've checked all the 'SET' clauses at the start of both files and they do not differ at all.

[marcel_olmo]

If you assure me that they are exactly the same codes, the result cannot be different.

If you upload a QV with your info maybe we can help you. Otherwise, we're not wizards.

Regards.

[prieper]

Hi,

if you wish to maintain the codes as string (i.e. with leading "0"), use the function TEXT(costc_code). This will force QV to treat it always as text.

HTH
Peter

[adamdavi3s]

Hi Marcel,

The QV files are massive I am afraid, I will try and compress one down. I just wasn't sure if there was a setting or something that I could be missing!

Investigating further:

Both scripts are exactly the same, although the file which is not working correctly loads some additional data but it is not linked.

The file that is not working appears to be loading the data as numeric, whereas the working file is loading it as text but I can't see anywhere where this is specified

[adamdavi3s]

Hi Peter,

I did initially try the text clause in the script but it made no difference at all (very strangely!) I am afraid!

[adamdavi3s]

Sorted, I have no idea why its loading differently but if I actually remember to alias my text(costc_code) it works