IFNULL in QoQ

Can you use the IFNULL statement is QoQ: i.e. SELECT IFNULL( SUM( awardPoints - pointsRedeemed ) , 0) AS Points FROM   qryTmp.memberPoints.orderedMemberData i get this errro: Error Executing Database Query. Query Of Queries syntax error. Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct. TIA -- Nick Tong web:         http://talkwebsolutions.co.uk blog:         http://succor.co.uk short urls: http://wapurl.co.uk linkedin:    http://linkedin.com/pub/0/a70/502

Nick, As far as I know, ColdFusion queries do not have NULL values. NULLs come over from the SQL server as empty string. What you should probably do is make sure that awardPoints and pointsRedeemed are NEVER null when coming back form the DB, that way, you won't have to worry about the SUM on the ColdFusion end: SELECT   (     ISNULL( awardPoints, 0 )   ) AS awardPoints,   (     ISNULL( pointsRedeemed, 0 )   ) AS pointsRedeemed, FROM   [table] ...................... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 x 14 212.691.3477 fax www.nylontechnology.com "Some people call me the space cowboy. Some people call me the gangster of love." Can you use the IFNULL statement is QoQ: i.e. SELECT IFNULL( SUM( awardPoints - pointsRedeemed ) , 0) AS Points FROM   qryTmp.memberPoints.orderedMemberData i get this errro: Error Executing Database Query. Query Of Queries syntax error. Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct. TIA -- Nick Tong web:         http://talkwebsolutions.co.uk blog:         http://succor.co.uk short urls: http://wapurl.co.uk linkedin:    http://linkedin.com/pub/0/a70/502

I don't think that's the case. They remain as NULLs until they are used. Could be wrong though. Adrian Nick, As far as I know, ColdFusion queries do not have NULL values. NULLs come over from the SQL server as empty string. What you should probably do is make sure that awardPoints and pointsRedeemed are NEVER null when coming back form the DB, that way, you won't have to worry about the SUM on the ColdFusion end: SELECT   (     ISNULL( awardPoints, 0 )   ) AS awardPoints,   (     ISNULL( pointsRedeemed, 0 )   ) AS pointsRedeemed, FROM   [table] ....................... Ben Nadel Can you use the IFNULL statement is QoQ: i.e. SELECT IFNULL( SUM( awardPoints - pointsRedeemed ) , 0) AS Points FROM   qryTmp.memberPoints.orderedMemberData i get this errro: Error Executing Database Query. Query Of Queries syntax error. Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct. TIA -- Nick Tong

> I don't think that's the case. They remain as NULLs until they are used. > > Could be wrong though. > > Adrian > I just did a quick test of this, and they are still considered NULL (this is MX7), and IS (NOT) NULL is supported, although COALESCE doesn't seem to be.  Still, maybe you could do something like the following: <cfquery name="q1" datasource="somedsn"> select col1,col2 FROM sometable </cfquery> <cfquery name="q2" dbtype="query"> select '0',col2 FROM q1 where col1 IS NULL UNION select col1,col2 FROM q1 where col1 IS NOT NULL </cfquery> -- Jim Wright Wright Business Solutions wrightjim@gmail.com 919-417-2257

What i want to do is return a zero if the value is null... grrrr i guess i'll have to use CF to work on this.. So other odeties that i've had with QoQ are if i try and run QoQs in a CFC i can't access thoise values until the value is ruturned from the CFC i.e. Page A calls CFC B, CFC B calls other functions to return the data back to the method in CFC B. I then try and calculate some of the values in CFC B to pupulate a struct to return to page A.  The problem is that I can't as the values from the QoQ arn't populated. If i do a dump of the struct (which get populated with the qry and any other data) I get a black recordset for the QoQ, but if i do a dump for the value returned from CFC B in Page A the recordset if populated ?? Does that make sense? ----- Excess quoted text cut - see Original Post for more -----

Make sure your query name is a local variable... It sounds like something might be overwriting it???: var qData = ""; <cfquery name="qData" .....></cfquery> <cfreturn qData /> This way qData is LOCAL to the function and it won't get mixed up with anyone elses values. ...................... Ben Nadel www.bennadel.com What i want to do is return a zero if the value is null... grrrr i guess i'll have to use CF to work on this.. So other odeties that i've had with QoQ are if i try and run QoQs in a CFC i can't access thoise values until the value is ruturned from the CFC i.e. Page A calls CFC B, CFC B calls other functions to return the data back to the method in CFC B. I then try and calculate some of the values in CFC B to pupulate a struct to return to page A.  The problem is that I can't as the values from the QoQ arn't populated. If i do a dump of the struct (which get populated with the qry and any other data) I get a black recordset for the QoQ, but if i do a dump for the value returned from CFC B in Page A the recordset if populated ?? Does that make sense? ----- Excess quoted text cut - see Original Post for more -----

they are all vared - i've check and double check and then changed names etc to make sure... i have 3 others look at it here as well - but no joy.... ----- Excess quoted text cut - see Original Post for more -----

Nick Tong - TalkWebSolutions.co.uk wrote: > Can you use the IFNULL statement is QoQ: No: http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/using_39.htm Jochem

>> NULLs come over from the SQL server as empty string. Which is incorrect and rightly should be a bug. Nick, As far as I know, ColdFusion queries do not have NULL values. NULLs come over from the SQL server as empty string. What you should probably do is make sure that awardPoints and pointsRedeemed are NEVER null when coming back form the DB, that way, you won't have to worry about the SUM on the ColdFusion end: SELECT   (     ISNULL( awardPoints, 0 )   ) AS awardPoints,   (     ISNULL( pointsRedeemed, 0 )   ) AS pointsRedeemed, FROM   [table] ....................... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 x 14 212.691.3477 fax www.nylontechnology.com "Some people call me the space cowboy. Some people call me the gangster of love." Can you use the IFNULL statement is QoQ: i.e. SELECT IFNULL( SUM( awardPoints - pointsRedeemed ) , 0) AS Points FROM   qryTmp.memberPoints.orderedMemberData i get this errro: Error Executing Database Query. Query Of Queries syntax error. Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct. TIA -- Nick Tong web:         http://talkwebsolutions.co.uk blog:         http://succor.co.uk short urls: http://wapurl.co.uk linkedin:    http://linkedin.com/pub/0/a70/502

Neil, I am not sure this should be bug. ColdFusion doesn't have NULL values anywhere (query or no query). In fact, if you are using Java calls and you have a variable that is set to a Java null value, the ColdFusion variable is no longer defined. In ColdFusion a null destroys variables. For instance, let's say you were setting the variables Foo["bar"] to a value returned by Java... You could loop until NULL this way: While ( StructKeyExists( Foo, "bar" ) ){ Foo["bar"] = JavaFNThatMayReturnNULL( x, y, z ); } If the java method returns a Null, the "bar" key is actually removed. So, all that to say, if queries handled NULLs by value (instead of as empty string) it would be inconsistent with the rest of ColdFusion. ...................... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 x 14 212.691.3477 fax www.nylontechnology.com "Some people call me the space cowboy. Some people call me the gangster of love." [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Friday, June 30, 2006 10:31 AM To: CF-Talk Subject: RE: IFNULL in QoQ >> NULLs come over from the SQL server as empty string. Which is incorrect and rightly should be a bug. Nick, As far as I know, ColdFusion queries do not have NULL values. NULLs come over from the SQL server as empty string. What you should probably do is make sure that awardPoints and pointsRedeemed are NEVER null when coming back form the DB, that way, you won't have to worry about the SUM on the ColdFusion end: SELECT   (     ISNULL( awardPoints, 0 )   ) AS awardPoints,   (     ISNULL( pointsRedeemed, 0 )   ) AS pointsRedeemed, FROM   [table] ........................ Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 x 14 212.691.3477 fax www.nylontechnology.com "Some people call me the space cowboy. Some people call me the gangster of love." Can you use the IFNULL statement is QoQ: i.e. SELECT IFNULL( SUM( awardPoints - pointsRedeemed ) , 0) AS Points FROM   qryTmp.memberPoints.orderedMemberData i get this errro: Error Executing Database Query. Query Of Queries syntax error. Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct. TIA -- Nick Tong web:         http://talkwebsolutions.co.uk blog:         http://succor.co.uk short urls: http://wapurl.co.uk linkedin:    http://linkedin.com/pub/0/a70/502

Well my point was that a NULL is not the same as "no data" and if your table contains NULLs you should be able to eval them as such. Neil, I am not sure this should be bug. ColdFusion doesn't have NULL values anywhere (query or no query). In fact, if you are using Java calls and you have a variable that is set to a Java null value, the ColdFusion variable is no longer defined. In ColdFusion a null destroys variables. For instance, let's say you were setting the variables Foo["bar"] to a value returned by Java... You could loop until NULL this way: While ( StructKeyExists( Foo, "bar" ) ){ Foo["bar"] = JavaFNThatMayReturnNULL( x, y, z ); } If the java method returns a Null, the "bar" key is actually removed. So, all that to say, if queries handled NULLs by value (instead of as empty string) it would be inconsistent with the rest of ColdFusion. ....................... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 x 14 212.691.3477 fax www.nylontechnology.com "Some people call me the space cowboy. Some people call me the gangster of love." [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Friday, June 30, 2006 10:31 AM To: CF-Talk Subject: RE: IFNULL in QoQ >> NULLs come over from the SQL server as empty string. Which is incorrect and rightly should be a bug. Nick, As far as I know, ColdFusion queries do not have NULL values. NULLs come over from the SQL server as empty string. What you should probably do is make sure that awardPoints and pointsRedeemed are NEVER null when coming back form the DB, that way, you won't have to worry about the SUM on the ColdFusion end: SELECT   (     ISNULL( awardPoints, 0 )   ) AS awardPoints,   (     ISNULL( pointsRedeemed, 0 )   ) AS pointsRedeemed, FROM   [table] ......................... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 x 14 212.691.3477 fax www.nylontechnology.com "Some people call me the space cowboy. Some people call me the gangster of love." Can you use the IFNULL statement is QoQ: i.e. SELECT IFNULL( SUM( awardPoints - pointsRedeemed ) , 0) AS Points FROM   qryTmp.memberPoints.orderedMemberData i get this errro: Error Executing Database Query. Query Of Queries syntax error. Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct. TIA -- Nick Tong web:         http://talkwebsolutions.co.uk blog:         http://succor.co.uk short urls: http://wapurl.co.uk linkedin:    http://linkedin.com/pub/0/a70/502

Neil, I understand what you are saying, and from the link the guy below posted, CF says: cond_primary ::= simple_cond | <OPENPAREN> cond_exp <CLOSEPAREN> simple_cond ::= like_cond | null_cond | between_cond | in_cond | comparison_cond null_cond ::= row_constructor <IS> ( <NOT> )? <NULL> This BNF notation makes me think that is should be able to handle an "IS NULL" which would make you think that the value in the Q of Q IS a null value... Interesting. ...................... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 x 14 212.691.3477 fax www.nylontechnology.com "Some people call me the space cowboy. Some people call me the gangster of love." [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Friday, June 30, 2006 10:58 AM To: CF-Talk Subject: RE: IFNULL in QoQ Well my point was that a NULL is not the same as "no data" and if your table contains NULLs you should be able to eval them as such.

Very! Neil, I understand what you are saying, and from the link the guy below posted, CF says: cond_primary ::= simple_cond | <OPENPAREN> cond_exp <CLOSEPAREN> simple_cond ::= like_cond | null_cond | between_cond | in_cond | comparison_cond null_cond ::= row_constructor <IS> ( <NOT> )? <NULL> This BNF notation makes me think that is should be able to handle an "IS NULL" which would make you think that the value in the Q of Q IS a null value... Interesting. ....................... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 x 14 212.691.3477 fax www.nylontechnology.com "Some people call me the space cowboy. Some people call me the gangster of love." [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Friday, June 30, 2006 10:58 AM To: CF-Talk Subject: RE: IFNULL in QoQ Well my point was that a NULL is not the same as "no data" and if your table contains NULLs you should be able to eval them as such.