Strange behavior using JOIN clause through linked server

  • lcastro
  • Topic Author
  • Visitor
  • Visitor
13 years 1 month ago #770 by lcastro
Hi everyone,

i am using PGNP 1.3.0.2218 licensed version. And the strange behavior occurs when i use JOIN clause against a PostgreSQL table. Let me explain:

i have two tables, one in SQL Server 2008 that contains call records for various ACD, and the other in PostgreSQL that contains the configuration parameters for an ACD.

when i want to contrast one table with the other is when i have the problem. Suppose this query:
Code:
SELECT substring(convert(varchar(24), acdcalls.call_time_start, 121), 12, 2) as hour, acdcalls.acd_id as acd_id, acdconf.acd_name as acd_name, acdconf.strategy as acd_strategy, count(acdcalls.conf_id) as gest_calls, count(distinct(acdcalls.conf_id)) as unique_call, sum(acdcalls.answered)as answ_calls, sum(acdcalls.abandoned)as aband_calls, sum(acdcalls.disconnected)as disc_calls, sum(acdcalls.overflowed)as over_calls, sum(acdcalls.other)as other_calls FROM acdcalls JOIN acdlinkedserver.voice.acd.nodes as acdconf ON acdconf.acd_id = acdcalls.acd_id WHERE acdcalls.acd_id in ('1904') AND acdcalls.service_id in ('11946') AND convert(varchar,acdcalls.call_time_start,111) >= '2011/01/01' AND convert(varchar,acdcalls.call_time_start,111) <='2011/01/11' AND convert(varchar,acdcalls.call_time_start,108) >= '00:00:00' AND convert(varchar,acdcalls.call_time_start,108) < '24:00:00' AND (acdcalls.not_ready=0 and acdcalls.busy=0) GROUP BY acdcalls.acd_id,substring(convert(varchar(24), acdcalls.call_time_start, 121), 12, 2) ORDER BY acd_name,hour

in this query i want to extract all records for a certain ACD and Service ID grouped by hours, and count and sum the different events for that records. If i run the query, it shows me only one row, for a determined hour, because it seems that the other grouped hours contains null records when contrast the data with the joined table.

This is impossible because i only contrast one ACD ID, and the configuration parameters for that ID exist on the joined table, in fact the result of the query is correct, but not complete.

I create a table in SQL Server 2008 with an exact copy of the joined table, with the configuration parameters. I change the joined table to this new one, and when i run the query, it works fine, and the result shows all the records within the grouped hours.

Any ideas for this erroneous behavior when using JOIN?

Thanks!

Lucas

Please Log in or Create an account to join the conversation.

More
13 years 1 month ago #771 by Moderator
We are trying to reproduce the issue with some test DDL and schema. We'll update the thread when more details are available.

Thank you

Please Log in or Create an account to join the conversation.

  • Visitor
  • Visitor
11 years 3 months ago #9817 by
Hi,

I've hit a similar problem with JOINS although it seems to me that the problem is with the AND clause. I can specify one clause on the where and query works okay if I add an AND then I get no rows. I doesn't seem to matter if I have the AND on the WHERE clause or if I specify it as part of the JOIN. The query works fine when run directly in postgres. Has their been an update to fix this?

Regards,

Martin

Please Log in or Create an account to join the conversation.

  • Visitor
  • Visitor
11 years 3 months ago #9818 by
Actually looking at this a bit more I'm wondering if it's a limitation of the demo version. I know it's limited to 100 rows or something but is that limit enforced on each of the joins?

select a.* --&gt; 100 rows
from a
join b on b.id = c.id --&gt; 100 rows
join c on c.id = b.id --&gt; 100 rows

I need to know if this is the case as I'm testing various queries we need to implement before deciding to purchase. I'm seeing some strange results when using where and join.

Regards,

Martin

Please Log in or Create an account to join the conversation.

More
11 years 3 months ago #9819 by Moderator
Yes, this is a limitation of the Trial version. The OLEDB provider limits the resulting rowset size to 100 rows. This limit is enforced to any query regardless to the joins.

Please Log in or Create an account to join the conversation.

Time to create page: 0.132 seconds
Powered by Kunena Forum