#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3

    Cfquery driving me crazy


    I'm running the following query for many hours, made all sorts of changes but wondering why the DETAIL Query is not returning any results. I have similar queries with me for other connections as well( FIRSTCONN, SECONDCONN, FOURTHCONN) which are running absolutely fine and displaying desired range of dates in the output. Could anyone figure out what could be the reason?



    Code:
        <cfquery datasource = "XX.XX.X.XX" name="master">
        SELECT date_format(Timedetail,'%m/%d/%y') as THIRDCONN,count(Timedetail) as THIRDOCCURANCES ,EVENTS 
        FROM MyDatabase
        WHERE EVENTS = "THIRD" 
        GROUP BY THIRDCONN;
         </cfquery> 
         
         
          DUMP FOR THIRD MASTER <cfdump var = "#master#"> 
            
         
        
        
        
        <cfquery dbtype="query" name="detail">
        SELECT  *
        FROM master 
        WHERE THIRDCONN  >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_date"> 
        AND THIRDCONN  <  <cfqueryparam value="#form.enddate#" cfsqltype="cf_sql_date">;
        </cfquery>  
        
        
           DUMP FOR THIRD DETAIL <cfdump var = "#detail#">



    P.S. Just as a test, I ran "SELECT * from master" in the detail query and I got the identical output for master and detail queries which was expected but when I mention the dates parameters in the detail query, it stops displaying the output.However, this is not the case with other connection queries (FIRSTCONN, SECONDCONN, FOURTHCONN etc)

    Is there any other way I can run this query or any mistake I have done?











    DUMP FOR THIRD MASTER
    Code:
         	THIRDCONN 	THIRDOCCURANCES 	EVENTS
        1 	06/06/13 	10810 	           THIRD
        2 	06/07/13 	8076 	                 THIRD
        3 	06/10/13 	25043                THIRD
        4 	06/11/13 	24754 	THIRD
        5 	06/12/13 	14587 	THIRD
        6 	06/13/13 	24828 	THIRD
        7 	06/14/13 	23987 	THIRD
        8 	06/15/13 	28027 	THIRD
        9 	06/16/13 	25190 	THIRD
        10 	06/17/13 	27255 	THIRD
        11 	06/18/13 	22227 	THIRD
        12 	06/19/13 	15951 	THIRD
        13 	06/20/13 	21120 	THIRD
        14 	06/21/13 	24214 	THIRD
        15 	06/22/13 	22466 	THIRD
        16 	06/23/13 	19881 	THIRD
        17 	06/24/13 	23479 	THIRD
        18 	06/25/13 	7879 	       THIRD

    DUMP FOR THIRD DETAIL

    Code:
    THIRDCONN 	THIRDOCCURANCES 	EVENTS
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3
    Originally Posted by Jack_Tauson_Sr
    I'm running the following query for many hours, made all sorts of changes but wondering why the DETAIL Query is not returning any results. I have similar queries with me for other connections as well( FIRSTCONN, SECONDCONN, FOURTHCONN) which are running absolutely fine and displaying desired range of dates in the output. Could anyone figure out what could be the reason?



    Code:
        <cfquery datasource = "XX.XX.X.XX" name="master">
        SELECT date_format(Timedetail,'%m/%d/%y') as THIRDCONN,count(Timedetail) as THIRDOCCURANCES ,EVENTS 
        FROM MyDatabase
        WHERE EVENTS = "THIRD" 
        GROUP BY THIRDCONN;
         </cfquery> 
         
         
          DUMP FOR THIRD MASTER <cfdump var = "#master#"> 
            
         
        
        
        
        <cfquery dbtype="query" name="detail">
        SELECT  *
        FROM master 
        WHERE THIRDCONN  >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_date"> 
        AND THIRDCONN  <  <cfqueryparam value="#form.enddate#" cfsqltype="cf_sql_date">;
        </cfquery>  
        
        
           DUMP FOR THIRD DETAIL <cfdump var = "#detail#">



    P.S. Just as a test, I ran "SELECT * from master" in the detail query and I got the identical output for master and detail queries which was expected but when I mention the dates parameters in the detail query, it stops displaying the output.However, this is not the case with other connection queries (FIRSTCONN, SECONDCONN, FOURTHCONN etc)

    Is there any other way I can run this query or any mistake I have done?











    DUMP FOR THIRD MASTER
    Code:
         	THIRDCONN 	THIRDOCCURANCES 	EVENTS
        1 	06/06/13 	10810 	           THIRD
        2 	06/07/13 	8076 	                 THIRD
        3 	06/10/13 	25043                THIRD
        4 	06/11/13 	24754 	THIRD
        5 	06/12/13 	14587 	THIRD
        6 	06/13/13 	24828 	THIRD
        7 	06/14/13 	23987 	THIRD
        8 	06/15/13 	28027 	THIRD
        9 	06/16/13 	25190 	THIRD
        10 	06/17/13 	27255 	THIRD
        11 	06/18/13 	22227 	THIRD
        12 	06/19/13 	15951 	THIRD
        13 	06/20/13 	21120 	THIRD
        14 	06/21/13 	24214 	THIRD
        15 	06/22/13 	22466 	THIRD
        16 	06/23/13 	19881 	THIRD
        17 	06/24/13 	23479 	THIRD
        18 	06/25/13 	7879 	       THIRD

    DUMP FOR THIRD DETAIL

    Code:
    THIRDCONN 	THIRDOCCURANCES 	EVENTS
    I don't know why but when I changed the order of displaying the queries, it worked. I mean I mentioned THIRDCONN first and then SECOND and then FIRST.

IMN logo majestic logo threadwatch logo seochat tools logo