Issues with undefined variable (Not showing up)

New Here ,
Oct 23, 2018 Oct 23, 2018

Copy link to clipboard

Copied

Hi!

​I'm having an issue with joining/merging two different databases. I'm receiving all of the variables from one Datasource, but one variable is not showing up from the other DataSource.

Both of them are related to the "IDInquiry" field, but even after relating the two its not showing up.

​Attached is my code:

<cfquery datasource="Portal" name="GetNoms"> 

SELECT IDStudent, NameFull, School_Name_Previous, InqContactPhone, P1_full, P1_phone_H, P1_city, IDINQUIRY 

FROM Inquiries WHERE Inquiry = 1 AND Adm_Record_Status = 'Active' AND (Application IS NULL OR Application = 0)

AND Adm_Enr_Status = 'Inquired' AND P1_phone_H IS NOT NULL

AND P1_city IS NOT NULL AND YOE = '#YOE#'

ORDER BY P1_city ASC</

cfquery>

<cfquery datasource="Portal" name="GetPercentage"> 

SELECT IDInquiry, PercentComplete_display

FROM inq_PWSINPUT</cfquery>

<cfquery dbtype="query" name="FinalMix"> 

SELECT * FROM GetNoms, GetPercentage

WHERE GetNoms.IdInquiry = GetPercentage.IDINQUIRY

</cfquery>

​What its telling me, is that my PercentComplete_display is undefined.

​Any help would be appreciated!

Views

164

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 23, 2018 Oct 23, 2018

Copy link to clipboard

Copied

CFDUMP the results of the first two queries to make sure you're getting everything that you expect.

If those show correctly, then place the third query/output inside a CFTRY / CFCATCH and CFDUMP the #cfcatch# response.

HTH,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 23, 2018 Oct 23, 2018

Copy link to clipboard

Copied

I'd also read up on QoQ, because not everything that you can do in a standard query will work in QoQ.

HTH,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 23, 2018 Oct 23, 2018

Copy link to clipboard

Copied

LATEST

Both your datasource statements are pointing to the same name PORTAL.  So I am thinking that you are trying to connect two tables together, not merging two different tables from two different databases.  If I'm wrong, then the datasource statements would need to be different, as two databases wouldn't be on one datasource without some linking at the DB Server level.

At first look, since you are querying the same datasource for both tables, I'd seriourly consider creating a single query joining the two tables.  I've had hit/miss results on QoQ lately.  I've never seen or tried joining tables in a QoQ. How you structure a join may depend on the database type.   First, lets try this like WoldShade said;

  1. <cfquery datasource="Portal" name="GetNoms">   
  2. SELECT IDStudent, NameFull, School_Name_Previous, InqContactPhone, P1_full, P1_phone_H, P1_city, IDINQUIRY   
  3. FROM Inquiries WHERE Inquiry = 1 AND Adm_Record_Status = 'Active' AND (Application IS NULL OR Application = 0)  
  4. AND Adm_Enr_Status = 'Inquired' AND P1_phone_H IS NOT NULL  
  5. AND P1_city IS NOT NULL AND YOE = '#YOE#'  
  6. ORDER BY P1_city ASC
  7. </cfquery> 
  8. <cfdump var="#GetNoms#">
  9. <cfquery datasource="Portal" name="GetPercentage">   
  10. SELECT IDInquiry, PercentComplete_display  
  11. FROM inq_PWSINPUT
  12. </cfquery> 
  13. <cfdump var="#GetPercentage#">
  14. <cfquery dbtype="query" name="FinalMix">   
  15. SELECT * FROM GetNoms, GetPercentage  
  16. WHERE GetNoms.IDINQUIRY = GetPercentage.IDInquiry
  17. </cfquery> 
  18. <cfdump var="#FinalMix#"/>

Do you get one, two or three data dumps?

So my questions to you are, what is your database server type?  I use Postgresql, and it is case sensitive for field and table names.  Do you know how to go about creating a select join statement to bring the two tables together in a single query?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines