Link to home
Start Free TrialLog in
Avatar of xp310
xp310

asked on

Convert DbVisualizer code into Access

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
Avatar of stevbe
stevbe

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
Avatar of xp310

ASKER

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?
Avatar of xp310

ASKER

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?
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
Avatar of xp310

ASKER

Um, I'm unclear as to what I should be doing?
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
Avatar of xp310

ASKER

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.
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

Avatar of xp310

ASKER

I added it but I still get the same error.
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
Avatar of xp310

ASKER

Yup, that worked.  Wether or not the information is right, I don't know.  But it did return results.
ASKER CERTIFIED SOLUTION
Avatar of stevbe
stevbe

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of xp310

ASKER

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