[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

My SQL Error

I have the following query!

select DISTINCT (stores.id) as single, stores.name, c.styleid,
si.itemid, c.item_type, IFNULL(c.cost_retail,0), si.date_paid
from si
inner join stores on si.storeid = stores.storeid
inner join c on si.itemid = c.itemid

it runs perfect in Mysql editor when i run in cfquery i get the following error

Invalid CFML construct found on line 1 at column 37.
ColdFusion was looking at the following text:

''

The CFML compiler was processing:

    An expression beginning with var.IFNULL, on line 1, column 1.This message is usually caused by a problem in the expressions structure.



Please guide
0
Gurpreet Singh Randhawa
Asked:
Gurpreet Singh Randhawa
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try COALESCE instead of IFNULL
0
 
gdemariaCommented:
I think you need to show more code, if that SELECT is inside a CFQUERY it seems like that is not where the error is happening because the error is a CF error not a database error

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
here is my code!

<cfquery name = "qInventory" datasource="#dsn#">
select DISTINCT (stores.id) as single, stores.name, c.styleid,
si.itemid, c.item_type, IFNULL(c.cost_retail,0), si.date_paid
from si
inner join stores on si.storeid = stores.storeid
inner join c on si.itemid = c.itemid
WHERE date_paid >= #createodbcdate(start_date)#
</cfquery>
<cf_dump variable="qInventory">
<cfif qInventory.recordcount>
      <cfset c_sales = qInventory.recordcount>
    <CFSET cost_unit = #qInventory.cost_retail#>
<cfoutput>
<cf_dump variable="cost_unit">
</cfoutput>    
<cfelse>
      <cfset c_sales = 0>
    <CFSET cost_unit = 0>
</cfif>
<cfabort>

now the record comes to 7000, but my cost)unit shows "" i mean null value
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
gdemariaCommented:
You need to use #s inside your cfdump

(You don't need them in the CFSET, but it didn't hurt anything)

<cfquery name = "qInventory" datasource="#dsn#">
  select DISTINCT (stores.id) as single, stores.name, c.styleid,
  si.itemid, c.item_type, IFNULL(c.cost_retail,0), si.date_paid 
  from si
    inner join stores on si.storeid = stores.storeid
   inner join c on si.itemid = c.itemid 
   WHERE date_paid >= #createodbcdate(start_date)# 
</cfquery>

<cf_dump variable="#qInventory#">
<cfif qInventory.recordcount>
    <cfset c_sales = qInventory.recordcount>
    <CFSET cost_unit = qInventory.cost_retail>
    <cf_dump variable="#cost_unit#">
<cfelse>
    <cfset c_sales = 0>
    <CFSET cost_unit = 0>
</cfif>
<cfabort>

Open in new window

0
 
gdemariaCommented:
wait.. are you using your own custom dump tag?

cfDump has no _  and it's var =

<cf_dump variable="#qInventory#">

should be..

<cfdump var="#qInventory#">


Unless you have a custom tag called dump.cfm ??
0
 
gdemariaCommented:


when using a function on a field, you should add an alias

select DISTINCT (stores.id) as single
     , stores.name
     , c.styleid
     , si.itemid
     , c.item_type,
     , IFNULL(c.cost_retail,0) as cost_retail   <======= add an alias  
     , si.date_paid
from si

0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now