The developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its apps will no longer be available. More details available on our FAQ.
Topic
• 5 replies
• Latest Post - ‏2019-04-10T08:28:29Z by CGRIERE
CGRIERE
41 Posts

# Pinned topic Nominal frequency of a Power

‏2019-04-05T10:19:49Z |

Hi,

The nominal frequency of a Power is stored in binary in the first four bytes of gdes column  [binchar(10) ?]  of QAPMCONF file with gkey='NF'.

I use this SQL request to get this frequency :

select gdes , substr(gdes, 1, 4) as freq from qpfrdata.qapmconf where gkey='NF' ;

I get:

GDES                                 FREQ

 00000BD3404040404040 00000BD3

I tried many SQL functions to apply to FREQ column but could not find one to get this frequency in human view (decimal) in ACS.

Any tip will be appreciated.

Regards.

Updated on 2019-04-05T10:27:48Z at 2019-04-05T10:27:48Z by CGRIERE
• Jordi.Hoffmann
33 Posts

#### Re: Nominal frequency of a Power

‏2019-04-08T07:56:22Z

Hello,

I have not found a built-in function either, but you could do it yourself:

with data (s) as (values ('00000BD3')),
alph (a) as (values ('0123456789ABCDEF'))
select sum(
bigint(
int(locate(substr(s, n, 1),a) - 1) *
power(16, 8 - n)
)
)
from   data
cross  join alph
cross  join (values (1),(2),(3),(4),(5),(6),(7),(8)) x (n)
union  all
select bigint(
3 * power( 16, 0) + 13 * power(16, 1) + 11 * power(16, 2)
)
from sysibm/sysdummy1

==>

SUM
3.027
3.027

Best regards

Jordi

• Jordi.Hoffmann
33 Posts

#### Re: Nominal frequency of a Power

‏2019-04-08T07:56:22Z

Hello,

I have not found a built-in function either, but you could do it yourself:

with data (s) as (values ('00000BD3')),
alph (a) as (values ('0123456789ABCDEF'))
select sum(
bigint(
int(locate(substr(s, n, 1),a) - 1) *
power(16, 8 - n)
)
)
from   data
cross  join alph
cross  join (values (1),(2),(3),(4),(5),(6),(7),(8)) x (n)
union  all
select bigint(
3 * power( 16, 0) + 13 * power(16, 1) + 11 * power(16, 2)
)
from sysibm/sysdummy1

==>

SUM
3.027
3.027

Best regards

Jordi

• CGRIERE
41 Posts

#### Re: Nominal frequency of a Power

‏2019-04-09T08:18:28Z

Hello,

I have not found a built-in function either, but you could do it yourself:

with data (s) as (values ('00000BD3')),
alph (a) as (values ('0123456789ABCDEF'))
select sum(
bigint(
int(locate(substr(s, n, 1),a) - 1) *
power(16, 8 - n)
)
)
from   data
cross  join alph
cross  join (values (1),(2),(3),(4),(5),(6),(7),(8)) x (n)
union  all
select bigint(
3 * power( 16, 0) + 13 * power(16, 1) + 11 * power(16, 2)
)
from sysibm/sysdummy1

==>

SUM
3.027
3.027

Best regards

Jordi

Hi Jordi,

Thanks for this sophisticated answer that should give you a lot of work!

It is hard for me to think there is no simpler solution but I trust you because I did not succeed.

Regards

• CGRIERE
41 Posts

#### Re: Nominal frequency of a Power

‏2019-04-09T17:11:05Z
• CGRIERE
• ‏2019-04-09T08:18:28Z

Hi Jordi,

Thanks for this sophisticated answer that should give you a lot of work!

It is hard for me to think there is no simpler solution but I trust you because I did not succeed.

Regards

Hi Jordi,

When I replace values ('00000BD3') by select substr(gdes, 1, 4) from qpfrdata.qapmconf where gkey='NF'

I get an errror in parameter 1 of the locate function.

I will use a trivial RPG program to do that.

Thanks.

Regards.

Updated on 2019-04-09T17:13:39Z at 2019-04-09T17:13:39Z by CGRIERE
• Jordi.Hoffmann
33 Posts

#### Re: Nominal frequency of a Power

‏2019-04-10T08:20:10Z
• CGRIERE
• ‏2019-04-09T17:11:05Z

Hi Jordi,

When I replace values ('00000BD3') by select substr(gdes, 1, 4) from qpfrdata.qapmconf where gkey='NF'

I get an errror in parameter 1 of the locate function.

I will use a trivial RPG program to do that.

Thanks.

Regards.

Hello,

please try  select hex(substr(gdes, 1, 4))...

Best regards

Jordi

Updated on 2019-04-10T08:20:52Z at 2019-04-10T08:20:52Z by Jordi.Hoffmann
• CGRIERE
41 Posts

#### Re: Nominal frequency of a Power

‏2019-04-10T08:28:29Z

Hi Jordi,

That works fine.

Thanks.

Best regards.