SQL查询由于某种原因而中断 [英] SQL Query breaking for some reason

问题描述

请考虑以下图片：

 < cfquery datasource =mydbname =qCoulmnInsert> 
 INSERT INTO 
 simplexresults.contactresults_email_account_summary_devices 
（open_desktop_int，
 open_other_int，
 open_phone_int，
 open_tablet_int，
 open_webmail_int，
< ！--- FOR Unique Open ---> 
 unique_webMail_int，
 unique_tablet_int，
 unique_other_int，
 unique_phone_int，
 unique_desktop_int，
< -  FOR DATES ---> 
 startdate_dt，
 enddate_dt，
 date_dt）
 
 VALUES 
<！---循环遍历数组---> 
< cfloop from =1to =＃arrayLen（cfData）＃index =i> 
（
< cfif structKeyExists（cfData [i]，open）> 
< cfqueryparam CFSQLTYPE =CF_SQL_INTEGERvalue =＃cfData [i] .open.Desktop＃ > 
< cfelse> 
 NULL 
< / cfif>，
< cfif structKeyExists（cfData [i]，open）> 
 < cfqueryparam CFSQLTYPE =CF_SQL_INTEGERvalue =＃cfData [i] .open.Other＃> 
< cfelse> 
 NULL 
< / cfif>，
 
 
< cfif structKeyExists（cfData [i]，open）> 
< cfqueryparam CFSQLTYPE =CF_SQL_INTEGERvalue =＃cfData [i] .open.Phone＃ > 
< cfelse> 
 NULL 
< / cfif>，
 
等等... 
 ... 。
 
< / cfquery> 
  

我收到以下错误： / p>

 元素OPEN.DESKTOP在作为表达式一部分引用的CFML结构中未定义
 
错误发生在C：\myfile.cfm：line 55 
 
 53：（
 54：< cfif structKeyExists（cfData [i]，open）& 
 55：< cfqueryparam CFSQLTYPE =CF_SQL_INTEGERvalue =＃cfData [i] .open.Desktop＃> 
 56：< cfelse> 
 57：NULL 
  

我知道它没有定义一些结构，如下面，但我已经在我的< cfelse> 语句中包含NULL来处理这些情况。我在这里做错了吗？请指定。

但是，查询在定义OPEN的日期范围内运行良好。

正确的方式：

$ c
$ b

 < cfif structKeyExists（cfData [i]，open）& 
< cfif structKeyExists（cfData [i] .open，Desktop）> 
< cfqueryparam CFSQLTYPE =CF_SQL_INTEGERvalue =＃cfData [i] [open] [桌面]＃> 
< cfelse> 
 NULL 
< / cfif> 
< cfelse> 
 NULL 
 
< / cfif> ，
  

解决方案

您认为open if语句，但Desktop不总是可用，并且不包括在限定语句中。如果换句话说，桌面不总是一个键，但只要定义了打开键，你的循环总是寻找它。

Please consider the following Image:

When I ran the following query in coldfusion:

<cfquery datasource="mydb" name="qCoulmnInsert">
INSERT INTO 
simplexresults.contactresults_email_account_summary_devices 
(open_desktop_int,
open_other_int,
open_phone_int,
open_tablet_int,
open_webmail_int,  
<!--- FOR Unique Open --->
unique_webMail_int,
unique_tablet_int,
unique_other_int,
unique_phone_int,
unique_desktop_int,
<!--- FOR DATES --->
startdate_dt,
enddate_dt,
date_dt)

VALUES
<!--- loop through your array --->
<cfloop from="1" to="#arrayLen(cfData)#" index="i">
(
<cfif structKeyExists(cfData[i], "open")>
<cfqueryparam CFSQLTYPE="CF_SQL_INTEGER" value="#cfData[i].open.Desktop#">
<cfelse>
NULL
</cfif> ,
<cfif structKeyExists(cfData[i], "open")>
<cfqueryparam CFSQLTYPE="CF_SQL_INTEGER" value="#cfData[i].open.Other#">
<cfelse>
NULL
</cfif> ,


<cfif structKeyExists(cfData[i], "open")>
<cfqueryparam CFSQLTYPE="CF_SQL_INTEGER" value="#cfData[i].open.Phone#">
<cfelse>
NULL
</cfif> ,

  and so on and so forth ...
                   ....

</cfquery>

I get the following error:

 Element OPEN.DESKTOP is undefined in a CFML structure referenced as part of an expression.

The error occurred in C:\myfile.cfm: line 55

53 :              (
54 :               <cfif structKeyExists(cfData[i], "open")>
55 :                 <cfqueryparam CFSQLTYPE="CF_SQL_INTEGER" value="#cfData[i].open.Desktop#">
56 :               <cfelse>
57 :                 NULL

I know it's not defined for some of the structures as shown in the image below, but I have included "NULL" in my <cfelse> statement to handle those cases. Am I doing something wrong here? Please advise.

However the query runs fine for the date range where OPEN'S are defined.

Correct Way:

<cfif structKeyExists(cfData[i], "open")>
                <cfif  structKeyExists(cfData[i].open,"Desktop")>
                <cfqueryparam CFSQLTYPE="CF_SQL_INTEGER" value="#cfData[i]["open"]["Desktop"]#">
                <cfelse>
                 NULL
                </cfif>
              <cfelse>
                NULL

              </cfif> ,

解决方案

You qualify that "open" is available in your if statement, but "Desktop" is not always available and is not wrapped in a qualifying statement. If other words, "Desktop" is not always a key, but your loop always looks for it as long as the "open" key is defined.

这篇关于SQL查询由于某种原因而中断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！