Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

MS Access to MS SQL - report requires single SQL statement

Avatar of Shadow Breeze
Shadow BreezeFlag for United States of America asked on
Microsoft AccessMicrosoft SQL Server 2005SQL
26 Comments1 Solution580 ViewsLast Modified:
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];
Access2SQL.txt
ASKER CERTIFIED SOLUTION
Avatar of Bodestone
BodestoneFlag of United Kingdom of Great Britain and Northern Ireland image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 26 Comments.
See Answers