SQL - Operation between hexadecimal data Oracle

   
Vista:

Operation between hexadecimal data Oracle

Publicado por carlos (1 intervención) el 22/04/2014 19:46:01
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):
Código:

1
2
3
4
5
6
7
8
9
10
11
12
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:
Código:
1
2
3
4
5
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;

And I saw:
1
2
3
4
5
6
7
8
9
10
11
12
13
SALIDA
Q
Q
Q
Q
OK
Q
Q
Q
Q
Q
Q
Q
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


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
CREATE TABLE A_5MIN_TST1
(
  UTCTIME      DATE                             NOT NULL,
  POINTNUMBER  INTEGER                          NOT NULL,
  SITEID       INTEGER,
  VALOR_INST   FLOAT(126),
  TLQ_INST     INTEGER,
  VALOR_PROM   FLOAT(126),
  TLQ_PROM     INTEGER,
  VALOR_MAX    FLOAT(126),
  TLQ_MAX      INTEGER,
  UTCTIME_MAX  DATE,
  VALOR_MIN    FLOAT(126),
  TLQ_MIN      INTEGER,
  UTCTIME_MIN  DATE
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
 
 
ALTER TABLE A_5MIN_TST1 ADD (
  PRIMARY KEY
 (UTCTIME, POINTNUMBER)
    USING INDEX
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));
 
 
SET DEFINE OFF;
Insert into A_5MIN_TST1
   (UTCTIME, TLQ_INST)
 Values
   (TO_DATE('08/01/2013 01:05:00', 'MM/DD/YYYY HH24:MI:SS'), 32);
Insert into A_5MIN_TST1
   (UTCTIME, TLQ_INST)
 Values
   (TO_DATE('08/01/2013 01:10:00', 'MM/DD/YYYY HH24:MI:SS'), 128);
Insert into A_5MIN_TST1
   (UTCTIME, TLQ_INST)
 Values
   (TO_DATE('08/01/2013 01:15:00', 'MM/DD/YYYY HH24:MI:SS'), 8);
Insert into A_5MIN_TST1
   (UTCTIME, TLQ_INST)
 Values
   (TO_DATE('08/01/2013 01:20:00', 'MM/DD/YYYY HH24:MI:SS'), 32);
Insert into A_5MIN_TST1
   (UTCTIME, TLQ_INST)
 Values
   (TO_DATE('08/01/2013 01:25:00', 'MM/DD/YYYY HH24:MI:SS'), 1);
Insert into A_5MIN_TST1
   (UTCTIME, TLQ_INST)
 Values
   (TO_DATE('08/01/2013 01:30:00', 'MM/DD/YYYY HH24:MI:SS'), 10);
Insert into A_5MIN_TST1
   (UTCTIME, TLQ_INST)
 Values
   (TO_DATE('08/01/2013 01:35:00', 'MM/DD/YYYY HH24:MI:SS'), 100);
Insert into A_5MIN_TST1
   (UTCTIME, TLQ_INST)
 Values
   (TO_DATE('08/01/2013 01:40:00', 'MM/DD/YYYY HH24:MI:SS'), 1000);
Insert into A_5MIN_TST1
   (UTCTIME, TLQ_INST)
 Values
   (TO_DATE('08/01/2013 01:45:00', 'MM/DD/YYYY HH24:MI:SS'), 2000);
Insert into A_5MIN_TST1
   (UTCTIME, TLQ_INST)
 Values
   (TO_DATE('08/01/2013 01:50:00', 'MM/DD/YYYY HH24:MI:SS'), 3000);
Insert into A_5MIN_TST1
   (UTCTIME, TLQ_INST)
 Values
   (TO_DATE('08/01/2013 01:55:00', 'MM/DD/YYYY HH24:MI:SS'), 4000);
COMMIT;
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder