I want to insert a value if a column exists in query else insert null

Report · Aug 19, 2024

Hi ,

Right now I'm using something like this,

My query is ,

insert into ...values(

.

</cfloop>

Now I want to change the code to something like ,

, <cfqueryparam cfsqltype="cf_sql_varchar" value="#IIF(QueryKeyExists(local.Q_Data,'FILE_OPERATOR'),TRIM(FILE_OPERATOR),NULL)#"> null="#this.comp('Imports').checkIsNull(FILE_OPERATOR)#">

But it is not working. I'm getting error like FILE_OPERATOR is undefined.

Please help. Thank you.

Report · Aug 20, 2024

The following structure can be improved using the ternary operator

<cfloop query="local.Q_Data>
    <cfif QueryKeyExists(local.Q_Data,"FILE_OPERATOR")>
    </cfif>
</cfloop>

There is yet another place where the code can be improved. The code invokes a component to check for a null value: this.comp('Imports').checkIsNull(FILE_OPERATOR). This is not only expensive, it is also unnecessary.

At this point in the code we have local.Q_data. Therefore we know which values of local.Q_Data['FILE_OPERATOR'] are null or the empty string. (Note: in ColdFusion, null may be stored as empty-string within a query-result. )

So, let's put the two suggestions together. To do so, I shall assume that what you want to achieve is similar to the following:

INSERT INTO MyTable
  ( Column1, Column2, fileOperator, column4, column5)
VALUES
 (1, 103, 'Operator One', 'Mumbai', 'India'), 
 (2, 105, 'Operator Two', 'Beijing', 'China'), 
 (3, 107, 'Operator Three', 'Paris', 'France'), 
 (4, 109, 'Operator Four', 'London', 'UK'), 
 (5, 111, 'Operator Five', 'New York', 'USA'),
 (6, 113, 'Operator Six', 'Milan', 'Italy'),
 (7, 115, 'Operator Seven', 'Rio de Janeiro', 'Brazil'),
 (8, 117, 'Operator Eight', 'Johannesburg', 'South Africa')

That is to say, I am assuming that you intend to use a loop to insert each row of values. Then the required code will be something like:

<cfquery >
	INSERT INTO MyTable (
		Column1, 
		Column2, 
		fileOperator, 
		Column4,
		Column5)
	VALUES 
	<cfloop query="local.Q_Data">		
		<cfset currentFileOperatorValue=queryKeyExists(local.Q_Data,"FILE_OPERATOR") ? "#FILE_OPERATOR#" : "">
		
		(
			#Column1#,
			#Column2#,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(currentFileOperatorValue)#" null="#trim(currentFileOperatorValue) eq ''#">,
			'#Column4#',
			'#Column5#')

	   <cfif CURRENTROW LT RECORDCOUNT>
	   		,
	   </cfif>
    </cfloop>
</cfquery>

Some explanations:

value="#trim(currentFileOperatorValue)#" null="#trim(currentFileOperatorValue) eq ''#" means that null will be inserted into the database if trim(currentFileOperatorValue) eq '' is true. Otherwise, trim(currentFileOperatorValue) will be inserted instead.
<cfif CURRENTROW LT RECORDCOUNT> ,</cfif> ensures that a comma is added between the rows that are to be inserted.

That's it.

You should also have a look at the INSERT INTO SELECT statement.

Report · Aug 20, 2024

Thank you for your reply. But is there a way to use iif or cfif tag inside cfqueryparam?

Report · Aug 21, 2024

Thank you for your reply. But is there a way to use iif or cfif tag inside cfqueryparam?

By @Vishnu22410012h6s8

Yes. What I had given was just a suggestion to improve the original code you posted. I infact expected that you would further improve the code. In any case, your question has drawn my attention to some typos and duplicated code, which I have corrected.

As an example, you could go one step further, and improve the code as follows:

<cfquery >
	INSERT INTO MyTable (
		Column1, 
		Column2, 
		fileOperator, 
		Column4,
		Column5)
	VALUES
	<cfloop query="local.Q_Data">
		 (
			#intVar1#,
			#intVar2#,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(FILE_OPERATOR)#" null="#NOT queryKeyExists(local.Q_Data,'FILE_OPERATOR') OR local.Q_Data['FILE_OPERATOR'][CURRENTROW] EQ ''#">,
			'#var4#',
			'#var5#')

	   <cfif CURRENTROW LT RECORDCOUNT>
	   		,
	   </cfif>
    </cfloop>
</cfquery>

Report · Aug 21, 2024

Oh, note that

<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(FILE_OPERATOR)#" null="#NOT queryKeyExists(local.Q_Data,'FILE_OPERATOR') OR local.Q_Data['FILE_OPERATOR'][CURRENTROW] EQ ''#">

is equivalent to the following IIF-version:

<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(FILE_OPERATOR)#" null="#iif((NOT queryKeyExists(local.Q_Data,'FILE_OPERATOR') OR local.Q_Data['FILE_OPERATOR'][CURRENTROW] EQ ''), true, false)#">

Report · Aug 26, 2024

@Vishnu22410012h6s8 , did that help?

Report · Aug 29, 2024

Thank you so much @BKBK , Let's say if I have more than one columns like FILE_OPERATOR, then how to do check queryexists and null check in same code as above?

Report · Aug 29, 2024

I'm sorry I tried using ,

It is not working. No errors in code but if the column FILE_OPERATOR don't exist, then it throws undefined error.

My full code is ,

INSERT INTO #REQUEST.CFG.TablePrefix#T_#local.importType#

(

DoFiImportLink

, DoFiDocD

, DoFiBName

, DorFiClientLID

, DoFiState

, DoFiPEXA

, DoFiDealType

, DoFiAccNum

, DoFiDateDocsReturned

, DoFiSettlementDate

, DoFiFileOperator

, DoFiLoanAmount

)

VALUES

(#Q_InsertImport.newImId#

, <cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(DOCUPREP_ID)#" null="#this.comp('Imports').checkIsNull(DOCUPREP_ID)#">

, <cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(BORROWER_NAME)#" null="#this.comp('Imports').checkIsNull(BORROWER_NAME)#">

, <cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(CLIENT_LOAN_ID)#" null="#this.comp('Imports').checkIsNull(CLIENT_LOAN_ID)#">

, <cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(STATE)#" null="#this.comp('Imports').checkIsNull(STATE)#">

, <cfif QueryKeyExists(local.Q_Data,"PEXA")><cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(PEXA)#" null="#this.comp('Imports').checkIsNull(PEXA)#"><cfelse>NULL</cfif>

, <cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(DEAL_TYPE)#" null="#this.comp('Imports').checkIsNull(DEAL_TYPE)#">

, <cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(ACCOUNT_NUMBER)#" null="#this.comp('Imports').checkIsNull(ACCOUNT_NUMBER)#">

, <cfif QueryKeyExists(local.Q_Data,"DATE_DOCUMENTS_RETURNED")><cfqueryparam cfsqltype="cf_sql_date" value="#LSDateFormat(DATE_DOCUMENTS_RETURNED,'yyyy-mm-dd')#"><cfelse>NULL</cfif>

, <cfqueryparam cfsqltype="cf_sql_date" value="#LSDateFormat(SETTLEMENT_DATE,'yyyy-mm-dd')#">

, <cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(FILE_OPERATOR)#" null="#NOT queryKeyExists(local.Q_Data,'FILE_OPERATOR') OR local.Q_Data['FILE_OPERATOR'] EQ ''#">

, <cfif QueryKeyExists(local.Q_Data,"LOAN_AMOUNT")><cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(LOAN_AMOUNT)#"><cfelse>NULL</cfif>

)

SELECT @@IDENTITY AS newID;

</cfquery>

</cfif>

</cfloop>

Report · Aug 29, 2024

Actually the query local.Q_Data is not an actual table, we are looping through a set of records in an excel sheet.

Report · Aug 30, 2024

Actually the query local.Q_Data is not an actual table, we are looping through a set of records in an excel sheet.

By @Vishnu22410012h6s8

I don't think that that really matters. Irrespective of the origin of the data, the main thing is that local.Q_Data is a query object.

Report · Aug 30, 2024

I'm sorry I tried using ,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(FILE_OPERATOR)#" null="#NOT queryKeyExists(local.Q_Data,'FILE_OPERATOR') OR local.Q_Data['FILE_OPERATOR'] EQ ''#">

It is not working. No errors in code but if the column FILE_OPERATOR don't exist, then it throws undefined error.

My full code is ,

....

By @Vishnu22410012h6s8

I can see why it didn't work. The row number was missing. I have now corrected it.

Let's say if I have more than one columns like FILE_OPERATOR, then how to do check queryexists and null check in same code as above?

By @Vishnu22410012h6s8

No problem. Yes, you can do it as above.

For example,

 <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(DOCUPREP_ID)#" null="#(NOT queryKeyExists(local.Q_Data,'DOCUPREP_ID')) OR local.Q_Data['DOCUPREP_ID'][CURRENTROW] EQ '' OR local.Q_Data['DOCUPREP_ID'][CURRENTROW] EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(BORROWER_NAME)#" null="#(NOT queryKeyExists(local.Q_Data,'BORROWER_NAME')) OR local.Q_Data['BORROWER_NAME'][CURRENTROW] EQ '' OR local.Q_Data['BORROWER_NAME'][CURRENTROW] EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(CLIENT_LOAN_ID)#" null="#(NOT queryKeyExists(local.Q_Data,'CLIENT_LOAN_ID')) OR local.Q_Data['CLIENT_LOAN_ID'][CURRENTROW] EQ '' OR local.Q_Data['CLIENT_LOAN_ID'][CURRENTROW] EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(STATE)#" null="#(NOT queryKeyExists(local.Q_Data,'STATE')) OR local.Q_Data['STATE'][CURRENTROW] EQ '' OR local.Q_Data['STATE'][CURRENTROW] EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(PEXA)#" null="#(NOT queryKeyExists(local.Q_Data,'PEXA')) OR local.Q_Data['PEXA'][CURRENTROW] EQ '' OR local.Q_Data['PEXA'][CURRENTROW] EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(DEAL_TYPE)#" null="#(NOT queryKeyExists(local.Q_Data,'DEAL_TYPE')) OR local.Q_Data['DEAL_TYPE'][CURRENTROW] EQ '' OR local.Q_Data['DEAL_TYPE'][CURRENTROW] EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(ACCOUNT_NUMBER)#" null="#(NOT queryKeyExists(local.Q_Data,'ACCOUNT_NUMBER')) OR local.Q_Data['ACCOUNT_NUMBER'][CURRENTROW] EQ '' OR local.Q_Data['ACCOUNT_NUMBER'][CURRENTROW] EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_date" value="#LSDateFormat(DATE_DOCUMENTS_RETURNED,'yyyy-mm-dd')#" null="#NOT queryKeyExists(local.Q_Data,'DATE_DOCUMENTS_RETURNED')#">
, <cfqueryparam cfsqltype="cf_sql_date" value="#LSDateFormat(SETTLEMENT_DATE,'yyyy-mm-dd')#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(FILE_OPERATOR)#" null="#(NOT queryKeyExists(local.Q_Data,'FILE_OPERATOR')) OR local.Q_Data['FILE_OPERATOR'][CURRENTROW] EQ '' OR local.Q_Data['FILE_OPERATOR'][CURRENTROW] EQ 'null'#">
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(LOAN_AMOUNT)#" null="#(NOT queryKeyExists(local.Q_Data,'LOAN_AMOUNT')) OR local.Q_Data['LOAN_AMOUNT'][CURRENTROW] EQ '' OR local.Q_Data['LOAN_AMOUNT'][CURRENTROW] EQ 'null'#">

Report · Aug 31, 2024

Hi I tested the above fix for one column , the FILE_OPERATOR,

Still same error,

Variable FILE_OPERATOR is undefined.

Report · Aug 31, 2024

Hi I tested the above fix for one column , the FILE_OPERATOR,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(FILE_OPERATOR)#" null="#(NOT queryKeyExists(local.Q_Data,'FILE_OPERATOR')) OR local.Q_Data['FILE_OPERATOR'][CURRENTROW] EQ '' OR local.Q_Data['FILE_OPERATOR'][CURRENTROW] EQ 'null'#">

Still same error,
Variable FILE_OPERATOR is undefined.

By @Vishnu22410012h6s8

I am surprised at that error. It can only mean that ColdFusion attempted to evaluate the value in the following situation:

 value="#trim(FILE_OPERATOR)#" null="true"

As you can see from the cfqueryparam documentation, ColdFusion should have ignored the value attribute when null="true".

In any case, let's assume that ColdFusion never ignores the value attribute. Not even when null="true". Then one way to work around that is:

 value="#queryKeyExists(local.Q_Data,'FILE_OPERATOR')?trim(FILE_OPERATOR):'null'#"

You will have noticed that I have dropped the fully-qualified name, local.Q_Data['FILE_OPERATOR'], and used FILE_OPERATOR instead. From the code you have shared, I am assuming that the columns DOCUPREP_ID, BORROWER_NAME, CLIENT_LOAN_ID, etc. belong to just one query, local.Q_Data. As those column names are not shared by any other query, we may simplify the code as follows:

  <cfqueryparam cfsqltype="cf_sql_varchar" value="#queryKeyExists(local.Q_Data,'DOCUPREP_ID')?trim(DOCUPREP_ID):'null'#" null="#(NOT queryKeyExists(local.Q_Data,'DOCUPREP_ID')) OR DOCUPREP_ID EQ '' OR DOCUPREP_ID EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#queryKeyExists(local.Q_Data,'BORROWER_NAME')?trim(BORROWER_NAME):'null'#" null="#(NOT queryKeyExists(local.Q_Data,'BORROWER_NAME')) OR BORROWER_NAME EQ '' OR BORROWER_NAME EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#queryKeyExists(local.Q_Data,'CLIENT_LOAN_ID')?trim(CLIENT_LOAN_ID):'null'#" null="#(NOT queryKeyExists(local.Q_Data,'CLIENT_LOAN_ID')) OR CLIENT_LOAN_ID EQ '' OR CLIENT_LOAN_ID EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#queryKeyExists(local.Q_Data,'STATE')?trim(STATE):'null'#" null="#(NOT queryKeyExists(local.Q_Data,'STATE')) OR STATE EQ '' OR STATE EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#queryKeyExists(local.Q_Data,'PEXA')?trim(PEXA):'null'#" null="#(NOT queryKeyExists(local.Q_Data,'PEXA')) OR PEXA EQ '' OR PEXA EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#queryKeyExists(local.Q_Data,'DEAL_TYPE')?trim(DEAL_TYPE):'null'#" null="#(NOT queryKeyExists(local.Q_Data,'DEAL_TYPE')) OR DEAL_TYPE EQ '' OR DEAL_TYPE EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#queryKeyExists(local.Q_Data,'ACCOUNT_NUMBER')?trim(ACCOUNT_NUMBER):'null'#" null="#(NOT queryKeyExists(local.Q_Data,'ACCOUNT_NUMBER')) OR ACCOUNT_NUMBER EQ '' OR ACCOUNT_NUMBER EQ 'null'#">
, <cfqueryparam cfsqltype="cf_sql_date" value="#queryKeyExists(local.Q_Data,'DATE_DOCUMENTS_RETURNED')?LSDateFormat(DATE_DOCUMENTS_RETURNED,'yyyy-mm-dd'):'null'#" null="#NOT queryKeyExists(local.Q_Data,'DATE_DOCUMENTS_RETURNED')#">
, <cfqueryparam cfsqltype="cf_sql_date" value="#queryKeyExists(local.Q_Data,'LSDateFormat(SETTLEMENT_DATE,'yyyy-mm-dd')#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#queryKeyExists(local.Q_Data,'FILE_OPERATOR')?trim(FILE_OPERATOR):'null'#" null="#(NOT queryKeyExists(local.Q_Data,'FILE_OPERATOR')) OR FILE_OPERATOR EQ '' OR FILE_OPERATOR EQ 'null'#">
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#queryKeyExists(local.Q_Data,'LOAN_AMOUNT')?trim(LOAN_AMOUNT):'null'#" null="#(NOT queryKeyExists(local.Q_Data,'LOAN_AMOUNT')) OR LOAN_AMOUNT EQ '' OR LOAN_AMOUNT EQ 'null'#">

Report · Sep 02, 2024

Hi @Vishnu22410012h6s8 , any update on this?

Report · Sep 02, 2024

Hi @BKBK , thank you so much it is working, for the DATE_DOCUMENTS_RETURNED column also can I include

OR DATE_DOCUMENTS_RETURNED EQ '' OR DATE_DOCUMENTS_RETURNED EQ 'null'

in it's null attribute?