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_TY PE,L.ASSIG NED_TO,T.D ESCRIPTION ,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_TY PE,L.ASSIG NED_TO,T.D ESCRIPTION ,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_TY PE,L.ASSIG NED_TO,T.D ESCRIPTION ,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
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,
,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,
,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,
,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
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/200 4#)
AND T.TICKETID = L.TICKETID
and l.workflowstageid = fs.workflowstageid
AND T.CUSTOMERID = C.CUSTOMERID
AND T.TROUBLE_TYPE ='Issue'
AND TRIM'.
Any ideas?
I didn't have much luck. This is what the error says...
In operator without () in query expression 'TRIM(T.DATE_OPENED)<=#10/
AND (TRIM(T.DATE_CLOSED) IS NULL OR TRIM (T.DATE_CLOSED)>#10/01/200
AND T.TICKETID = L.TICKETID
and l.workflowstageid = fs.workflowstageid
AND T.CUSTOMERID = C.CUSTOMERID
AND T.TROUBLE_TYPE ='Issue'
AND TRIM'.
Any ideas?
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_TICKE T
OSR_OTIS_OSS_TROUBLE_TICKE T_LOG
is that correct?
OSR_OTIS_OSS_CUSTOMER
OSR_OTIS_OSS_TROUBLE_TICKE
OSR_OTIS_OSS_TROUBLE_TICKE
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/2 004#
found another piece ...
FROM OSS_TROUBLE_TICKET AS T, OSS_TROUBLE_TICKET_LOG AS L, OSS_CUSTOMER AS C
Steve
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))
found another piece ...
FROM OSS_TROUBLE_TICKET AS T, OSS_TROUBLE_TICKET_LOG AS L, OSS_CUSTOMER AS C
Steve
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
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
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_TICKE T AS T,
OSR_OTIS_OSS_TROUBLE_TICKE T_LOG AS L,
OSR_OTIS_OSS_CUSTOMER AS C,
fsdc_flowassignments FS
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_TICKE
OSR_OTIS_OSS_TROUBLE_TICKE
OSR_OTIS_OSS_CUSTOMER AS C,
fsdc_flowassignments FS
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_TICKE T_LOG AS L
Steve
SELECT
L.WORKGROUP,
L.ASSIGNED_TO,
L.ACTION
FROM
OSR_OTIS_OSS_TROUBLE_TICKE
Steve
ASKER
Yup, that worked. Wether or not the information is right, I don't know. But it did return results.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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