MS-SQL Link Server issue

More
5 years 11 months ago #11893 by BobKosko
BobKosko created the topic: MS-SQL Link Server issue
Hi all,

I am running the following query from MS-SQL 2012
select CI.cust_id,COD.FirstOrderDate,COD.LastOrderDate
from
[LINKEDSERVER].[database].[schema].[custinfo] CI,
(
SELECT cohead_cust_id,MIN(cohead_orderdate) as FirstOrderDate , MAX(cohead_orderdate) as LastOrderDate
FROM   [LINKEDSERVER].[database].[schema].cohead AS cohead
GROUP BY cohead_cust_id
) COD
WHERE CI.cust_id = COD.cohead_cust_id

This returns the error
OLE DB provider "PGNP" for linked server "OPENMFG-PROD" returned message "Undefined column name "Expr1004"".

An error occurred while preparing the query "SELECT "Tbl1001"."cust_id" "Col1026","Expr1004","Expr1005" FROM [snip]

Now if I run the inner query by itself, no error returns.
Notice the two Min Max functions in the inner query. The problems seems to lie here. If I do not include the two dates in the outer select, the query runs fine.

So the Min(date) and Max(date) seem to cause problems ONLY in a JOIN.

Anyone have any idea what is going on here?

Thank you

Bob

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

More
5 years 11 months ago #11894 by BobKosko
BobKosko replied the topic: Re: MS-SQL Link Server issue
Well this is embarassing but hopefully it will provide a future reference for a poor soul.

I changed the inner query to
SELECT cohead_cust_id,MIN(CONVERT(datetime,cohead_orderdate)) as FirstOrderDate , MAX(CONVERT(datetime,cohead_orderdate)) as LastOrderDate
FROM   [LINKEDSERVER].[database].[schema].cohead AS cohead
GROUP BY cohead_cust_id

and the full query ran.

The trick was to convert to Postgresql date column to a ms-sql datetime column.

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

More
5 years 10 months ago #12025 by Moderator
Moderator replied the topic: Re: MS-SQL Link Server issue
Thank you for letting us know! I added this issue to our "known issues" list. We'll see if a fix can be found.

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

Time to create page: 0.064 seconds
Powered by Kunena Forum