Solved

SQL Query stopped returning Data

Posted on 2013-01-03
6
493 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
Technology Partners: 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 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

737 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