MS Access to MS SQL - report requires single SQL statement

I am converting an existing MS Access report to MS SQL 2005.  The wild card here is that the reporting environment is CA's CleverPath Forest & Trees (F&T)  - which has several quirks in it's SQL interface.  But more of that below.  The first code snippet here is the original MS Access queries, they build on one another to get the final results in the last query.

The attached file is my converted SQL query, which is basically a 1-1 conversion using table varaibles.  This works perfectly in SQL Mgt Studio.  The problem is that F&T uses application/report components called 'views' which can be, among other things, database interfaces.  Each SQL interface 'view' can only accept a single SQL statement.  There can be mutiple views, but any variables defined inside are local in scope, so I can't pass my results between views unless they are part of the output.  

Adding to the fun, is that you must use the F&T sql language to connect F&T views.  And this, dear reader, does not support outer joins.  They recommend using UNIONs to simulate joins, as UNIONS are allowed as part of the single sql statement.

So, can the SQL be written as a single statement or several statements that can be combined with UNIONS?
Query 1 (Active P Tickets-new)

SELECT call_req.ref_num, call_req.persid, call_req.type, cr_stat.sym AS Code, call_req.summary, CvrtFromUnixTime([call_req]![last_mod_dt]) AS [Last Modified Date], call_req.description
FROM call_req LEFT JOIN cr_stat ON call_req.status=cr_stat.code
GROUP BY call_req.ref_num, call_req.persid, call_req.type, cr_stat.sym, call_req.summary, CvrtFromUnixTime([call_req]![last_mod_dt]), call_req.description, call_req.close_date
HAVING (((call_req.type)="p") AND ((call_req.close_date) Is Null));

Query 2 (Active P Tickets-new1)

SELECT [Active P Tickets log-new].ref_num, [Active P Tickets log-new].code, [Active P Tickets log-new].summary, [Active P Tickets log-new].[Last Modified Date], [Active P Tickets log-new].description, act_log.action_desc, act_log.description, CvrtFromUnixTime([act_log]![time_stamp]) AS [Activity Time]
FROM [Active P Tickets log-new] LEFT JOIN act_log ON [Active P Tickets log-new].persid = act_log.call_req_id
WHERE (((act_log.action_desc) Like "*status change*" And (act_log.action_desc) Not Like [act_log.description])) OR (((act_log.action_desc) Like "log a user comment"));

Query 3 (Active P Tickets-new1-5)

SELECT [Active P Tickets log-new1].ref_num, [Active P Tickets log-new1].code, [Active P Tickets log-new1].summary, [Active P Tickets log-new1].[Last Modified Date], [Active P Tickets log-new1].[Active P Tickets log-new].description, Max([Active P Tickets log-new1].[Activity Time]) AS [MaxOfActivity Time]
FROM [Active P Tickets log-new1]
GROUP BY [Active P Tickets log-new1].ref_num, [Active P Tickets log-new1].code, [Active P Tickets log-new1].summary, [Active P Tickets log-new1].[Last Modified Date], [Active P Tickets log-new1].[Active P Tickets log-new].description;

Query 4 (Active P Tickets-new3)

SELECT [Active P Tickets log-new].ref_num, [Active P Tickets log-new].Code, [Active P Tickets log-new].summary, [Active P Tickets log-new].description, [Active P Tickets log-new1].action_desc, [Active P Tickets log-new1].act_log.description, [Active P Tickets log-new1-5].[MaxOfActivity Time]
FROM [Active P Tickets log-new] LEFT JOIN ([Active P Tickets log-new1-5] LEFT JOIN [Active P Tickets log-new1] ON ([Active P Tickets log-new1-5].ref_num = [Active P Tickets log-new1].ref_num) AND ([Active P Tickets log-new1-5].[MaxOfActivity Time] = [Active P Tickets log-new1].[Activity Time])) ON [Active P Tickets log-new].ref_num = [Active P Tickets log-new1-5].ref_num
GROUP BY [Active P Tickets log-new].ref_num, [Active P Tickets log-new].Code, [Active P Tickets log-new].summary, [Active P Tickets log-new].description, [Active P Tickets log-new1].action_desc, [Active P Tickets log-new1].act_log.description, [Active P Tickets log-new1-5].[MaxOfActivity Time];

Open in new window

Access2SQL.txt
shadowbreezeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
please confirm
you are converting an access database to a sql server 2005 one?
the ca f&t product will connect to the sql server but can only use a single sql statement in its
"view" object when connecting to sql server?

several ca ft "views" can be combined to produce the report ?

so can ca ft interface to a sql server view?

0
LowfatspreadCommented:
which version of forest and trees?

is this still a supported CA product?
   if so what is its current name?
0
shadowbreezeAuthor Commented:
I am not converting _databases_, just queries against the same database using different interfaces.

Each F&T views can have one SQL statement, with UNIONs considered to be one statement.

Views can be executed in any (logical) sequence and can be combined to produce reports - however, you can't perform outer joins between views.

The F&T 'view' interface is separate from the 'ODBC interface', which is what I refer to as the 'SQL interface'.

This is F&T 7.11 and it is still supported for this customer.

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

LowfatspreadCommented:
my point is create the create as a view on the sql server 2005 database and point forrest and tress at a select on that..

the join is then not visible to forrest and trees at all.
0
shadowbreezeAuthor Commented:
A new SQL 2005 database view requires change control, and DBA review, etc.  That is a possible solution, but not preferred since I am 'assuming' it can be handled on the reporting front end.
0
LowfatspreadCommented:
any change will require dba review... involve them early it will cost less in the end...

you are porting the report to a new environment that will require dba review , you have already identified that different sql will be required to the existing app... likely new security arrangements will have to be implemented as wrll.

involve them know.
0
shadowbreezeAuthor Commented:
Thanks for the advice, but that is not the situation here.

The trending reports are run against a replicated instance of the database.  There are already existing F&T reports running there, so that is not new.  Each new report does not require DBA invlovement unless persisent DB objects are required to be created, like views.  For example, my converted example creates items in tempdb, which is allowed for the reporting account without further review.  We are porting the Access reports to the preferred reporting application.

So for now, I am looking for a single SQL statement or a re-write of the LEFT JOINS into mutiple statements that can be UNION'd.
0
BodestoneCommented:
I'm going to have to agree with the above. The only sensible way is to create a view and query that view in F&T. After all, that is efectiovely what you are doing with access. Each access query is acting as a view.

Maybe with a partial data set, more information and a lot of time it may be possible to hack together another way of doing it but it would be hideous and counter productive.

As an analogy you need to cross a river but cannot use a boat unless you get the keys to the boat shed and sign for a boat so instead you are asking for instructions on how to build a bridge out of McDonalds straws.

One shortcut I may suggest.
If you are allowed your own database on the same server that you can build views on and make those views reference the replicated database.



CREATE VIEW workaround AS

SELECT a.col1, a.col2, b.col2
FROM replicatedserver.dbo.table1 a
LEFT JOIN replicatedserver.dbo.table2 b
ON b.fkID = a.pkID

Open in new window

0
shadowbreezeAuthor Commented:
What is sensible in one culture is not in another.   Do you understand the comparison of 'preaching to the choir'? I do NOT need any further attempts at convincing me of the flaws in my requests.  We are agreed on creating views would work, but it is not the solution I am asking for!

If you need me to provide datasets, or more information, please simply ask for them - but only if you are intending on working the problem.  Please explain why the solution would be 'hideous and counter-productive'?  I assume you mean it would have some performance issues?    

Thanks for the suggested workaround, but it is also create new objects on the server.

Does anyone else have experience converting left joins into unions that can help?
0
BodestoneCommented:
Without even seeing sample data and queries I would have no idea how long it would take to work up a solution or even if one were possible. I'm certainly prepared to have a look and see if there is anything I can come up with as a short term answer for you while you work on the internal politics.

By hideous I mean that chances are it would be sluggish and more than likely look nasty and be harder to follow/maintain.

By counter productive it just seems that there is a core issue which needs to be addressed and that is the issue of having to make these workarounds in the first place.

I know you are aware of it I was just thinking of ways in which you might try and placate those imposing the restrictions on you by having your own database which you were allowed to create your views on.

I suppose you also don't have the option of having your own small SQL server which you are allowed to maintain and create a linked server on it to the other one?
0
shadowbreezeAuthor Commented:
Again, thanks for the alternatives.  I will work on a suitable dataset for upload.

Linked servers may be an acceptable alternative, since the only thing they have to maintain on their end is the credentials information for security - which they are doing now anyway.
0
BodestoneCommented:
I'm not sure if your query language supports IF EXISTS (until now I had never even considered the possibility that there would be a SQL varient that didn't support outer joins) but if so I have mocked up a very simple example using UNION to mimic the results of a LEFT JOIN:
/**
*  Create an populate some test tables to use for the example
**/
DECLARE	@parent TABLE(parentID INT , parentName NVARCHAR(50))
DECLARE	@child TABLE(childID INT, parentID INT, childName NVARCHAR(50))
INSERT	@parent
SELECT	1,'Animal' UNION
SELECT	2,'Mineral' UNION
SELECT	3,'Vegetable'
INSERT	@child
SELECT	1,1,'Mongoose' UNION
SELECT	2,1,'Lark' UNION
SELECT	3,3,'Larch' 

/**
*  Very basic UNION to give the same results as a left JOIN
*  Experiment with UNION vs UNION ALL for speed. UNION ALL does not sort or apply DISTINCT
*  UNION ALL should be quicker so unless DISTINCT rows are actually required then probably best to use that.
*  in this example you will see that the only visible difference is in the ordering.
**/
SELECT	p.parentID, p.parentName, c.childID, c.childName
FROM	@parent p
JOIN	@child c
	ON	c.parentID = p.ParentID
UNION ALL
SELECT	p.parentID, p.parentName, NULL, NULL
FROM	@parent p
WHERE	NOT EXISTS(SELECT 1 FROM @child WHERE parentID = p.parentID)

Open in new window

0
shadowbreezeAuthor Commented:
Ordering is not important.  OK, so now I will have to figure out how to combine the first left join against the act_log table - where the only common field is the foriegn key.

After that, I believe the rest of the queries can be modified to include all the sames field - necessary for the final set of unions.

I don't know why this isn't as clear in BOL.
0
BodestoneCommented:
OK, this has become soemwhat of a challenge for me and it is indeed that. My head keeps thinking at the wrong angle to what I want to achieve doing it this way but I've run up an example with 3 child tables, one that has no worws in the child and one that has a second level dependancy on a second level child table.

It is possibel but not at all desirable just because it hurts to think this way (though I do now have some new neural paths open they feel wrong and make me want to lick hats while standing on one leg humming American pie (OK, that actually seemed liek a good idea at the time ;)


/**
*  Create an populate some test tables to use for the example
**/
DECLARE @parent TABLE(parentID INT , parentName NVARCHAR(50))
DECLARE @child TABLE(childID INT, parentID INT, childName NVARCHAR(50)) --Animal
DECLARE @child2 TABLE(child2ID INT, parentID INT, child2Name NVARCHAR(50)) --vegetable
DECLARE @child3 TABLE(child3ID INT, parentID INT, child3Name NVARCHAR(50)) --Mineral
DECLARE @grandChild TABLE(grandChildID INT, childID INT, grandChildName NVARCHAR(50))
INSERT  @parent
SELECT  1,'Animal' UNION
SELECT  2,'Mineral' UNION
SELECT  3,'Vegetable'

INSERT  @child
SELECT  1,1,'Mammal' UNION
SELECT  2,1,'Insect' UNION
SELECT  3,1,'Fish' UNION
SELECT  4,1,'Bird' 

INSERT  @grandChild
SELECT  1,1,'Monkey' UNION
SELECT  2,1,'Aardvark' UNION
SELECT  3,1,'Platypus' UNION
SELECT  4,2,'Scarab' UNION
SELECT  5,3,'Guppy' 

INSERT  @child2
SELECT  1,3,'Grass' UNION
SELECT  2,3,'Shrub' UNION
SELECT  3,3,'Moss' 

/**
*  This is where it gets nasty
**/
--Need an extra wraper here to remove the repeat nulls on parent rows that have children (see boolean mare below)
SELECT  * FROM
(
    --parent with null as everythign else
    SELECT  p.parentID, p.parentName, NULL AS [childID], NULL AS [childName], NULL AS [grandChildID], NULL AS [grandChildName], NULL AS [child2ID], NULL AS [child2Name], NULL AS [child3ID], NULL AS [child3Name]
    
    FROM    @parent p
    UNION
    SELECT p.parentID, p.ParentName, cgu.childID, cgu.childName, cgu.grandChildID, cgu.grandChildName, NULL, NULL, NULL, NULL 
    FROM    @parent p
    JOIN    
    (
    
        SELECT  c.childID, c.parentID, c.childName, g.grandChildID, g.grandChildName
        FROM    @child c
        JOIN    @grandChild g
            ON  g.childID = c.childID
        UNION ALL
        SELECT  c.childID, c.parentID, c.childName, NULL, NULL
        FROM    @child c
        WHERE   NOT EXISTS(SELECT 1 FROM @grandChild WHERE childID = c.childID)
    ) cgu
        ON  cgu.parentID = p.parentID
    UNION
    SELECT  p.parentID, p.parentName, NULL, NULL, NULL, NULL , c2u.child2ID, c2u.child2Name, NULL, NULL
    FROM    @parent p
    JOIN    (
                SELECT c2.child2ID, p.parentID, c2.child2Name
                FROM    @parent p
                JOIN    @child2 c2
                    ON      c2.parentID = p.parentID
                UNION ALL           
                SELECT  NULL, p.parentID, NULL
                FROM    @parent p
                
            ) c2u
        ON c2u.parentID = p.parentID
    UNION
    SELECT  p.parentID, p.parentName, NULL, NULL, NULL, NULL, NULL, NULL, c3u.child3ID,c3u.child3Name
    FROM    @parent p
    JOIN    (
                SELECT c3.child3ID, p.parentID, c3.child3Name
                FROM    @parent p
                JOIN    @child3 c3
                    ON      c3.parentID = p.parentID
                UNION ALL           
                SELECT  NULL, p.parentID, NULL
                FROM    @parent p
                
            ) c3u
        ON c3u.parentID = p.parentID
        
        
) final
--possible boolian mare to eliminate the correct nulls as things change
WHERE   childID IS NOT NULL OR NOT EXISTS(SELECT 1 FROM @child WHERE parentID = final.parentID)
OR      child2ID IS NOT NULL OR NOT EXISTS(SELECT 1 FROM @child2 WHERE parentID = final.parentID)
OR      child3ID IS NOT NULL OR NOT EXISTS(SELECT 1 FROM @child3 WHERE parentID = final.parentID)

Open in new window

0
shadowbreezeAuthor Commented:
OK.  That _is_ ugly.  This will take me some time to digest.  I never considered 'SELECT'ing 'NULL's as fillers for missing fields.  But since I'm doing a GROUP BY at the end, those should collapse without an addition IFNULL() or COALESCE.

So now I need to apply this.

0
BodestoneCommented:
See what I meant now by hideous. Given you had query on query on query, indicating far more levels of sub unioned select than I have there........

Hoping my table aliases don't make it worse for you to undesstand Feel free to ask anythign about the query but the more left joins you throw in the more complex it will get, especially if they are child of child of child etc.
0
BodestoneCommented:
I am hoping though that you can get your own personal DB to direct to the reports to that can link to the source data. I mean, wouldn't you far prefer to maintain this:
/**
*  but with LEFT JOIN
**/
SELECT p.parentID, p.ParentName, c.childID, c.childName, g.grandChildID, g.grandChildName, c2.child2ID, c2.child2Name, c3.child3ID, c3.child3Name
FROM    @parent p
LEFT JOIN @child c 
    ON c.parentID = p.parentID
LEFT JOIN @grandChild g
    ON g.childID = c.childID
LEFT JOIN @child2 c2
    ON c2.parentID = p.parentID
LEFT JOIN @child3 c3 
    ON c3.parentID = p.parentID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BodestoneCommented:
Out of interest, I looked up stuff on forest and trees but it seems one of those apps where the help is not openly available for general search. Maybe you can do an ODBC passthrough query directly from the application as you can in Access to get round the limitations of JET SQL.
0
shadowbreezeAuthor Commented:
Hey, my Access2SQL code works perfectly in any other frontend except F&T, it has even less to maintain and is readable by the legacy Access users!

F&T was (is) a product CA acquired to fill the need for a 'dashboard' layer of reporting for C-level management.  It is extremely extensible as an application platform, but the quirks will drive you up a tree!

This all goes away in the current release as all reporting has been migrated to an embedded Buisness Objects (now SAP) XI Server.  So, I may feign ignorance until we get there in a few months.

0
BodestoneCommented:
I wasn't knocking your SQL, I was knocking the Jet SQL query language. It has it's own lacks such as not allowing more than one JOIN condition and not supporting a lot of the funky new stuff in SQL 2005 (CTEs, ROW_NUMBER() etc).

When I find it much easier to use them I use a pass through query to pass t-sql direct to the server rather than use Jet.
It was just an afterthought that something like that might exist in F&T.
0
shadowbreezeAuthor Commented:
No I didn't take it as a knock - I was agreeing with you.  I _have_ a solution that works everywhere else!  I am going to bring the subject up with management again this morning.
0
BodestoneCommented:
Good luck.
0
shadowbreezeAuthor Commented:
Thanks
0
shadowbreezeAuthor Commented:
The solutions' "complete" and "easy to follow" grading has nothing to do with the expert’s ability but to my inexperience in following!

I was able to use the expert’s comments to present to management the justification to use the preferred solution instead of the one they were originally wanting!

0
shadowbreezeAuthor Commented:
See comments under Accepted Solutions for the outcome!
0
BodestoneCommented:
Nice win!
I certainly count that as being more helpful that providing the workaround.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.