simple select query not working

hi i have a query in cf and want to run another very simple query on it and for some reason it is not getting the data that i know is in there: first i am running this query (i am using query of query for these queries): select qu_80_tb_89_split_0_split_T_Wt from mergedColumnsQuery where subjectID = 446 and projectID = 36 and testOccasionID = 2 and qu_80_tb_89_split_0_split_T_Ht is null and qu_82_tb_89_split_0_split_T_Ht = 147.0; and it is returning a value that i know that is in there. but then when i run the following statement which is identical to the one above except a couple of changes in the where clause it doesnt produce any data although i have checked the data many times and it should definately return a result: select qu_80_tb_89_split_0_split_T_Wt from mergedColumnsQuery where subjectID = 447 and projectID = 36 and testOccasionID = 2 and qu_80_tb_89_split_0_split_T_Ht is null and qu_82_tb_89_split_0_split_T_Ht = 142.2; thanks for any suggestions richard

Hmmm... if it is a query of query, then you are limited to what the parent query (sorry, don't really know what the first query from DB is called...) has.  So, even if a row of data does exist in a table, if that row of data is not pulled by the parent query, then there is no way how you can get it through any query of query based on the parent query. Now, if you look at the query result of the parent query, is the desired row of data in it? ----- Excess quoted text cut - see Original Post for more -----

yes i have dumped out the query named mergedColumnsQuery just before i run the cfquery select statement on that query and it does contain a row with the data that i am trying to select would it have anything to do with column types? thanks ----- Excess quoted text cut - see Original Post for more -----

Richard White wrote: > yes i have dumped out the query named mergedColumnsQuery just before i run the cfquery select statement on that query and it does contain a row with the data that i am trying to select > > would it have anything to do with column types? > > thanks Well the first thought I had was can one do "NULL" in a query of query?   I wonder because CF does not normally grock 'NULL' and null columns from a database are 'empty strings' in ColdFusion record sets. You might try changing your 'IS NULL' comparisons to = ''.  (equal empty string) comparisons. But why the first works with IS NULL and the second doesn't, I do not understand.

Hi Ian, yes i did start off comparing with empty strings - like where xx = "" but i wont always know the data type of the column and therefore i get an error if the column data type is an integer for example, so i thought that by adding 'is null' will then work for all column types. if anyone sees a better way of doing this and that this may be unreliable i will appreciate it, as i dont know how else to compare an integer column with no data? thanks ----- Excess quoted text cut - see Original Post for more -----

Try: LEN(RTRIM(LTRIM(my_integer_column))) = 0 ----- Excess quoted text cut - see Original Post for more -----

>Try: LEN(RTRIM(LTRIM(my_integer_column))) = 0 thanks ill use this

Hmm... interesting.  I thought about data types, but if a column is varchar and you are not enclosing the data for it in single quotes, the query should still return the rows. For example, if you did SELECT * FROM myTable WHERE myRow = 1 and myRow is actually a VARCHAR column, all rows of myRow = 1 should still be returned. Probably not the best practice, but should work... Not sure how it behaves though if the value is a decimal stored in varchar and you are not enclosing it in single quotes.  Might be worth a try. Here's a strategy: comment out  condition in your WHERE clause one at a time.  This way you can see the exclusion of which condition gives you the desired the result, then you can use that as a clue ----- Excess quoted text cut - see Original Post for more -----

thanks Qing, getting there now!!! after what you said i commented them out one by one and it only fails when it gets to the last column which is: and qu_82_tb_89_split_0_split_T_Ht = 142.2; so i then used a function i got from cflibs called queryColumnTypes and found that the column is a 'Real' data type. would this have something to do with it, i cannot work out why this is Real instead of a double or decimal - it is a float in the database when i first queried it, so does cf convert them to a 'real' and do you think this is the cause thanks ----- Excess quoted text cut - see Original Post for more -----

Wow.  That is wild! I have never experienced where a column is one data type in a SQL server table and then changed to something else after CF pulls it... (Except for NULL of course, there is no NULL in CF) Beats me! I can't imagine why this is happening... But, just for kicks... change that troublesome condition to: and qu_82_tb_89_split_0_split_T_Ht = 142.20 and see if it gets you anything. ----- Excess quoted text cut - see Original Post for more -----

thanks for your help anyway Qing, i cannot work this out either, i cannot see why the first one would work but not the second thanks ----- Excess quoted text cut - see Original Post for more -----

>dont know if it may apply but to get that column i did a left join with another query and then from that query i did a union with another query so i will backtrack through all of these to see at what point it is changed and see where i can fix it thanks

Speaking of left outer joins:  when you have a left outer join, and then you put a condition for the right-hand-side table in the WHERE clause, then that turns the LEFT OUTER JOIN into an INNER JOIN. For exmaple, the following two queries will return the same result: select  ta.ta_id,        ta.columnA1,         ta.columnA2,        tb.tb_id,         tb.columnB1,         tb.columnB2 from tableA ta left outer join tableB tb   on ta.ta_id = tb.tb_id where tb.columnB1 = 100 select  ta.ta_id,        ta.columnA1,         ta.columnA2,        tb.tb_id,         tb.columnB1,         tb.columnB2 from tableA ta inner join tableB tb   on ta.ta_id = tb.tb_id where tb.columnB1 = 100 So you might want to check whether a left join clause could have been accidentally placed in the WHERE clause. ----- Excess quoted text cut - see Original Post for more -----

i tried changing all the columns to data types of varchar and then to query this data by surrounding the where clause data with '' and this now produces an error saying: Error casting an object of type  to an incompatible type. This usually indicates a programming error in Java, although it could also mean you have tried to use a foreign object in a different way than it was designed. can anyone suggest another way round this? thanks ----- Excess quoted text cut - see Original Post for more -----

thanks for all your help ben nadal managed to identify the problem as being the column types and using the following casting worked fine: CAST(qu_82_tb_89_split_0_split_T_Ht AS DECIMAL) = CAST(142.2 AS DECIMAL)

Are you sure the qu_80_tb_89_split_0_split_T_Ht column is really NULL, and not just an empty string? ~Brad yes i have dumped out the query named mergedColumnsQuery just before i run the cfquery select statement on that query and it does contain a row with the data that i am trying to select would it have anything to do with column types?

hi brad, even if i leave out that column and just add the select statement: select qu_80_tb_89_split_0_split_T_Wt from mergedColumnsQuery where subjectID = 447 and projectID = 36 and testOccasionID = 2 and qu_82_tb_89_split_0_split_T_Ht = 142.2; it still doesnt find the row - and just before i run the statement i have dumped the query i am selecting from and there is a row with those items in. i have never seen anything like this before!!!

I'm going to go with Qing Xia's suggestion here. Start with nothing but select qu_80_tb_89_split_0_split_T_Wt from mergedColumnsQuery and add the where pieces back in one at a time until the record goes away. ~Brad hi brad, even if i leave out that column and just add the select statement: select qu_80_tb_89_split_0_split_T_Wt from mergedColumnsQuery where subjectID = 447 and projectID = 36 and testOccasionID = 2 and qu_82_tb_89_split_0_split_T_Ht = 142.2; it still doesnt find the row - and just before i run the statement i have dumped the query i am selecting from and there is a row with those items in. i have never seen anything like this before!!!