Solved

SQL Query stopped returning Data

Posted on 2013-01-03
6
488 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 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Creating and Managing Databases with phpMyAdmin in cPanel.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now