turning SQL*Plus Code into Access Code

[mjstar]

I'm looking for a way to turn an SQL*Plus Code into an Access code. The problem is
how to nest the outer joins. Here are the original code and the beginning of the new Access one :

**************** SQL*Plus Code ********************

SELECT machine,nvl (alpha1,'AUTRE') status,
LEAST (nextdate,TO_DATE ('24-AUG-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')) PPNextEvt,
GREATEST (evtdate,TO_DATE ('30-JUL-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')) PGEvt,
status1
FROM ntc_events ev, gts_tables gt
WHERE ev.fac = 'FAB3'
AND nextdate >= TO_DATE ('30-JUL-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')
AND evtdate < TO_DATE ('24-AUG-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')
AND gt.fac(+) = 'FAB3'
AND tname(+) = 'N720_MODELE3'
AND key2a(+) = status1
AND machine like 'AMT30%'
ORDER BY machine, nextdate;

*************** Access Code ***********************

SELECT ev.MACHINE, IIf(IsNull([ALPHA1]),&quot;AUTRE&quot;,[ALPHA1]) AS Status,
IIf([NEXTDATE]>#8/24/01 6:00:00 AM#,#8/24/01 6:00:00 AM#,[NextDate]) AS PPNextEvt,
IIf([EVTDATE]<#7/30/01 6:00:00 AM#,#7/30/01 6:00:00 AM#,[evtdate]) AS PGEvt,
ev.STATUS1
FROM workstream_ntc_events AS ev , wsref_gts_tables AS gt
WHERE (((ev.MACHINE) Like 'AMT30*')
AND ((ev.NEXTDATE)>=#7/30/01 6:00:00 AM#)
AND ((ev.FAC)='FAB3')
AND ((ev.EVTDATE)<#8/24/01 6:00:00 AM#)
AND ((gt.FAC)='FAB3')
AND ((gt.TNAME)='N720_MODELE3')
AND gt.KEY2A = ev.STATUS1)
ORDER BY ev.MACHINE, ev.NEXTDATE;

PS: I've tried unsuccessfully to use LEFT and RIGHT JOINs...

 

[tlbroadbent]

Try this syntax for the Left Join. Note that you must also test for the NULL condition when including criteria for columns in the RIGHT table. I'm not familiar with the SQL*Plus syntax but have assumed that gts_tables belongs on the LEFT side of the join.

FROM wsref_gts_tables AS gt
LEFT JOIN workstream_ntc_events AS ev
ON gt.KEY2A = ev.STATUS1
WHERE (ev.MACHINE Like 'AMT30*' OR ev.MACHINE Is Null)
AND (ev.NEXTDATE>=#7/30/01 6:00:00 AM# OR ev.NEXTDATE Is Null)
AND (ev.FAC='FAB3' OR ev.FAC Is Null)
AND (ev.EVTDATE<#8/24/01 6:00:00 AM# OR ev.EVTDATE Is Null)
AND (gt.FAC='FAB3')
AND (gt.TNAME='N720_MODELE3') Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[mjstar]

Thanks Terry,

I've tried your solution, but it didn't work. The thing is that gts_tables and ntc_events are linked tables in Access. They are from an Oracle Database. And I'm just wondering wether Access deletes each Null field. This would be ridiculous.
I'm telling you this 'cause I tried to look for Null values for gt.KEY2A, and it returned me nothing...

I've to try something one more time

 

[GBall]

If you download (or have ) a copy of TOAD, it'll do it for you.

rgds,
Graham

 

[mjstar]

Hi Gball,

What is TOAD ?

 

[GBall]

T.O.A.D - Tool for Oracle Application Development.
All you need - check out

http://www.quest.com

for an evaluation copy. I think there's even a free version.

 

[mjstar]

Hi guys,

I think I found out what the problem was. It's an Access bug (saw it on Microsoft site). The example is:

SELECT Employees.LastName, Employees.FirstName, Orders.OrderId
From Employees LEFT OUTER JOIN Orders
ON ((Employees.EmployeeId = Orders.EmployeeID) and (Orders.ShipCity = 'Warszawa))

And Access considers things to be like:

SELECT Employees.LastName, Employees.FirstName, Orders.OrderId
From Employees INNER JOIN Orders
ON (Employees.EmployeeId = Orders.EmployeeID)
WHERE (Orders.ShipCity = 'Warszawa)

Microsoft says we have to break the query into two steps, eg:

SELECT Employees.LastName, Employees.FirstName, Orders.OrderId
From Employees LEFT JOIN
[SELECT * FROM Orders WHERE ShipCity = 'Warszawa'].
AS Orders
ON Employees.EmployeeId = Orders.EmployeeID

The thing is that the original code in SQL*Plus is:


SELECT machine,nvl (alpha1,'AUTRE') status,
LEAST (nextdate,TO_DATE ('24-AUG-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')) PPNextEvt,
GREATEST (evtdate,TO_DATE ('30-JUL-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')) PGEvt,
status1
FROM ntc_events ev, gts_tables gt
WHERE ev.fac = 'FAB3'
AND nextdate >= TO_DATE ('30-JUL-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')
AND evtdate < TO_DATE ('24-AUG-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')
AND gt.fac(+) = 'FAB3'
AND tname(+) = 'N720_MODELE3'
AND key2a(+) = status1
AND machine like 'AMT30%'
ORDER BY machine, nextdate;

Now, I've turned it into Access code like this :
ELECT ev.MACHINE, IIf(IsNull(gt.ALPHA1),&quot;AUTRE&quot;,gt.ALPHA1) AS Status,
IIf(ev.NEXTDATE>#8/24/01 6:00:00 AM#,#8/24/01 6:00:00 AM#,ev.NextDate) AS PPNextEvt,
IIf(ev.EVTDATE<#7/30/01 6:00:00 AM#,#7/30/01 6:00:00 AM#,ev.EVTDATE) AS PGEvt,
ev.STATUS1
FROM wsref_gts_tables AS gt
LEFT JOIN
[SELECT *
FROM workstream_ntc_events AS ev
WHERE (ev.MACHINE Like 'AMT30*')
AND (ev.NEXTDATE>=#7/30/01 6:00:00 AM#)
AND (ev.FAC='FAB3')
AND (ev.EVTDATE<#8/24/01 6:00:00 AM# )]. AS ev
ON ((gt.KEY2A = ev.STATUS1)
)
ORDER BY ev.MACHINE, ev.NEXTDATE;

But I don't know how to put the conditions:
... AND ((gt.FAC)='FAB3')
AND ((gt.TNAME)='N720_MODELE3') ...

Thank you for your help !!!

 

[GBall]

Mjstar, I thought I'd run your code through Toad and see what we got.
Unfortunately, it was this:-
SQL = &quot; SELECT machine,NVL (alpha1,'AUTRE') status,&quot;
SQL = SQL & &quot; LEAST (nextdate,TO_DATE ('24-AUG-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')) PPNextEvt,&quot;
SQL = SQL & &quot; GREATEST (evtdate,TO_DATE ('30-JUL-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')) PGEvt,&quot;
SQL = SQL & &quot; status1&quot;
SQL = SQL & &quot; FROM ntc_events ev, gts_tables gt&quot;
SQL = SQL & &quot; WHERE ev.fac = 'FAB3'&quot;
SQL = SQL & &quot; AND nextdate >= TO_DATE ('30-JUL-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')&quot;
SQL = SQL & &quot; AND evtdate < TO_DATE ('24-AUG-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')&quot;
SQL = SQL & &quot; AND gt.fac(+) = 'FAB3'&quot;
SQL = SQL & &quot; AND tname(+) = 'N720_MODELE3'&quot;
SQL = SQL & &quot; AND key2a(+) = status1&quot;
SQL = SQL & &quot; AND machine LIKE 'AMT30%'&quot;
SQL = SQL & &quot; ORDER BY machine, nextdate;&quot;

First time I've used this feature, sorry for misleading you.
Graham

 

[mjstar]

It's okay GBall

 

[mjstar]

Hi,

Still have a problem with Access. The original code in SQL*Plus is :

SELECT machine,nvl (alpha1,'AUTRE') status,
LEAST (nextdate,TO_DATE ('24-AUG-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')) PPNextEvt,
GREATEST (evtdate,TO_DATE ('30-JUL-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')) PGEvt,
status1
FROM ntc_events ev, gts_tables gt
WHERE ev.fac = 'FAB3'
AND nextdate >= TO_DATE ('30-JUL-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')
AND evtdate < TO_DATE ('24-AUG-2001 06:00:00','DD-MON-YYYY HH24:MI:SS')
AND gt.fac(+) = 'FAB3'
AND tname(+) = 'N720_MODELE3'
AND key2a(+) = status1
AND machine like 'AMT30%'
ORDER BY machine, nextdate;

Now, I've turned it into Access code like this :
SELECT ev.MACHINE, IIf(IsNull(gt.ALPHA1),&quot;AUTRE&quot;,gt.ALPHA1) AS Status,
IIf(ev.NEXTDATE>#8/24/01 6:00:00 AM#,#8/24/01 6:00:00 AM#,ev.NextDate) AS PPNextEvt,
IIf(ev.EVTDATE<#7/30/01 6:00:00 AM#,#7/30/01 6:00:00 AM#,ev.EVTDATE) AS PGEvt,
ev.STATUS1
FROM wsref_gts_tables AS gt
LEFT JOIN
[SELECT *
FROM workstream_ntc_events AS ev
WHERE (ev.MACHINE Like 'AMT30*')
AND (ev.NEXTDATE>=#7/30/01 6:00:00 AM#)
AND (ev.FAC='FAB3')
AND (ev.EVTDATE<#8/24/01 6:00:00 AM# )]. AS ev
ON ((gt.KEY2A = ev.STATUS1)
)
ORDER BY ev.MACHINE, ev.NEXTDATE;

But I don't know how to put the conditions:
... AND (gt.FAC(+)='FAB3')
AND (gt.TNAME(+)='N720_MODELE3') ...

Thanx for your help !!!

 

[tlbroadbent]

Add a Where clause before the final Order By clause.

Where gt.FAC=&quot;FAB3&quot;
AND gt.TNAME=&quot;N720_MODELE3&quot;
ORDER BY ev.MACHINE, ev.NEXTDATE;

Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[mjstar]

I tried your tip Terry, but the result is still wrong...
I don't know what to do. Maybe I should try to turn it another way. If s.o. can help me ?

Merci d'avance Alain TSARAFIDY
To think about the person you would like to be is to waste your time. Just be yourself
-Anonymous