?
Solved

SQL Query stopped returning Data

Posted on 2013-01-03
6
Medium Priority
?
499 Views
Last Modified: 2013-01-04
Say,


Data stored in MySql 5 running on Win7
Since moving it from linux MySql DB the join query below doesn't return data: If I remove the join - tTop10NosByCallType returns data.
Can you explain it and how to solve please?

SELECT
*,
     tDirectory.`CallerID` AS tDirectory_CallerID,
     tDirectory.`Category` AS tDirectory_Category,
     tDirectory.`Access` AS tDirectory_Access,
     tDirectory.`Status` AS tDirectory_Status
FROM
     `tDirectory` tDirectory LEFT OUTER JOIN `tTop10NosByCallType` tTop10NosByCallType ON tDirectory.`TelNo` = tTop10NosByCallType.`No`
WHERE
     tTop10NosByCallType.Client = $P{Client}
     and `Invoice number` = $P{InvNo}
ORDER BY
     CallType DESC,
     CountByCallType DESC,
     DurationHHMM DESC
0
Comment
Question by:shaunwingin
6 Comments
 

Author Comment

by:shaunwingin
ID: 38739707
Do you need to see the tables?
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38739886
Move below where condition to  join .... I mean with on  
 
tTop10NosByCallType.Client = $P{Client}


SELECT
*,
     tDirectory.`CallerID` AS tDirectory_CallerID,
     tDirectory.`Category` AS tDirectory_Category,
     tDirectory.`Access` AS tDirectory_Access,
     tDirectory.`Status` AS tDirectory_Status
FROM
     `tDirectory` tDirectory LEFT OUTER JOIN `tTop10NosByCallType` tTop10NosByCallType ON tDirectory.`TelNo` = tTop10NosByCallType.`No`       and
   tTop10NosByCallType.Client = $P{Client}

WHERE
   `Invoice number` = $P{InvNo}
ORDER BY
     CallType DESC,
     CountByCallType DESC,
     DurationHHMM DESC

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38739922
Just a thought ... the above T-SQL uses * to return all columns, but you have two queries in your table.  So is the intent here to return all columns from both tables, or one, or the other?

   tDirectory.*    
   tTop10NosByCallType.*
   *  -- this is the same as saying both of the above
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:shaunwingin
ID: 38740149
Tx.
1. I replaced pasted your code above, but same issue - no data. Wasn't sure what you meant by the comment though.
2. The same issue with just a *
I need fields from both tables
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 38741076
Try with literal values for $P{InvNo} and $P{Client}. I think the issue may be that Windows does not like those for variable names.
0
 

Author Closing Comment

by:shaunwingin
ID: 38743068
Moved back to Linux...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question