No result from SQL-Query HANA Studio

[Former Member]

Hi,

Data have been  inserted in SBO HANA 9.0. When I make a Query in the HANA Studio I got no result by this Query:

select * from "TRAININGDB_ENGLISH"."OITM" WHERE "ItemCode" = 'PURSER'

when I change the Query to:

select * from "TRAININGDB_ENGLISH"."OITM" WHERE TRIM("ItemCode") = 'PURSER'

I got a correct result.

[Former Member]

Hi Manfred,  Cloud try LTRIM and RTRIM both to check exec result?  Regards, Jerry

[Former Member]

Hi Manfred,

Cloud you post OITM table structure? It is column or row table?

Regards,

Jerry

[Former Member]

Hi Jerry,

that is the structure of the OITM : (the beginn and the end).

with RTRIM and LTRIM I got also everytime a result.

CREATE ROW TABLE "TRAININGDB_ENGLISH"."OITM" ( "ItemCode" NVARCHAR(20) CS_STRING NOT NULL,

           "ItemName" NVARCHAR(100) CS_STRING,

           "FrgnName" NVARCHAR(100) CS_STRING,

           "ItmsGrpCod" SMALLINT CS_INT DEFAULT 100,

           "CstGrpCode" SMALLINT CS_INT DEFAULT -1,

....

           "U_beas_prodrelease" CHAR(1) CS_FIXEDSTRING,

           "U_beas_mps" CHAR(1) CS_FIXEDSTRING,

           PRIMARY KEY ( "ItemCode" ) )

Regards

MAnfred

[former_member184768]

Well, trim = ltrim(rtrim(data)), but since the data already does not have any blank space as confirmed by Manfred, none of the trim functions should have impact on the query output.

Also the replace function did not show any blank values in the data.

I tried to create a row table with varchar field and inserted data smaller than the column width. But I could get the result without the need to use trim function.

I am not sure, but this could also be a bug. Back to a basic question, can you please check and confirm if your DB server and HANA studio are on the same revision.

Regards,

Ravi

[Former Member]

Hi Ravi,

Hana DB Version is: 1.00.53.375657

Hana Studio Version is: 1.00.53.201303281842

Regards,

Manfred

[former_member184768]

Hi Manfred,

May be it could be a bug. Can you please check one thing, just to be very sure.

Please update the table as follows:

update "TRAININGDB_ENGLISH"."OITM" set "ItemCode" = 'PURSER'

WHERE "ItemCode" = 'PURSER'; -- TRIM("ItemCode") = 'PURSER' -- please check which ever works.

The update is to ensure that you have the data without any blank spaces and then you can try the select statement again.

If it still doesn't work, then it is a bug for sure.

Can you please check the feasibility to upgrade to more recent revisions.

Regards,

Ravi

[Former Member]

Hi Ravi,

the update works, and after I update the row the query works like its should.

Thanks and regards,

Manfred

[former_member184768]

Mysterious way HANA works.

I think the data loaded to your tables might have different encoding. I am not completely sure, but it seems a possible issue.

Is it possible for you to copy the data to a simple text file and then load to your tables.

Regards,

Ravi

[Former Member]

Hi Manfred,

I have checked and tested based on posted table structure, It has no problem, I could not find reason, it maybe encode issue.

Could you try below sql statement to check again.

UPDATE "TRAININGDB_ENGLISH"."OITM" SET "ItemCode"='PURSER' WHERE TRIM("ItemCode") = 'PURSER';

select * from "TRAININGDB_ENGLISH"."OITM" WHERE "ItemCode" = 'PURSER';

Regards,

Jerry

[former_member184768]

Hi Jerry,

I think he already tried that and it works. , May be, you missed that part.

Regards,

Ravi

[Former Member]

Hi Ravi,

Thank you for your reminder, I checked, sorry for my missed this part.

Based Manfred's test result, I agreed with you, it should be imported data encoding issue.

Hi Manfred,

Please ignore my posted comments within today, you can check it based on Ravi's suggestion. You can change encoding to UTF-8 as well, I think.

Regards,

Jerry

[former_member184768]

Hi Manfred,

Please run the following query to identify the blank spaces in the data.

select lenght('PURSER') as "L1", length("ItemCode") as "L2", replace("ItemCode", ' ', 'XX') from "TRAININGDB_ENGLISH"."OITM" WHERE "ItemCode" like '%PURSER%'

compare the first length L1 with L2 and check if the blank spaces in third column are replaced by XX.

Regards,

Ravi

[Former Member]

Thanks Ravi,

but the length for both is 6 , PURSER is retrieved as the third Column and the OITM.ItemCode SQL Data Type is nvarchar(20).

Regards,

Manfred

[former_member184768]

Hi Manfred,

If there are no blank spaces, then trim really doesn't remove anything. Can you please check the following query:

select * from "TRAININGDB_ENGLISH"."OITM" WHERE "ItemCode" like 'PURSER';

If there are no blank spaces and the value is indeed just 'PURSER', then the query should return the data.

You can also check ascii(left("ItemCode", 1)) to check if it returns 32 for blank value.

Regards,

Ravi

[Former Member]

Hi Manfred,

The table("OITM") should be have record with space word, you can check your original imported data whether contain space word or not.

besides, could you also check column type for "itemcode"? it is char or varchar type or other type.

If the column type is char, then it will auto-insert space word, if the record has no enough characters.

Regards,

Jerry