The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
External Query
Posted: 12 Apr 07 1:27 PM
|
fiogf49gjkf0d Hi I have SQL query from SQL Query analizer and I need to execute it from SLX because is a part of my application and is a query from external DB what can I do? I need to create tables and views I Have de query created and working on my Query Analizer but I don´t know How to create or run the query from SLX Script this is my query:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CT_CamisaConnect]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CT_CamisaConnect]
GO
CREATE TABLE [dbo].[CT_CamisaConnect] (
[TableName] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UniqueID] [int] NOT NULL ,
[EntityID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModifyDate] [datetime] NULL ,
[ModifyUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LinkFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DeleteFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CT_vwNewSLCDPM]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[CT_vwNewSLCDPM]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.CT_vwNewSLCDPM
AS
SELECT *
FROM dbo.slcdpm S1 LEFT OUTER JOIN
dbo.CT_CamisaConnect CT ON CT.TableName = 'SLCDPM' AND CT.UniqueID = S1.identity_column
WHERE
CT.UniqueID IS NULL
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CT_vwNewSYADDR]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[CT_vwNewSYADDR]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.CT_vwNewSYADDR
AS
SELECT *
FROM dbo.syaddr S1 LEFT OUTER JOIN
dbo.CT_CamisaConnect CT ON CT.TableName = 'SYADDR' AND CT.UniqueID = S1.identity_column
WHERE (CT.UniqueID IS NULL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CT_vwNewSYPHON]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[CT_vwNewSYPHON]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.CT_vwNewSYPHON
AS
SELECT *
FROM dbo.syphon S1 LEFT OUTER JOIN
dbo.CT_CamisaConnect CT ON CT.TableName = 'SYPHON' AND CT.UniqueID = S1.identity_column
WHERE (CT.UniqueID IS NULL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CT_vwNewAPVEND]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[CT_vwNewAPVEND]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.CT_vwNewAPVEND
AS
SELECT *
FROM dbo.apvend A1 LEFT OUTER JOIN
dbo.CT_CamisaConnect CT ON CT.TableName = 'APVEND' AND CT.UniqueID = A1.identity_column
WHERE (CT.UniqueID IS NULL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CT_vwNewINMAST]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[CT_vwNewINMAST]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.CT_vwNewINMAST
AS
SELECT *
FROM dbo.inmast IN1 LEFT OUTER JOIN
dbo.CT_CamisaConnect CT ON CT.TableName = 'INMAST' AND CT.UniqueID = IN1.identity_column
WHERE (CT.UniqueID IS NULL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CT_vwNewSOMAST]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[CT_vwNewSOMAST]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.CT_vwNewSOMAST
AS
SELECT *
FROM dbo.somast S1 LEFT OUTER JOIN
dbo.CT_CamisaConnect CT ON CT.TableName = 'SOMAST' AND CT.UniqueID = S1.identity_column
WHERE
CT.UniqueID IS NULL
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CT_vwNewQTMAST]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[CT_vwNewQTMast]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.CT_vwNewQTMAST
AS
SELECT *
FROM dbo.qtmast S1 LEFT OUTER JOIN
dbo.CT_CamisaConnect CT ON CT.TableName = 'QTMAST' AND CT.UniqueID = S1.identity_column
WHERE
CT.UniqueID IS NULL
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO |
|
|
|
Re: External Query
Posted: 13 Apr 07 6:28 AM
|
The provider does NOT support TSQL..
You need to build up a VBScript and do this properly...
SalesLogix 101
--
rjl |
|
|
|
Re: External Query
Posted: 19 Apr 07 10:29 AM
|
Ramses,
Two questions:
Do you have Remote users?
What version of SLX are you using?
Carla |
|
|
| |
|
Re: External Query
Posted: 19 Apr 07 4:26 PM
|
fiogf49gjkf0d Then it is your lucky day.
Because you haven't any remotes, you need not worry about the push out of tables.
Because you are running v7, you have the capability of 'enabling your custom views and tables'
In your dev are try this:
Run your scripts.
Open Architect
Open Tools | Database Manager
Find & Select your new items.
In the properties pane; Click the Enable CheckBox
Viola. You're done.
Carla
|
|
|
|
Re: External Query
Posted: 11 May 07 11:52 AM
|
fiogf49gjkf0d Thanks for the instructions Carla. I need to display a group under the account which requires multiple joins to a table. I have a table that links the account with employees based on responsibilities. There are over 100 items which change regularly, so we opted to create a 1:many table vs. constantly adding and removing fields for each item. Now I need a grid that can join the account to the new table where the title is analyst and another join to the new table where the title is strategist and so on.
SLX Joins do not allow more than one join between tables on the same field. I created a SQL view to display the information including the accountid. Then I followed the instructions to enable the view in the architect. After refreshing the client I created a join between the account table and the new view, but the view and its fields are not available to add to the layout in a group. What else do I need to do?  |
|
|
|
Re: External Query
Posted: 11 May 07 1:16 PM
|
fiogf49gjkf0d OK - just double checking a couple of areas.
When you went into DBManager, you 'Enabled' the new view, correct? And it turned from a Grey icon to a colored icon?
The join you created is 'Allowed'?
When you are in the QBE (via clicking the SQL property from your grid) : Are you able to right click on accountid and create a local join to your new view?
Not sure what the issue could be. I can't reproduce it on this end. 
c |
|
|
|
Re: External Query
Posted: 11 May 07 3:21 PM
|
fiogf49gjkf0d Thanks again for the input. I am learning a great deal from this site. I changed the join's visibility to always, and it worked. Finally, we now have a way to create dynamic views that can actually group by something and sum or count. |
|
|
|
Re: External Query
Posted: 11 May 07 5:25 PM
|
fiogf49gjkf0d Good for you!!!! I am constantly learning from the pros on this site as well.
A Very Helpful Community. 
c |
|
|
|