Strange behavior using JOIN clause through linked server

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

Strange behavior using JOIN clause through linked server

Postby lcastro » Thu Jan 13, 2011 3:40 am

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 all
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
lcastro
 
Posts: 1
Joined: Wed Jan 12, 2011 1:39 am

Re: Strange behavior using JOIN clause through linked server

Postby Moderator » Sat Jan 15, 2011 5:51 pm

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
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm

Re: Strange behavior using JOIN clause through linked server

Postby mc_coder » Wed Nov 14, 2012 7:42 am

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
mc_coder
 
Posts: 7
Joined: Wed Nov 14, 2012 7:37 am

Re: Strange behavior using JOIN clause through linked server

Postby mc_coder » Wed Nov 14, 2012 11:37 am

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.* --> 100 rows
from a
join b on b.id = c.id --> 100 rows
join c on c.id = b.id --> 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
mc_coder
 
Posts: 7
Joined: Wed Nov 14, 2012 7:37 am

Re: Strange behavior using JOIN clause through linked server

Postby Moderator » Thu Nov 15, 2012 5:37 pm

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.
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 1 guest