Solved

Operation between hexadecimal data Oracle

Posted on 2014-04-22
9
472 Views
Last Modified: 2014-04-30
HI experts, I need resolve the next situation:

I have the next twelve rows in a table A_5MIN_TST1 (the data to be compared are hexa, but Examples works with decimal values):
UTCTIME|TLQ_INST
01/08/2013 01:05:00 a.m.|32
01/08/2013 01:10:00 a.m.|128
01/08/2013 01:15:00 a.m.|8
01/08/2013 01:20:00 a.m.|32
01/08/2013 01:25:00 a.m.|1
01/08/2013 01:30:00 a.m.|10
01/08/2013 01:35:00 a.m.|100
01/08/2013 01:40:00 a.m.|1000
01/08/2013 01:45:00 a.m.|2000
01/08/2013 01:50:00 a.m.|3000
01/08/2013 01:55:00 a.m.|4000

Doing a select I must analyze each bit of the tlq_inst column (hexadecimal data) and to decide:
if some value of tlq_inst is
= 8
or
= 32
or
= 128
then write = 8
when tlq_inst doesn't is 8, 32, 128 then write the first value of tlq_inst, over the range.
I have tried with this query:
SELECT DECODE(POWER(2,BITAND(tlq_inst, 168)), 1, 'OK','Q') salida
FROM A_5MIN_TST1
WHERE utctime >= TO_DATE ('01/08/2013 01:00:01','dd/mm/yyyy hh24:mi:ss')
AND utctime < TO_DATE ('01/08/2013 02:00:00','dd/mm/yyyy hh24:mi:ss')
AND POINTNUMBER = 330062
;

Open in new window

And I saw:
SALIDA
Q
Q
Q
Q
OK
Q
Q
Q
Q
Q
Q
Q

Open in new window

Resuming, on these 12 values, I need to do:

Get 'Q' if the comparison condition with mask, is met.

Get the first value of tlq_inst, when the comparison with the mask, is NOT true.

If possible, do the same but inside where

With this query I managed to get 12 values, but I need to get only one.

Could you help me to resolve this problem?

Thanks all in advanced
Regards
0
Comment
Question by:carlino70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 40020776
do select TOP 1
0
 

Author Comment

by:carlino70
ID: 40020837
the sentence "select top" does not exist in Oracle.
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 40021019
sorry, forgot about that.

If you want just a first selected row you can:

select fname from MyTbl where rownum = 1
you can also use analytic functions to order and take the top x

select max(fname) over (rank() order by some_factor) from MyTbl
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40021364
With this query I managed to get 12 values, but I need to get only one.
So you're supposed to get just one row as result?! What exactly do you expect as result?!

@esskayb2d: Don't get me wrong + I really don't want to be too offensice, BUT please -if you do not exactly know what the asker wants AND obviously do not have or just have half knowledge about Oracle (RDBMS), please stick to your topics, where you are truly powerful (and I mean this since I viewed your profile + your answered threads)...
If you want just a first selected row you can:

select fname from MyTbl where rownum = 1

Yes, you'd get just one row. But within a table holding billions of rows, which row is returned??!! Without an "order by" (e.g.) this is totally undetermined!

you can also use analytic functions to order and take the top x

select max(fname) over (rank() order by some_factor) from MyTbl
Sorry, but this is far too vague ;-)
0
 

Author Comment

by:carlino70
ID: 40022359
"   WHERE utctime >= TO_DATE ('01/08/2013 01:00:01','dd/mm/yyyy hh24:mi:ss')
AND utctime < TO_DATE ('01/08/2013 02:00:00','dd/mm/yyyy hh24:mi:ss')  "

Open in new window


I have 12 rows.

If there is any value is in mask, then I must write :  'Q' (in hexa: 0000000000000008, in decimal: 8)

If any value doesn't is in mask, then I must write the first value of "tlq_inst"

Both cases, I need just 1 value

note: I'm using 'Q' and 'OK', just for testing
0
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 500 total points
ID: 40022496
I suppose you have to use analytic window functions for this, like:
select DECODE(POWER(2, BITAND(tlq_inst, 168)), 1, 'OK', 'Q') salida,
       case DECODE(POWER(2, BITAND(tlq_inst, 168)), 1, 'OK', 'Q')
        when
        'OK' then to_char(first_value(tlq_inst) ignore nulls over (partition by null order by null))
        else
        'Q'
       end testit,
       A_5MIN_TST1.*
  from A_5MIN_TST1
 where utctime >= TO_DATE('01/08/2013 01:00:00', 'dd/mm/yyyy hh24:mi:ss')
   and utctime < TO_DATE('01/08/2013 02:00:00', 'dd/mm/yyyy hh24:mi:ss');

Open in new window


Whereas you should adjust the "partition by" and "order by" within the window function to suit your needs/table specs...

As you might have seen, I also changed your "where utctime >= TO_DATE('01/08/2013 01:00:01', 'dd/mm/yyyy hh24:mi:ss')" to "where utctime >= TO_DATE('01/08/2013 01:00:00', 'dd/mm/yyyy hh24:mi:ss')": don't miss any data ;-)
0
 

Author Comment

by:carlino70
ID: 40022510
Good, I'll try this.

Thanks for the recomendation, I going put the time in correct way
0
 

Author Comment

by:carlino70
ID: 40032223
With a little change, the solution was:
select 
  case when max(bitand(tlq_inst, 168)) = 0 then 
    max(tlq_inst) keep (dense_rank first order by utctime)
  else
    8
  end as result
from a_5min_tst1
where utctime >= to_date ('01/08/2013 01:00:01','dd/mm/yyyy hh24:mi:ss')
and utctime < to_date ('01/08/2013 02:00:00','dd/mm/yyyy hh24:mi:ss')
and pointnumber = 330062;

Open in new window

Thanks  Alexander Eßer for your help, was very important.
Regards
0
 

Author Closing Comment

by:carlino70
ID: 40032225
Excellent!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SYS password changed. Now can't log in as SYS 27 62
oracle collections 2 42
Migration from SQL server to oracle (XML input) 4 72
Automate an Oracle update in Excel 7 70
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question