CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge -> Programming

 SQL Query vice Table Query results are no records found when all columns in the search record are populated.

Print topic Send  topic

Author Message
patm

Posts: 3
Posted: 05/17/2010, 8:08 PM

When writing an SQL Query to database vice Table Query results are no records found when all columns in the search record are populated.
This does not happen when you use the table query. Example In the table query you have in your search record 3 columns you can search on and you only populate one. The other two return a null set and are overlooked in the query. This does not happen when you convert to an SQL query. The query uses all columns in its query and if not populate looks for records that are null in these columns. This is not what I want. I want them to be excluded from the query as it does in the Table Query.

Below is my sql query:

SELECT Count(*) AS 'totalsession', caseservice.*, case_name, prog_code, closed, name
FROM ((caseservice LEFT JOIN intake ON
caseservice.case_nmber = intake.case_nmbr AND caseservice.instance = intake.instance) LEFT JOIN case_assignment ON
caseservice.case_nmber = case_assignment.case_nmber AND caseservice.instance = case_assignment.instan) LEFT JOIN users ON
case_assignment.case_worker = users.id
WHERE(caseservice.session_date IS NOT NULL
AND caseservice.session_date >= '{s_from}'
AND caseservice.session_date <= '{s_to}' )
AND caseservice.client_cancel = {Expr0}
AND caseservice.theripist_cancel = {Expr1}
AND caseservice.client_noshow = {Expr2}
AND (intake.closed >= '{s_from}'
AND intake.closed <= '{s_to}')
AND intake.prog_code = '{s_prog}'
AND caseservice.case_nmber = {s_cnumber}
AND users.id = {s_therapist}
GROUP BY caseservice.case_nmber
HAVING Count(*)< 8
View profile  Send private message
mamboBROWN


Posts: 1712
Posted: 05/18/2010, 7:51 PM

patm,
You could have default values in the event of null values. There is another way but I can seem to remember it right now. When it comes to me I will let you know.
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.