Quote on numeric field in DB2???

[avihaimar]

Hey,

I am building a dynamic query.
i create a select on some table fields and i append the where clause like this - ... Column_A = 'value' AND COLUMN_B = 'value' ...

i dont know the table and the columns types from ahead, so i wrap the value with single quote.

Almost all Data Base support automatic casting and even if COLUMN_A is a numeric type i can wrap the value with single quote.

I have problem with DB2 that give an error in case of numeric column.

Does any one know a solution for this?
My client is in Java over JDBC connection.

Thank you

 

[SkipVought]

Hi,

If your column is a numeric data type (note that there can be columns of data that appear to be all numbers, but they are text)

Where A_Numeric_Column = nValue
  And A_Character_Column = 'sValue'

Skip,

_[red][/red]

 

[avihaimar]

This is the problem!
i dont know the column type . i just append column name with value.
in most data bases its ok to wrap in singe quote value of numeric type, but DB2 dosnt allow it.

 

[SkipVought]

"...but DB2 dosnt allow it."

Then don't do it!

Skip,

_[red][/red]

 

[avihaimar]

I am looking for a generic solution.
Somthing that will support in all variables (without know their type) and for all data base.



Please, if you dont know an answer dont say - so dont do this.

let other people that know some tricks to help!

 

[MarcLodge]

Avihaimar,

The onyl way I can think of to do this is to put a CHAR round the left hand side of your where clause. This will convert any numeric value to a char field before DB2 considers the other side of the clause, so making a quote acceptable. e.g.

WHERE CHAR(COLUMN_A) = 'value1'

Hope this helps.

Marc

 

[SkipVought]

I've got the same challenge, (not knowing the explicit datatype) and I have never had a problem discovering what I need to do to manipulate data in SQL.

I suppose that converting each criteria field to CHAR is an option, but it is one I find senselessly time consuming and inefficient.

Just be very careful when you are converting Date/Time values to stings.

Skip,

_[red][/red]

 

[MarcLodge]

Skip,

I'll agree that it's not terribly efficient, and that care most certainly will need to be taken with the different formats of dates, integers, timestamps etc. but within the confines of the problem set by Avihaimar, I can't think of another solution.

Anybody else got anything that gets round this problem?

Marc

 

[SkipVought]

I guess an explaination of the business case for this requirement, would have been helpful.

I can't understand why you would use this generic approch, and THEN, "...care most certainly will need to be taken with the different formats of dates, integers, timestamps etc."

Don't you have to know something about the TYPE of data in order to apply some level of "care?"

So back to the business case -- WHY?

Skip,

_[red][/red]

 

[MarcLodge]

Skip,
When I said that care should be taken with dates, times, integers etc. I was thinking that CHAR would fill integer fields with leading zeroes, so care would need to be taken when building the WHERE clause to ensure these were not dropped. This was an issue outside of the TYPE of field but related to the LENGTH of the field.

I should have checked rather than rely on memory as CHAR doesn't do that at all. I've run a query and the CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP and INTEGER fields are all completely unaffected by CHAR. I would there say that your statement "Just be very careful when you are converting Date/Time values to stings." is incorrect.

I've also run a costing on the difference between SELECT CURRENT_DATE and SELECT CHAR(CURRENT_DATE) and can see no difference. I'm not sure therefore if I agree with your statement that adding CHAR is "senselessly time consuming and inefficient".

It's possible that knowing the business case might help, but Avihaimar described in reasonable detail what the technical problem was. Giving him some sort of answer that meets that problem has surely got to be better than saying "don't do it".

Avihaimar, try the CHAR approach and see if this helps.

Marc

 

[avihaimar]

Thank you guys.

The business case:
I have table that contain chages that where done on tables.

So i have :
table_name,column_name,column_value

once in a while there is a thread that extract all this changes.
it prepare the following sql - for each table:
select * from table_name where column_name = column_value and column_name = column_value....

what about using in PrepareStatment and setObject?
i understand that setObject is a very expensive, is it true?

Thank you

 

[SkipVought]

Hmmmmmmm?

I just did a query with a numeric criteria and got incorrect results...

HERE CHAR(ACTQTY)>='50'

does not give correct results. Uses STRING comparison rather than NUMERIC comparison.

Skip,

_[red][/red]

 

[avihaimar]

i try this:
Select * FROM WCSADM.CATENTRY WHERE CHAR(CATENTRY_ID)='1000000';
and it work for me.

what about performance? what about other DB? what about timestamp?

what you think about using in PrepareStatement (and using setObject )

Thank you,
You are great

 

[SkipVought]

Well a ID is not really a NUMBER that you woule do arithmetic on.

Try a criteria that uses GT or LT a numeric value and see ehat happens.

Skip,

_[red][/red]

 

[avihaimar]

Thank you, but all i need is only to extract those records.

 

[SkipVought]

Well that narrows down the requirement a WHOLE LOT.

I though this was a generic thing, but it s a generic thing with a caveat, so it is not so generic. ;-)

Skip,

_[red][/red]

 

[avihaimar]

You are funny.

So, what is your suggetion?

 

[SkipVought]

CHAR seems to work for the EQUALS operator ONLY.

Skip,

_[red][/red]

 

[Truusvlugindewind]

Why not cast on the other side of the "="? This technique enables you to ensure a matching column definition. e.g.:

WHERE   int-col = INT('value1')
  AND  date-col = DATE('value2')
  AND  doub-col = DOUBLE('value3')

This gives best chance of proper index utilization, especially for DB2 versions prior to V8.

 

[BrianTyler]

Your only sure solution is to extract information from SYSCAT.COLUMNS and to use that information to parse the field.

If you have no access to the system tables, get your DBA to produce an extract for you to use.

Brian