Use IF Statement to check data in SQL String

Question

I have a Oracle connection string with SQL Statement:

OracleCommand cmd = new OracleCommand("SELECT * FROM Database WHERE IPADDRESS='10.00.000.000' ORDER BY DATETIME ASC", con);

After loading all data in a dataset I check the bit of the row "RAWOUTPUT1" and write it in the correct column:

int bit = Convert.ToInt32(dr[22]);

dr[24] = (bit & (1 << 0)) != 0;
dr[25] = (bit & (1 << 1)) != 0;
dr[26] = (bit & (1 << 2)) != 0;
dr[27] = (bit & (1 << 3)) != 0;
dr[28] = (bit & (1 << 4)) != 0;
dr[29] = (bit & (1 << 5)) != 0;
dr[30] = (bit & (1 << 6)) != 0;
dr[31] = (bit & (1 << 7)) != 0;
dr[32] = (bit & (1 << 8)) != 0;
dr[33] = (bit & (1 << 9)) != 0;

My problem now is that the data is too big and I get a memory exception. So my idea is to load only the data from the database that I need, something like this:

OracleCommand cmd = new OracleCommand("SELECT * FROM Database WHERE IPADDRESS='10.00.000.000' 
AND IF((RAWOUTPUT1 & (1 << 0)) != 0) //here I need help, example for check bit
ORDER BY DATETIME ASC", con);

Thanks for the help!

elite · Accepted Answer · 2014-12-15 09:39:20Z

2

Try to use the oracle function BITAND https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions014.htm#SQLRF00612

answered Dec 15, 2014 at 9:39

elite

1911 gold badge2 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thorsten Kettner · Accepted Answer · 2014-12-15 16:21:15Z

1

What are you looking for? All records, where at least one bit is set in RAWOUTPUT1?

AND RAWOUTPUT1 <> 0

Or all records where all given 10 bits are set?

AND RAWOUTPUT1 = 1023

Or where at least one of the ten bits is set?

AND BITAND(RAWOUTPUT1, 1023) > 0

EDIT: If you are looking for a certain bit, then bitand with the according power of two:

AND BITAND( RAWOUTPUT1, POWER(2,:n-1) ) <> 0

edited Dec 15, 2014 at 16:21

answered Dec 15, 2014 at 9:44

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

3 Comments

Kᴀτᴢ Over a year ago

I have to check which bit is set in Column "ROWOUTPUT1". Possible is e.g. 0001 - 1111. So I have to check if bit 1 is set (0001, 0011, 0111, 1111) and load only that data

Thorsten Kettner Over a year ago

This is AND BITAND(RAWOUTPUT1, 1) <> 0.

Kᴀτᴢ Over a year ago

That's it! AND BITAND(RAWOUTPUT1, POWER(2,n-1) <> 0

Alberto Solano · Accepted Answer · 2014-12-15 17:58:13Z

1

I don't have much experience with Oracle database, but I think the IF condition cannot be used in that way in the WHERE clause. Filter your rows setting your condition as something like:

AND BITAND(RAWOUTPUT1, 1) <> 0

edited Dec 15, 2014 at 17:58

answered Dec 15, 2014 at 9:53

Alberto Solano

8,2653 gold badges40 silver badges62 bronze badges

3 Comments

Thorsten Kettner Over a year ago

That is surprising. When was the bitshift operator added to Oracle SQL? It doesn't work in 11g.

Alberto Solano Over a year ago

@ThorstenKettner Sorry, you're right. Oracle supports bitshift in this way NumberA / power(2, NumberB). The line should be then AND BITAND(RAWOUTPUT1, POWER(2,n-1) <> 0

Kᴀτᴢ Over a year ago

That's it! AND BITAND(RAWOUTPUT1, POWER(2,n-1) <> 0

Collectives™ on Stack Overflow

Use IF Statement to check data in SQL String

3 Answers 3

Comments

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related