Strange behavior using JOIN clause through linked server

[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 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

[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

[mc_coder]

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]

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

[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.