Issues with undefined variable (Not showing up)

[IvanGn]

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!

[WolfShade]

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,

^ _ ^

[WolfShade]

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

HTH,

^ _ ^

[AustinValley]

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> 
9. <cfdump var="#GetNoms#">
11. <cfquery datasource="Portal" name="GetPercentage">   
12. SELECT IDInquiry, PercentComplete_display  
13. FROM inq_PWSINPUT
14. </cfquery> 
16. <cfdump var="#GetPercentage#">
18. <cfquery dbtype="query" name="FinalMix">   
19. SELECT * FROM GetNoms, GetPercentage  
20. WHERE GetNoms.IDINQUIRY = GetPercentage.IDInquiry
21. </cfquery> 
23. <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?