Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

joining to sql during openquery call to MySql Expand / Collapse
Author
Message
Posted Saturday, December 29, 2012 8:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:44 AM
Points: 46, Visits: 274
i am querying a MySql view for data, but need to join to a sql table for three additional fields.

SET IDENTITY_INSERT CALLSNEW ON
INSERT INTO [cas].[dbo].[CALLSNEW]
([calls_key]
,[ext])

SELECT * FROM OPENQUERY(PHONEDB,'SELECT calls_key, ext FROM calls')
Where calltype IN ( 'from-internal', 'ext-local', 'ext-queues', 'ext-grou')


but i need to join to a linked SQL server as well to get a few more fields. I could use suggestions on how to do that.

SELECT dept = dbo.EMP.dept, fname = dbo.EMP.fname, lname = dbo.EMP.lname, empnum = dbo.EMP.empnum
FROM linkedserver.dbo.EMP
WHERE dbo.EMP.ext = ext
AND dbo.EMP.active = 1



thanks for your time.
Post #1401170
Posted Saturday, December 29, 2012 8:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 22,495, Visits: 30,203
TryingToLearn (12/29/2012)
i am querying a MySql view for data, but need to join to a sql table for three additional fields.

SET IDENTITY_INSERT CALLSNEW ON
INSERT INTO [cas].[dbo].[CALLSNEW]
([calls_key]
,[ext])

SELECT * FROM OPENQUERY(PHONEDB,'SELECT calls_key, ext FROM calls')
Where calltype IN ( 'from-internal', 'ext-local', 'ext-queues', 'ext-grou')


but i need to join to a linked SQL server as well to get a few more fields. I could use suggestions on how to do that.

SELECT dept = dbo.EMP.dept, fname = dbo.EMP.fname, lname = dbo.EMP.lname, empnum = dbo.EMP.empnum
FROM linkedserver.dbo.EMP
WHERE dbo.EMP.ext = ext
AND dbo.EMP.active = 1



thanks for your time.


Need more information. From just what you have posted, not sure how these two tables are related and could be joned together.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1401172
Posted Saturday, December 29, 2012 8:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:44 AM
Points: 46, Visits: 274
Thanks for replying.

i am pulling ext from the MySQL view which in also in the EMP SQL table.

Based on the EXT i need to get dept, fname, lname, empum from thenSQL table

Post #1401174
Posted Saturday, December 29, 2012 9:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 22,495, Visits: 30,203
I would say something like this should work. I don't have a linked server of any kind on my system so I can't test it:


SELECT
dept = e.dept,
fname = e.fname,
lname = e.lname,
empnum = e.empnum ,
calls_key = oq.calls_key,
ext = oq.ext
FROM
linkedserver.dbo.EMP e
inner join OPENQUERY(PHONEDB,'SELECT calls_key, ext FROM calls') oq
on (e.ext = oq.ext)
WHERE
e.ext = ext
AND e.active = 1
and oq.calltype IN ( 'from-internal', 'ext-local', 'ext-queues', 'ext-grou')



Also, please note the use of the table aliases especially on the linked server. The use of 3 and 4 part names in the select list is a deprecated feature and should not be used. You should use table alaises in the from clause.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1401179
Posted Saturday, December 29, 2012 10:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:44 AM
Points: 46, Visits: 274
Very grateful for your input!! thank you.
Post #1401183
Posted Saturday, December 29, 2012 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:44 AM
Points: 46, Visits: 274
Sorry, The EMP table is not on a linked server, it is a SQL table in the Local database..

does that explain the error ' ambiguous column name 'ext'
Post #1401187
Posted Saturday, December 29, 2012 10:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:44 AM
Points: 46, Visits: 274
i figured it out...Thank you for your assistance...
Post #1401188
Posted Saturday, December 29, 2012 11:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:44 AM
Points: 46, Visits: 274
I now need to turn this into a insert statement, calls_key a the primary key and i'm getting a new error on that issue. Can you help on that?

Thank you

SET IDENTITY_INSERT CALLSNEW ON
--INSERT INTO [CALLSNEW]
INSERT INTO [cas].[dbo].[CALLSNEW]

SELECT
calls_key = oq.calls_key,
ext = oq.ext,
calltype = oq.calltype,
dept = e.dept,
fname = e.fname,
lname = e.lname,
empnum = e.empnum
FROM
[EMP] e
inner join OPENQUERY(PHONEDB,'SELECT calls_key, ext, calltype FROM calls') oq
on (e.ext = oq.ext)
WHERE
e.ext = oq.ext
AND e.active = 1
and oq.calltype IN ( 'from-internal', 'ext-local', 'ext-queues', 'ext-group')
Post #1401196
Posted Saturday, December 29, 2012 4:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 22,495, Visits: 30,203
TryingToLearn (12/29/2012)
I now need to turn this into a insert statement, calls_key a the primary key and i'm getting a new error on that issue. Can you help on that?

Thank you

SET IDENTITY_INSERT CALLSNEW ON
--INSERT INTO [CALLSNEW]
INSERT INTO [cas].[dbo].[CALLSNEW]

SELECT
calls_key = oq.calls_key,
ext = oq.ext,
calltype = oq.calltype,
dept = e.dept,
fname = e.fname,
lname = e.lname,
empnum = e.empnum
FROM
[EMP] e
inner join OPENQUERY(PHONEDB,'SELECT calls_key, ext, calltype FROM calls') oq
on (e.ext = oq.ext)
WHERE
e.ext = oq.ext
AND e.active = 1
and oq.calltype IN ( 'from-internal', 'ext-local', 'ext-queues', 'ext-group')


Nope, I have no idea what the problem is with your code. My crystal ball has been in the shop for years, and I don't expect to get it back anytime soon. Also, the force may be strong in this one, but it isn't that strong.

What all the sarcasm is saying is that you haven't provided enough information for anyone not looking over your shoulder to be able to help you.

Please read the first article I reference below in my signature block. It will walk you through everything you need to post to get the best possible answers to your questions.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1401210
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse