i am using PGNP 220.127.116.118 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
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,
JOIN acdlinkedserver.voice.acd.nodes as acdconf
ON acdconf.acd_id = acdcalls.acd_id
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)
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?