Solved

SQL Query stopped returning Data

Posted on 2013-01-03
6
492 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 65

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 59

Accepted Solution

by:
Kevin Cross earned 500 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

735 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