topic Re: How to convert time in numeric to TIME field in SQL Script? in QlikView

How to convert time in numeric to TIME field in SQL Script?

haneeshmarella — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I am using AS400 DB2 SQL script. I am trying to figure out how can I convert numeric time field to a TIME format. I have numeric timefield of different lengths.

Example,

5 which is 00:00:05

10 which is 00:00:10

2529 which is 00:25:29

123223 which is 12:32:23

How do I convert it into TIME within script and not the LOAD since I would like to create QVD based on script, rather than making transformations later based on QVD which takes even longer time due to the large amount of data?

Thank you.

Re: How to convert time in numeric to TIME field in SQL Script?

sunny_talwar — Wed, 18 Jul 2018 16:42:39 GMT

You can do this

LOAD Interval(TIME/86400, 'hh:mm:ss') as TIMEField,

SELECT ....

FROM ....;

Store ....;

LOAD Interval(Time#(Num(TIME, '000000'), 'hhmmss'), 'hh:mm:ss') as TIMEField,

SELECT ....

FROM ....;

Store ....;

Re: How to convert time in numeric to TIME field in SQL Script?

haneeshmarella — Wed, 18 Jul 2018 16:55:41 GMT

Hi Sunny,

I am looking to implement it within the SQL script and not the LOAD. The field B.PRTCR is the TIME in numeric form. I want to convert it as mentioned in the question to TIME FIELD in below script itself without using load.

SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,

A.PHSHCN AS SHIP_TO_COUNTRY,

A.PHSVIA AS PLANNED_SHIP_VIA,

A.PHTCT AS TOTAL_NO_OF_CARTONS,

B.PRUSER AS PKMS_USERS,

B.PRCASN AS CASE#,

B.PRTLOC AS TO_LOCATION,

B.PRMNOP AS MENU_OPTION_NAME,

B.PRFLOC AS FROM_LOCATION,

B.PRSTYL AS STYLE,

B.PRUNTS AS UNITS#,

B.PRDCR AS DATE_STRING,

B.PRTCR AS TIME_STRING,

CONCAT(B.PRDCR,B.PRTCR) AS DATE_TIME_STRING,

C.USER_NAME AS USER_NAME

FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B, CAPM01.WM0272PRDD.USUSER00 C

Re: How to convert time in numeric to TIME field in SQL Script?

sunny_talwar — Wed, 18 Jul 2018 16:59:18 GMT

Couple of things

1) If you really need this to be in SQL, its better to reach out to SQL guys... for starters you can look here

TO_CHAR (datetime)

2) Is there a problem doing this in LOAD statement? What do you not like about it?

Re: How to convert time in numeric to TIME field in SQL Script?

haneeshmarella — Wed, 18 Jul 2018 17:03:23 GMT

Its currently in load statement. Since there is huge amount of data, although I follow incremental load, it has to load from QVD and apply transformations on it, and then add new records from QVD again with transformations, making it unoptimized and takes longer time.

I have searched on many DB2 SQL forums for conversion but am not able to find the right one. Hence, thought someone here would be able to help.

Re: How to convert time in numeric to TIME field in SQL Script?

sunny_talwar — Wed, 18 Jul 2018 17:09:29 GMT

I think you have never used Preceding Load‌. You don't have to transform this while loading from the qvd... but you can transform this before saving this data to qvd like this

LOAD *,

Interval(Time#(Num(TIME_STRING, '000000'), 'hhmmss'), 'hh:mm:ss') as TIME;

SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,

A.PHSHCN AS SHIP_TO_COUNTRY,

A.PHSVIA AS PLANNED_SHIP_VIA,

A.PHTCT AS TOTAL_NO_OF_CARTONS,

B.PRUSER AS PKMS_USERS,

B.PRCASN AS CASE#,

B.PRTLOC AS TO_LOCATION,

B.PRMNOP AS MENU_OPTION_NAME,

B.PRFLOC AS FROM_LOCATION,

B.PRSTYL AS STYLE,

B.PRUNTS AS UNITS#,

B.PRDCR AS DATE_STRING,

B.PRTCR AS TIME_STRING,

CONCAT(B.PRDCR,B.PRTCR) AS DATE_TIME_STRING,

C.USER_NAME AS USER_NAME

FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B, CAPM01.WM0272PRDD.USUSER00 C

Notice I have a load on top of a SQL load to do my QlikView transformation... I am not doing this transformation when loading from the qvd

Re: How to convert time in numeric to TIME field in SQL Script?

sunny_talwar — Wed, 18 Jul 2018 17:11:26 GMT

Or look here

https://bytes.com/topic/db2/answers/184829-number-time-conversion

Re: How to convert time in numeric to TIME field in SQL Script?

haneeshmarella — Wed, 18 Jul 2018 17:17:16 GMT

Saw the same exact link but didn't help.

I did the same preceding load method Sunny, it's quick but not as fast, probably now I wonder if it's due to the speed of the DB connection/server. Thank you for your responses though, highly appreciate it.

I tagged you in one other post asking for your help, do you have an idea for that? Link below

How do I create Auto-Timer in Straight Table?

Re: How to convert time in numeric to TIME field in SQL Script?

sunny_talwar — Wed, 18 Jul 2018 17:19:28 GMT

So, you see reload time difference between doing this

SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,

A.PHSHCN AS SHIP_TO_COUNTRY,

A.PHSVIA AS PLANNED_SHIP_VIA,

A.PHTCT AS TOTAL_NO_OF_CARTONS,

B.PRUSER AS PKMS_USERS,

B.PRCASN AS CASE#,

B.PRTLOC AS TO_LOCATION,

B.PRMNOP AS MENU_OPTION_NAME,

B.PRFLOC AS FROM_LOCATION,

B.PRSTYL AS STYLE,

B.PRUNTS AS UNITS#,

B.PRDCR AS DATE_STRING,

B.PRTCR AS TIME_STRING,

CONCAT(B.PRDCR,B.PRTCR) AS DATE_TIME_STRING,

C.USER_NAME AS USER_NAME

FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B, CAPM01.WM0272PRDD.USUSER00 C

LOAD *,

Interval(Time#(Num(TIME_STRING, '000000'), 'hhmmss'), 'hh:mm:ss') as TIME;

SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,

A.PHSHCN AS SHIP_TO_COUNTRY,

A.PHSVIA AS PLANNED_SHIP_VIA,

A.PHTCT AS TOTAL_NO_OF_CARTONS,

B.PRUSER AS PKMS_USERS,

B.PRCASN AS CASE#,

B.PRTLOC AS TO_LOCATION,

B.PRMNOP AS MENU_OPTION_NAME,

B.PRFLOC AS FROM_LOCATION,

B.PRSTYL AS STYLE,

B.PRUNTS AS UNITS#,

B.PRDCR AS DATE_STRING,

B.PRTCR AS TIME_STRING,

CONCAT(B.PRDCR,B.PRTCR) AS DATE_TIME_STRING,

C.USER_NAME AS USER_NAME

FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B, CAPM01.WM0272PRDD.USUSER00 C

How many rows are you pulling? and what is the time difference for the number of rows you just mentioned?

Re: How to convert time in numeric to TIME field in SQL Script?

sunny_talwar — Wed, 18 Jul 2018 17:20:14 GMT

I tagged you in one other post asking for your help, do you have an idea for that? Link below

How do I create Auto-Timer in Straight Table?

Will check

Re: How to convert time in numeric to TIME field in SQL Script?

haneeshmarella — Wed, 18 Jul 2018 17:22:32 GMT

It's at least 25,000 rows every few minutes `15-20 minutes.

Probably it's because I used a lot of WHERE conditions and joins, that it is slow?

I see a time difference of about 30 seconds - 1 minute.

Re: How to convert time in numeric to TIME field in SQL Script?

sunny_talwar — Wed, 18 Jul 2018 17:25:07 GMT

So, when pulling 25,000 rows, you are seeing a time difference of 30 seconds to 1 minute between the two reloads?

Re: How to convert time in numeric to TIME field in SQL Script?

haneeshmarella — Wed, 18 Jul 2018 17:34:53 GMT

I mean time difference before and after implementing preceding load. Between the two consecutive reloads, I see about 5-6 minutes.

Re: How to convert time in numeric to TIME field in SQL Script?

sunny_talwar — Wed, 18 Jul 2018 17:39:36 GMT

5-6 minutes difference for 25,000 rows?

Re: How to convert time in numeric to TIME field in SQL Script?

haneeshmarella — Wed, 18 Jul 2018 17:45:37 GMT

No, only to add the new rows into the QVD, which are about 500.

Re: How to convert time in numeric to TIME field in SQL Script?

sunny_talwar — Wed, 18 Jul 2018 18:05:01 GMT

So, when the SQL returns just the 500 rows... the second query is approx 5 minutes slower than the first query? That doesn't seem right to me... something else is not right.... Would you be able to share the log files for both reloads?

Re: How to convert time in numeric to TIME field in SQL Script?

haneeshmarella — Wed, 18 Jul 2018 18:15:54 GMT

I have made certain changes. I have attached the query. Can you please check the query to see if I am doing it right?

I am doing preceding load, and then doing the incremental load and replacing the original QVD with the incremental load concatenating the original one.

I will send you the log files once I run it again.

Re: How to convert time in numeric to TIME field in SQL Script?

haneeshmarella — Wed, 18 Jul 2018 18:18:51 GMT

Now takes about 1 minute to do the reload. Attached is the log file.

Re: How to convert time in numeric to TIME field in SQL Script?

sunny_talwar — Wed, 18 Jul 2018 18:20:44 GMT

Made couple of changes... try this

Re: How to convert time in numeric to TIME field in SQL Script?

sunny_talwar — Wed, 18 Jul 2018 18:22:05 GMT

And without Preceding load?