Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Loyal Listener
Posts: 17
Registered: ‎04-15-2009

Re: SQL Views as entities in 7.5p2

I am modifying my views to follow these requirements and have run into an additional issue.  Apparently you are no longer able to have embedded selct commands inside your views (this could be done since views were enabled until we hit SP2).  An example (see propblem area in red) would be:

 

 

CREATE VIEW SYSDBA.TESTVIEW (ACCOUNTID, TOTALCOUNT, TOTALASSETS)

AS

SELECT

A.ACCOUNTID,

SUM(P.ACTIVE_PARTICIPANTS) AS TOTALCOUNT,

SUM(P.TOTAL_PLAN_ASSETS) AS TOTALASSETS,

(SELECT TOP 1 USERID FROM HISTORY H WHERE H.ACCOUNTID = A.ACCOUNTID AND CREATEDATE = (SELECT MAX(H.CREATEDATE)) FROM

HISTORY WHERE H.ACCOUNTID = A.ACCOUNTID)) AS LASTUSERCONTACTID,

FROM SYSDBA.ACCOUNT AS A

LEFT OUTER JOIN SYSDBA.[PLAN] AS P ON A.ACCOUNTID = P.ACCOUNTID

WHERE (A.ACCOUNTID = 'A0FXUA100001')

GROUP BY A.ACCOUNTID

 

 

This is causing me a significant issue as I can't wait for a SP3 release to update my software that isran in multiple SLX installations.  I am having to rewrite my applications to work arround this issue created by SP2 unless there is a quick answer to this issue.

 

Ray Walther

TimeLinx Software 

(281) 257-0950

Ray A. Walther
TimeLinx Software, LLC

281-257-0950 (Direct)
Ray.Walther (Skype)
Bronze Super Contributor
Posts: 349
Registered: ‎03-24-2009

Re: SQL Views as entities in 7.5p2

This is a significant defect - SQL Views arent very valuable if we can not do sub selects. Sage please fix quickly.
Timmus Agersea
Black Moth CRM
Bronze Elite Contributor
Posts: 402
Registered: ‎07-13-2009

Re: SQL Views as entities in 7.5p2

I agree. I use these to create structures that are not otherwise available in the Saleslogix database.

 

Mike Boysen - Blytheco

www.blytheco.com | www.blythecodev.com | bMobile
Employee
Posts: 79
Registered: ‎06-25-2009

Re: SQL Views as entities in 7.5p2

This is indeed an issue. 

I had Hot Issue Defect # 1-72882 created for 7.5 SP2.  I will post again when it is available.

 

Paul Zeimet

Development Manager

Sage SalesLogix

Paul Zeimet
Development Manager
Infor CRM
Bronze Super Contributor
Posts: 349
Registered: ‎03-24-2009

Re: SQL Views as entities in 7.5p2

Thank you Paul!
Timmus Agersea
Black Moth CRM
Loyal Listener
Posts: 17
Registered: ‎04-15-2009

Re: SQL Views as entities in 7.5p2

Thanks Paul.  I am new to this process so any idea how long these take to get out?  I have to make some decisions in direction I take with my app.

 

Thanks for your help.

 

Ray Walther

Ray A. Walther
TimeLinx Software, LLC

281-257-0950 (Direct)
Ray.Walther (Skype)
Loyal Listener
Posts: 17
Registered: ‎04-15-2009

Re: SQL Views as entities in 7.5p2

While you are at it I found a couple of additional issues.  The follwing view works in the current SP2 environment:

 
CREATE VIEW TLXV_TIMESHEETDETAILTEST AS
SELECT  
    TLX_SERVICETASKID,
    ACCOUNTID    

FROM   TLX_SERVICETASK TASK

 

But this fails (note difference in red):


 
CREATE VIEW TLXV_TIMESHEETDETAILTEST AS
SELECT  
    TLX_SERVICETASKID AS TLXKEY,
    ACCOUNTID    

FROM   TLX_SERVICETASK TASK

I found this issue in trying to create a primary key for one of my views where the only way a unique key can be created is by concatenating 3 index fields and a converted date string to make it unique.  My guess is the primary key is assumed to always be char(12) which in this case is imposible.  Can we have an option that would allow us to not create a view that will not be available as a entity for AA and will not test for primary key?

 

I make extensive use of SQL views in my application and would be more than happy to test or discuss the changes you are making to the system.

 

Thanks for your help......

 

Ray

 

Ray Walther

281-257-0950

TimeLinx Software

Ray A. Walther
TimeLinx Software, LLC

281-257-0950 (Direct)
Ray.Walther (Skype)
Bronze Super Contributor
Posts: 349
Registered: ‎03-24-2009

Re: SQL Views as entities in 7.5p2

Ray, simply remove the "AS" and your aliasing should work fine. I also typically wrap my alias in single ticks as it makes reading the sql easier but I don't believe that is a requirement.

CREATE VIEW TLXV_TIMESHEETDETAILTEST AS
SELECT
TLX_SERVICETASKID 'TLXKEY',
ACCOUNTID
Timmus Agersea
Black Moth CRM
Loyal Listener
Posts: 17
Registered: ‎04-15-2009

Re: SQL Views as entities in 7.5p2

Tried both ways and neither one works.  Get the same does not contain the primary key error.  I have a lot of bruises on the forehead with this change....

 

Ray Walther

TimeLinx Software

281-257-0950

 

 

Ray A. Walther
TimeLinx Software, LLC

281-257-0950 (Direct)
Ray.Walther (Skype)
Bronze Elite Contributor
Posts: 402
Registered: ‎07-13-2009

Re: SQL Views as entities in 7.5p2

Unfortunately, a heavy reliance on external tools is akin to relying on custom forms in the web client. Too bad we don't have custom code snippet properties in the LAN client.
Mike Boysen - Blytheco

www.blytheco.com | www.blythecodev.com | bMobile