[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Convert DbVisualizer code into Access

Posted on 2004-11-10
13
Medium Priority
?
741 Views
Last Modified: 2008-02-01
Hello,

How can I convert this code from DbVisualizer, into Access?  (Assuming this is even possible...!!!)  I don't know anything about this code, so my only hope is something that will convert it for me.

SELECT L.WORKGROUP,T.DATE_OPENED,T.TICKETID,C.NAME,T.TROUBLE_TYPE,L.ASSIGNED_TO,T.DESCRIPTION,T.TROUBLE_CODE
,l.action, fs.status, fs.dateassigned, fs.assignedtousername
FROM OSS_TROUBLE_TICKET T, OSS_TROUBLE_TICKET_LOG L, OSS_CUSTOMER C
,fsdc_flowassignments fs
WHERE TRUNC(T.DATE_OPENED) <= '01-oct-04'
AND (TRUNC (T.DATE_CLOSED) IS NULL OR TRUNC (T.DATE_CLOSED) > '01-oct-04')
AND T.TICKETID = L.TICKETID
and l.workflowstageid = fs.workflowstageid
AND T.CUSTOMERID = C.CUSTOMERID
AND T.TROUBLE_TYPE ='Issue'
AND TRUNC(L.STARTED) <= '01-oct-04'
and t.hasticketflow = 'Y'  --new style
and fs.status = 'Live' --Active in what stage
AND (TRUNC (L.ACTION_DATE) IS NULL OR TRUNC (L.ACTION_DATE) > '01-oct-04')
UNION
SELECT L.WORKGROUP,T.DATE_OPENED,T.TICKETID,C.NAME,T.TROUBLE_TYPE,L.ASSIGNED_TO,T.DESCRIPTION,T.TROUBLE_CODE
,l.action, fs.status, fs.dateassigned, fs.assignedtousername
FROM OSS_TROUBLE_TICKET T, OSS_TROUBLE_TICKET_LOG L, OSS_CUSTOMER C
,fsdc_flowassignments fs
WHERE TRUNC(T.DATE_OPENED) <= '01-oct-04'
AND (TRUNC (T.DATE_CLOSED) IS NULL OR TRUNC (T.DATE_CLOSED) > '01-oct-04')
AND T.TICKETID = L.TICKETID
and l.workflowstageid = fs.workflowstageid (+)
AND T.CUSTOMERID = C.CUSTOMERID
AND T.TROUBLE_TYPE ='Issue'
AND TRUNC(L.STARTED) <= '01-oct-04'
and t.hasticketflow = 'Y'  --new style tickets
and l.assigned_to = 'Unassigned' --belongs to unassigned
and l.action = 'Assessment'  --sitting in assessment  
and fs.status = 'Live' --Active in what stage
UNION
SELECT L.WORKGROUP,T.DATE_OPENED,T.TICKETID,C.NAME,T.TROUBLE_TYPE,L.ASSIGNED_TO,T.DESCRIPTION,T.TROUBLE_CODE
,l.action, NULL "ASSIGNMENT_STATUS", sysdate "DATEASSIGNED", null "ASSIGNEDTOUSERNAME"
FROM OSS_TROUBLE_TICKET T, OSS_TROUBLE_TICKET_LOG L, OSS_CUSTOMER C
WHERE TRUNC(T.DATE_OPENED) <= '01-oct-04'
AND (TRUNC (T.DATE_CLOSED) IS NULL OR TRUNC (T.DATE_CLOSED) > '01-oct-04')
AND T.TICKETID = L.TICKETID
AND T.CUSTOMERID = C.CUSTOMERID
and t.hasticketflow = 'N' --old style tickets
AND T.TROUBLE_TYPE ='Issue'
AND TRUNC(L.STARTED) <= '01-oct-04'
AND (TRUNC (L.ACTION_DATE) IS NULL OR TRUNC (L.ACTION_DATE) > '01-oct-04')
ORDER BY 2 ASC

Thank you,
Jay
0
Comment
Question by:xp310
  • 7
  • 6
13 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 12547084
change TRUNC to TRIM
use US format  and wrap in # for dates ... from '01-oct-04' to #10/01/2004#
remove all comments like ...     --new style
NULL "ASSIGNMENT_STATUS"   ...   NULL AS "ASSIGNMENT_STATUS"
sysdate "DATEASSIGNED"         ...    Now() AS "DATEASSIGNED"
null "ASSIGNEDTOUSERNAME"   ... NULL AS "ASSIGNEDTOUSERNAME"

see if that gets you closer, if you [paste it into an Access query it should tell you where it has trouble.

Steve
0
 

Author Comment

by:xp310
ID: 12547246
Steve,

I didn't have much luck.  This is what the error says...

In operator without () in query expression 'TRIM(T.DATE_OPENED)<=#10/01/2004#
AND (TRIM(T.DATE_CLOSED) IS NULL OR TRIM (T.DATE_CLOSED)>#10/01/2004#)
AND T.TICKETID = L.TICKETID
and l.workflowstageid = fs.workflowstageid
AND T.CUSTOMERID = C.CUSTOMERID
AND T.TROUBLE_TYPE ='Issue'
AND TRIM'.

Any ideas?
0
 

Author Comment

by:xp310
ID: 12547261
On thing I'm not entirely sure of, is if I have all the tables I need.  I am linking to:

OSR_OTIS_OSS_CUSTOMER
OSR_OTIS_OSS_TROUBLE_TICKET
OSR_OTIS_OSS_TROUBLE_TICKET_LOG

is that correct?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 39

Expert Comment

by:stevbe
ID: 12547371
Looks like you have the correct tables ...
I would break it up into the pieces between the UNION statements to make it easier to debug each part


try specifically converting to a date ...
    CDate(TRIM(T.DATE_OPENED))<=#10/01/2004#

found another piece ...
   FROM OSS_TROUBLE_TICKET AS T, OSS_TROUBLE_TICKET_LOG AS L, OSS_CUSTOMER AS C

Steve
0
 

Author Comment

by:xp310
ID: 12547401
Um, I'm unclear as to what I should be doing?
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12547570
make a new query and put the SQL in ...

start with getting the tables and fields

SELECT
  L.WORKGROUP,
  T.DATE_OPENED,
  T.TICKETID,
  C.NAME,
  T.TROUBLE_TYPE,
  L.ASSIGNED_TO,
  T.DESCRIPTION,
  T.TROUBLE_CODE,
  L.ACTION,
  FS.status,
  FS.dateassigned,
  FS.assignedtousername
FROM
  OSS_TROUBLE_TICKET AS T,
  OSS_TROUBLE_TICKET_LOG AS L,
  OSS_CUSTOMER AS C,
  fsdc_flowassignments FS

Steve
0
 

Author Comment

by:xp310
ID: 12547623
The Microsoft Jet database engine cannot find the input table or query 'OSS_TROUBLE_TICKET'.  Make sure it exists and that its name is spelled correctly.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12547686
you need anlother linked table ...   fsdc_flowassignments

SELECT
  L.WORKGROUP,
  T.DATE_OPENED,
  T.TICKETID,
  C.NAME,
  T.TROUBLE_TYPE,
  L.ASSIGNED_TO,
  T.DESCRIPTION,
  T.TROUBLE_CODE,
  L.ACTION,
  FS.status,
  FS.dateassigned,
  FS.assignedtousername
FROM
  OSR_OTIS_OSS_TROUBLE_TICKET AS T,
  OSR_OTIS_OSS_TROUBLE_TICKET_LOG AS L,
  OSR_OTIS_OSS_CUSTOMER AS C,
  fsdc_flowassignments FS

0
 

Author Comment

by:xp310
ID: 12547883
I added it but I still get the same error.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12548064
ok ... lets try to get 1 table to work ...

SELECT
  L.WORKGROUP,
  L.ASSIGNED_TO,
  L.ACTION
FROM
  OSR_OTIS_OSS_TROUBLE_TICKET_LOG AS L

Steve
0
 

Author Comment

by:xp310
ID: 12548623
Yup, that worked.  Wether or not the information is right, I don't know.  But it did return results.
0
 
LVL 39

Accepted Solution

by:
stevbe earned 2000 total points
ID: 12552907
have you tried adding the tables and their respective fields back into the SQL to see what part(s) are goofy?
0
 

Author Comment

by:xp310
ID: 12584487
stevbe,

I'm going to close out this question as I was able to excalate this issue with a department here & they will be recoding the query.  Thank you for your help :)

Jay
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question