Downloading from SQL Server 2008

[#1]

Hi,

I'm new in QV and hope you can help me with the following issue.

I'm connecting to SQL Server using OLE DB Provider.

This is my SELECT Statement:

SELECT

    PROJID

  FROM "Kom50_Test".dbo.PROJTABLE

The value in one of rows is '00001.00'. But it is displaying as '00001' (Zeroes after dot are trimmed).

If I'm using WHERE clause

SELECT

    PROJID

  FROM "Kom50_Test".dbo.PROJTABLE

WHERE PROJID = '00001.00'

the same value is displaying as '00001.00'.

How can I get correct value in the result of first statement?

Thanking you.

[its_anandrjs]

Hi,

When you get this PROJID field in the QV application then set is Number format in Properties -> select Number -> and precision is 2 or set in place of number fixed to upto 2 decimal place.

Rgds

Anand

[kaushiknsolanki]

HI,

    Let me explain what actually happen with both of your code.

    In first code qlikview took the ProjID field as number field and by default the number format is set to 0 decimal thus the decimals are trimmed.

    In second case you said where ProjID = '00001.00' , as you have defined the 00001.00  in a single quote, qlikview considered this field as a string field and thus it shown you the field as it is.

    Now if you want to have number with 2 decimal, you can get it this way.

    Load

      num(ProjID,'#,##0.00') as ProjID,

      .......

    ;

    SQL Select * from XYZ;

    Hope its clear to you now.

Regards,

Kaushik Solanki

[SunilChauhan]

properties->number-> fixed to and type 2

it show you ressult till 2 precision

[#5]

I'm sorry, I forgot to specify, that PROJID is a text field.

The other values, such as '00001.01', '00001.02' etc., are displayed correctly.

[kaushiknsolanki]

Hi,

   ok then try this.

   load text(ProdID) as ProdID

   from xyz.

Regards,

Kaushik Solanki

[#7]

Function text() doesn't work in SELECT statement. Standard SQL functions CAST and CONVERT also don't solve the problem.

Really, QV treats text as a numeric value.

If I'm selecting

'P'+PROJID as PROJID

values are correctly dispayed.

It's acceptable solution, but the problem still exists.

[kaushiknsolanki]

Hi,

     Try this way.

     Load text(ProdID) as ProdID,

     Field1,

     Field2;

     SQL select * from xyz;

Regards,

Kaushik Solanki