topic AS400 packed decimal field in QlikView

AS400 packed decimal field

karensmith — Fri, 24 Jul 2009 01:31:32 GMT

I have two identical calculations in my script. The first always works just fine, but the second returns does load. Usually the values are the same, but in some cases they may be different. Sources data field is AS400 packed(decimal, 15,6). Are there any bugs with num function with AS400 packed fields. I have verified the source data, the load script and the result, but this strange occurence just not make any sense.

num

(SPLCAB, '0000.000000') AS LOCAL_SALES_PRICE

num

(SPOCAB, '0000.000000') AS SALES_PRICE

Has anyone experience anything like this or knows what could be causing the problem.

AS400 packed decimal field

Fri, 24 Jul 2009 02:07:45 GMT

I'm not going to attempt to answer the question of bugs with AS400. I can share with you one solve I have used when dealing with strange formatting issues. I will also say up front here that I don't know the difference between the two functions, and the QV documentation is very poor in this area, but QV has a couple of number formatting functions:

Num()

Num#()

I used Num#() once out of desperation when trying to format a column I was pulling from a table and it seemed to work much better.

Sorry i am not more help. Hopefully this may give you a work-around until you can determind the root cause.

sjprows

AS400 packed decimal field

rwunderlich — Fri, 24 Jul 2009 02:45:25 GMT


sjprows wrote:
I'm not going to attempt to answer the question of bugs with AS400. I can share with you one solve I have used when dealing with strange formatting issues. I will also say up front here that I don't know the difference between the two functions, and the QV documentation is very poor in this area, but QV has a couple of number formatting functions:
Num()
Num#()
I used Num#() once out of desperation when trying to format a column I was pulling from a table and it seemed to work much better.
Sorry i am not more help. Hopefully this may give you a work-around until you can determind the root cause.

sjprows<div></div>

Actually one is a formatting function and the other is an interpretation (input) function. If you are familiar with SAS, formatting -- num() -- is like a SAS "format" and interpretation -- num#() -- is like a SAS "informat".

AS400 packed decimal field

rwunderlich — Fri, 24 Jul 2009 02:48:41 GMT


KarenSmith wrote:but in some cases they may be different

Can you give a sample of some of the data and the incorrect results you are seeing? What does the data look like if you don't use the num function at all?

-Rob

AS400 packed decimal field

karensmith — Fri, 24 Jul 2009 03:43:02 GMT

I realized the problem is with my join statment here it is below.

I have load

......

then sql statement:

SQL

SELECT *

FROM

S10A5929.USSOMEA.SMABP00

LEFT

OUTER JOIN S10A5929.ULCLMEA.MEAPDP02

S10A5929.USSOMEA.SMABP00.CARDAB = S10A5929.ULCLMEA.MEAPDP02.CARDAB

WHERE

S10A5929.USSOMEA.SMABP00.STATAB = ' ' AND S10A5929.USSOMEA.SMABP00.ORDCAB = '';

the following statement works fine from SQL session of AS400 the only difference is the qualifer names. is there something wrong with QV syntax?

SELECT *
FROM USSOMEA/SMABP00
LEFT OUTER JOIN ULCLMEA/MEAPDP02
ON SMABP00.CARDAB = MEAPDP02.CARDAB
WHERE STATAB = ' ' AND SMABP00.ORDCAB IN('','P')
ORDER BY SMABP00.CARDAB ASC

AS400 packed decimal field

rwunderlich — Fri, 24 Jul 2009 05:24:30 GMT

The SQL is executed by the ODBC driver and the database, not QV, so there should not be a problem caused by QV.

How does the data look if you don't do the preceeding load -- that is, just use the SQL select in the script to get the raw data?

-Rob

AS400 packed decimal field

karensmith — Fri, 24 Jul 2009 05:58:28 GMT

Thanks Rob,

Query ran fine without load; as a select * sql statement alone, but this makes no sense to me. Shouldn't it be possible to use load and sql? my load statement had many qv functionality. do you have any other recommendations.

AS400 packed decimal field

rwunderlich — Fri, 24 Jul 2009 06:13:47 GMT


KarenSmith wrote:Shouldn't it be possible to use load and sql? my load statement had many qv functionality. do you have any other recommendations.

Now that you've confirmed that the SQL data is coming across fine, examine what the load is doing to your data -- the table itself and the relationships to other tables. I'm assuming your LOAD is more complex than "LOAD *". If you have further questions, post the qvw if possible.

-Rob

AS400 packed decimal field

karensmith — Fri, 24 Jul 2009 06:24:49 GMT

I will continue to look at my load statment. I will also post qvw. Just need to figure out how.