Problem with SQL Syntax in <CFQUERY>

[scripter73]

Hi,

I'm trying to do a join of 3 tables in a SQL query. Everything works fine until I include the 3rd table. I want to pull only those records where fields of the 3rd table match portions of a field from the 1st table. I thought I could do it by including a String parsing function within the SQL, but I get an error "MID in *LIBL type *N not found.
"

My code follows and I'll bold the section where I use the 3rd table. My question is can I do this? Is my syntax correct? Should I just do a separate query, etc.?


<cfquery name=&quot;newdata&quot; datasource=&quot;#session.dsn#&quot;>
select PLWEBNEW.wnew_policy_alpha,
PLWEBNEW.wnew_policy_number,
PLWEBNEW.wnew_agent_code,
PLWEBNEW.wnew_issued_date,
PLWEBNEW.wnew_pol_code,
PLWEBNEW.wnew_master_agent,
PLWEBPOL.wpol_renewal_seq_number,
PLWEBPOL.wpol_endorsement_no,
PLWEBPOL.wpol_ins_first_name,
PLWEBPOL.wpol_ins_last_name,
PLWEBPOL.wpol_ins_mi,
PLWEBPOL.wpol_binder_number,
PLWEBPOL.wpol_eff_date,
PLWEBPOL.wpol_quoted_tot,
ARIVR.vr00_branch,
ARIVR.vr00_company,
ARIVR.vr00_product,
ARIVR.vr00_policy_number,
ARIVR.vr00_uprate_reasons,
ARIVR.vr00_orig_issued_tot
from PLWEBNEW,PLWEBPOL,ARIVR
<cfif Left(#session.agentcode#,1) eq &quot;G&quot;>
<!--- <cfset session.mastercode = #session.agentcode#> --->
where (wnew_master_agent = '#session.agentcode#' AND wnew_pol_code = 'N'
AND PLWEBNEW.wnew_policy_alpha = PLWEBPOL.wpol_policy_alpha AND PLWEBNEW.wnew_policy_number = PLWEBPOL.wpol_policy_number AND ARIVR.vr00_branch = Mid(PLWEBNEW.wnew_policy_alpha,1,2) and ARIVR.vr00_company = Mid(PLWEBNEW.wnew_policy_alpha,3,2) and ARIVR.vr00_product = Mid(PLWEBNEW.wnew_policy_alpha,5,2) and ARIVR.vr00_policy_number = PLWEBNEW.wnew_policy_number)
<cfelse>
where wnew_agent_code = '#session.agentcode#' and wnew_pol_code = 'N'
</cfif>
order by wnew_issued_date
</cfquery>





Thanks in advance for your help,
scripter73



Change Your Thinking, Change Your Life.

 

[CFDude]

What DB are you using?

Just a guess, but could it be this line:
and ARIVR.vr00_company = Mid(PLWEBNEW.wnew_policy_alpha,3,2) and ARIVR.vr00_product = Mid(PLWEBNEW.wnew_policy_alpha,5,2)

I am not sure if MID is a function availble in the database. (?) I don't see it available in SQL server. (But I could be wrong, of course)

 

[scripter73]

I'm using Client Access. Here's the error line:

[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0204 - MID in *LIBL type *N not found.

But even if I set variables, it doesn't work.

<cfif>....
<cfset a = Mid(....)>

select .... where field = a

</cfif>

I'm out of ideas. Thanks, anyway for your help.
scripter73
Change Your Thinking, Change Your Life.

 

[CFDude]

I don't know anything about Client Access. Since you were setting a CF variable, could it have been missing the ##?

e,g

<cfif>....
<cfset a = Mid(....)>

select .... where field = #a#

</cfif>

 

[scripter73]

Well, here's what I had, but since the query isn't finished, CF doesn't recognize the variables that I grabbed from the SELECT.


<cfquery name=&quot;newdata&quot; datasource=&quot;#session.dsn#&quot;>
select PLWEBNEW.wnew_policy_alpha,
PLWEBNEW.wnew_policy_number,
PLWEBNEW.wnew_agent_code,
PLWEBNEW.wnew_issued_date,
PLWEBNEW.wnew_pol_code,
PLWEBNEW.wnew_master_agent,
PLWEBPOL.wpol_renewal_seq_number,
PLWEBPOL.wpol_endorsement_no,
PLWEBPOL.wpol_ins_first_name,
PLWEBPOL.wpol_ins_last_name,
PLWEBPOL.wpol_ins_mi,
PLWEBPOL.wpol_binder_number,
PLWEBPOL.wpol_eff_date,
PLWEBPOL.wpol_quoted_tot,
ARIVR.vr00_branch,
ARIVR.vr00_company,
ARIVR.vr00_product,
ARIVR.vr00_policy_number,
ARIVR.vr00_uprate_reasons,
ARIVR.vr00_orig_issued_tot
from PLWEBNEW,PLWEBPOL,ARIVR
<cfif Left(#session.agentcode#,1) eq &quot;G&quot;>

PLWEBNEW.WNEW_vars not recognized yet
<cfset abc = Mid(PLWEBNEW.wnew_policy_alpha,1,2)>
<cfset def = Mid(PLWEBNEW.wnew_policy_alpha,3,2)>
<cfset ghi = Mid(PLWEBNEW.wnew_policy_alpha,5,2)>
<cfset prod = PLWEBNEW.wnew_policy_number>
<!--- <cfset session.mastercode = #session.agentcode#> --->
where (wnew_master_agent = '#session.agentcode#' AND wnew_pol_code = 'N'
AND PLWEBNEW.wnew_policy_alpha = PLWEBPOL.wpol_policy_alpha AND PLWEBNEW.wnew_policy_number = PLWEBPOL.wpol_policy_number AND ARIVR.vr00_branch = <cfoutput>#abc#</cfoutput> and ARIVR.vr00_company = <cfoutput>#def#</cfoutput> and ARIVR.vr00_product = <cfoutput>#ghi#</cfoutput> and ARIVR.vr00_policy_number = PLWEBNEW.wnew_policy_number)
<cfelse>
where wnew_agent_code = '#session.agentcode#' and wnew_pol_code = 'N'
</cfif>
order by wnew_issued_date
</cfquery>


Change Your Thinking, Change Your Life.

 

[scripter73]

Well, here's what I had, but since the query isn't finished, CF doesn't recognize the variables that I grabbed from the SELECT.


<cfquery name=&quot;newdata&quot; datasource=&quot;#session.dsn#&quot;>
select PLWEBNEW.wnew_policy_alpha,
PLWEBNEW.wnew_policy_number,
PLWEBNEW.wnew_agent_code,
PLWEBNEW.wnew_issued_date,
PLWEBNEW.wnew_pol_code,
PLWEBNEW.wnew_master_agent,
PLWEBPOL.wpol_renewal_seq_number,
PLWEBPOL.wpol_endorsement_no,
PLWEBPOL.wpol_ins_first_name,
PLWEBPOL.wpol_ins_last_name,
PLWEBPOL.wpol_ins_mi,
PLWEBPOL.wpol_binder_number,
PLWEBPOL.wpol_eff_date,
PLWEBPOL.wpol_quoted_tot,
ARIVR.vr00_branch,
ARIVR.vr00_company,
ARIVR.vr00_product,
ARIVR.vr00_policy_number,
ARIVR.vr00_uprate_reasons,
ARIVR.vr00_orig_issued_tot
from PLWEBNEW,PLWEBPOL,ARIVR
<cfif Left(#session.agentcode#,1) eq &quot;G&quot;>

PLWEBNEW.WNEW_vars not recognized yet
<cfset abc = Mid(PLWEBNEW.wnew_policy_alpha,1,2)>
<cfset def = Mid(PLWEBNEW.wnew_policy_alpha,3,2)>
<cfset ghi = Mid(PLWEBNEW.wnew_policy_alpha,5,2)>
<cfset prod = PLWEBNEW.wnew_policy_number>
<!--- <cfset session.mastercode = #session.agentcode#> --->
where (wnew_master_agent = '#session.agentcode#' AND wnew_pol_code = 'N'
AND PLWEBNEW.wnew_policy_alpha = PLWEBPOL.wpol_policy_alpha AND PLWEBNEW.wnew_policy_number = PLWEBPOL.wpol_policy_number AND ARIVR.vr00_branch = <cfoutput>#abc#</cfoutput> and ARIVR.vr00_company = <cfoutput>#def#</cfoutput> and ARIVR.vr00_product = <cfoutput>#ghi#</cfoutput> and ARIVR.vr00_policy_number = PLWEBNEW.wnew_policy_number)
<cfelse>
where wnew_agent_code = '#session.agentcode#' and wnew_pol_code = 'N'
</cfif>
order by wnew_issued_date
</cfquery>



Thanks for your help.
scripter73
Change Your Thinking, Change Your Life.

 

[CFDude]

Without knowing Client Access, I am not sure what to do. Is there any type of function in that DB that does the same thing as MID in CF? (e.g In SQL Serve, you have SUBSTRING) So if you can get the DB to do the function, I think your problem may be solved.

Another possibility is to just grab the data and do a conditional output.
e.g
<cfif vr00_branch is Mid(wnew_policy_alpha,1,2)>
show record, etc.
</cfif>
But this many mean too many records though.

You can also do a query of a query in CF 5. But I am not too sure how you can do that without looping through the fields first.

I hope this helps.

 

[scripter73]

Hi CFDude,

Thanks for continuing to help me out with my problem. I did a work-around for the SQL problem since I don't think Client Access will recognize some standard SQL commands like mid().

I decided to just do two separate <CFQUERY> commands and then intertwine them into a loop.

Sort of like...

<cfloop query=&quot;Q1&quot;>
<cfloop query=&quot;Q2&quot;>
1) do processing where elements from Q1 match Q2
2) add all needed elements from both queries into one array
</cfloop>
</cfloop>



I don't know why I'm having such a problem with this one report, I've done alot harder, but I'll keep you posted on the outcome.

Thanks again for your help,
scripter73





Change Your Thinking, Change Your Life.