Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Determining if data exists

Status
Not open for further replies.

PushCode

Programmer
Dec 17, 2003
573
US
My first query returns hundreds of records, all of which have a claim_id and an ident_type_id, but only a handfull have an ident_value. For the rest, the ident_value doesn't exist.

Here's the first query:
Code:
<cfquery name="form_info" datasource="dbapp">
  select claim_id, ident_type_id, ident_value
  from sri_claims.dbo.claim_main
</cfquery>

Now I need to determine, for a given range of ident_type_id's (ident_type_id is a number) if any of the corresponding ident_value's exist. If even one ident_value exists in that range, do something, if not, do nothing.

So I thought a query of the first query would accomplish this, but I'm not doing it correctly b/c I get the error: "Error: 'anyvalue' is not a number."

Is this even the best way to do this? What am I doing wrong?

Here's the second query (the query of the first query):
<cfquery dbtype="query" name="get_page_count">
select ident_value
from form_info
where
(
(ident_type_id >= 4151 and ident_type_id <= 4262)
or
(ident_type_id >= 4870 and ident_type_id <= 4885)
)
AND '#form_info.ident_value#' IS NOT NULL
</cfquery>

Thanks for any help!
 
For the first query, since you are not doing ANYTHING with the rows that return NULL for ident_value, you can filter the first query but putting in "WHERE ident_value IS NOT NULL". This way, all ranges will have an ident_value.

Here's what the query would look like:

Code:
<cfquery name="form_info" datasource="dbapp">
  select claim_id, ident_type_id, ident_value
  from sri_claims.dbo.claim_main
  where ident_value IS NOT NULL
</cfquery>

 
Thanks pitz77,

But I cannot do that. I need to return all records from the 1st query so I know what range of ident_type_id's I have to work with...that range will vary, and in the live app, those range numbers are dynamic.

So the first query must remain as is. Anyone else have any ideas?
 
Do you have any idea where "anyvalue" is coming from? it's not in your code. If you turn on debugging, it may show you what query was run so that you can trouble shoot what's coming out of the first query. i.e., ident_type_id may not always be a number? Check the datatype of that field and possible values. If it's not a number, you can't compare it to another number.
 
'anyvalue' is just a generic value I put in for the sake of this forum post. It is actually 'Kukarna', which is an ident_value being returned from the query.

ident_type_id is always a number. It's an 'int' data type.
ident_value is a 'varchar' data type.

I do have debuggin turned on. It shows 886 records being returned from the first query, and nothing about the second query other than the error message.
 
i dont think you need # signs, single quotes, or the 'form_info' in your QoQ. I would see if this works for you
Code:
<cfquery dbtype="query" name="get_page_count">
        select ident_value
        from form_info
        where
        (
          (ident_type_id >= 4151 and ident_type_id <= 4262)
          or
          (ident_type_id >= 4870 and ident_type_id <= 4885)
        )
        AND ident_value IS NOT NULL
      </cfquery>


=========================================
I have not failed. I've just found 10,000 ways that won't work.
Thomas A. Edison
 
Thanks NorthStarDA, but that returns an error as well:
'Error: ident_value is not a number'

Is there another way to accomplish this other than a query of a query? I've heard there are difficulties using IS NOT NULL or != '' in a query of a query.
 
yeah QoQ is limited in many ways- if your first query MUST remain the way it is, I think you would have to query the database a second time, but I'm not sure if this would work for you.
Code:
<cfquery name="form_info" datasource="dbapp">
  select claim_id, ident_type_id, ident_value
  from sri_claims.dbo.claim_main
</cfquery>

<cfquery name="form_info_2" datasource="dbapp">
  select claim_id, ident_type_id, ident_value
  from sri_claims.dbo.claim_main
  where
  (
     (ident_type_id >= 4151 and ident_type_id <= 4262)
        or
     (ident_type_id >= 4870 and ident_type_id <= 4885)
   )
   AND ident_value IS NOT NULL
</cfquery>

1 query to get the total amount of records, the next having a WHERE clause.

let us know if that gives you what you need


=========================================
I have not failed. I've just found 10,000 ways that won't work.
Thomas A. Edison
 
Okay, I've seriously simplified the first query to make this post easier to understand, but perhaps it warrents the full monty. Below is the full first query and the logic and second query in question. I can't figure how to add the where clause to the first query to create a second query instead of a QoQ. Anyway, this will probably lose everyone, but I'm desperate.

The idea here is that I already know how many ident_type_id's there are, and how many will fit on a page, and therefore, the maximum possible number of pages there could be.(this will all be printed out later) What I'm trying to do is only print out those pages where there are actually ident_values present. So each range in the second query represents those ident_type_id's that would be on that page. I'm trying to create a list variable of the pages the DO have ident_value's present so I can later loop through and print only those pages. So basically I just need help creating the list variable (#page_list#), which might look like this '1,3,6,7'.

Here's the code, any help would be wonderful. Thanks.
Code:
<cfparam name="attributes.claim_id" default="115115">

<cfquery name="form_info" datasource="dbapp">
  select a.claim_id, b.ident_type_id, b.ident_value
  from sri_claims.dbo.claim_main a, sri_claims.dbo.claim_info b, sri_claims.dbo.Form_Ident_Types d
  where a.claim_id=b.claim_id
  and b.ident_type_id=d.ident_type_id
  and a.claim_id=#attributes.claim_id#
  union 
  select a.claim_id, d.ident_type_id, null as ident_value
  from sri_claims.dbo.claim_main a, sri_claims.dbo.Form_Ident_Types d
  where a.claim_id=#attributes.claim_id#
  and not exists (select 'x' from sri_claims.dbo.claim_info where claim_id=a.claim_id and ident_type_id=d.ident_type_id)
</cfquery>

<cfset beg_num_a = 4151>
<cfset end_num_a = 4262>
<cfset beg_num_b = 4870>
<cfset end_num_b = 4885>
<cfset page_max = 7>

<cfloop from="1" to="#page_max#" index="page_num">
	  <cfquery dbtype="query" name="get_page_count">
	    select ident_value
	    from form_info
	    where 
	    (
	      (ident_type_id >= #beg_num_a# and ident_type_id <= #end_num_a#)
	      or 
	      (ident_type_id >= #beg_num_b# and ident_type_id <= #beg_num_b#)
	    )
		AND ident_value is not null
	  </cfquery>
	  
	  <cfif page_num LT page_max>
	    <cfset beg_num_a = end_num_a + 1>
	    <cfset end_num_a = beg_num_a + 111>
	    <cfset beg_num_b = end_num_b + 1>
	    <cfset end_num_b = beg_num_b + 15>
	  </cfif>
	  <cfif get_page_count.recordcount GT 0>
	    <cfif isDefined('page_list') AND page_num GT 1>
		  <cfset page_list = page_list & ',' & page_num>
		<cfelse>
		  <cfset page_list = page_num>
		</cfif>
	  <cfoutput>#form_info.ident_value#<br></cfoutput>
	  </cfif> 
	</cfloop>
	<cfoutput>#page_list#</cfoutput>
 
First have you tried:

Code:
      <cfquery dbtype="query" name="get_page_count">
        select ident_value
        from form_info
        where 
        (
          (ident_type_id >= #beg_num_a# and ident_type_id <= #end_num_a#)
          or 
          (ident_type_id >= #beg_num_b# and ident_type_id <= #beg_num_b#)
        )
        [b]AND len(ident_value) > 0[/b]
      </cfquery>

Never fear, I have another idea...

Code:
<cfparam name="attributes.claim_id" default="115115">

<cfquery name="form_info" datasource="dbapp">
  select a.claim_id, b.ident_type_id, b.ident_value, [B]idflag = CASE WHEN (len(b.ident_value) > 0) THEN 1 ELSE 0 END[/B]
  from sri_claims.dbo.claim_main a, sri_claims.dbo.claim_info b, sri_claims.dbo.Form_Ident_Types d
  where a.claim_id=b.claim_id
  and b.ident_type_id=d.ident_type_id
  and a.claim_id=#attributes.claim_id#
  union 
  select a.claim_id, d.ident_type_id, null as ident_value
  from sri_claims.dbo.claim_main a, sri_claims.dbo.Form_Ident_Types d
  where a.claim_id=#attributes.claim_id#
  and not exists (select 'x' from sri_claims.dbo.claim_info where claim_id=a.claim_id and ident_type_id=d.ident_type_id)
</cfquery>

<cfset beg_num_a = 4151>
<cfset end_num_a = 4262>
<cfset beg_num_b = 4870>
<cfset end_num_b = 4885>
<cfset page_max = 7>

<cfloop from="1" to="#page_max#" index="page_num">
      <cfquery dbtype="query" name="get_page_count">
        select ident_value
        from form_info
        where 
        (
          (ident_type_id >= #beg_num_a# and ident_type_id <= #end_num_a#)
          or 
          (ident_type_id >= #beg_num_b# and ident_type_id <= #beg_num_b#)
        )
        AND [b]idflag = 1[/b]
      </cfquery>
      
      <cfif page_num LT page_max>
        <cfset beg_num_a = end_num_a + 1>
        <cfset end_num_a = beg_num_a + 111>
        <cfset beg_num_b = end_num_b + 1>
        <cfset end_num_b = beg_num_b + 15>
      </cfif>
      <cfif get_page_count.recordcount GT 0>
        <cfif isDefined('page_list') AND page_num GT 1>
          <cfset page_list = page_list & ',' & page_num>
        <cfelse>
          <cfset page_list = page_num>
        </cfif>
      <cfoutput>#form_info.ident_value#<br></cfoutput>
      </cfif> 
    </cfloop>
    <cfoutput>#page_list#</cfoutput>

ALFII.com
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top