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:
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:
And I saw:
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
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_INST01/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
-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
0