Solved

convert Acess SQL to Oracle (vvvvvvv imp and urgent)

Posted on 2004-10-01
8
376 Views
Last Modified: 2008-03-06


How do i convert this into a oracle sql


There are basically 2 tables tblcy-TS-Run and STEP and I am
linking them with the field RN_RUN_ID and ST_RUN_ID
and I am trying to group by cf_item_path,cy_cycle,TS_NAME,TS_TEST_ID,AND then by the last(RN_EXECUTION_DATE: RN_EXECUTION_DATE),then by First(ST_STEP_ID)


I am doing all this in 1 query its v simple in access because u have a GUI tool which calculates totals

I need to create a view in oracle doing the same thing...plss help me ..i need this solution as soon as possible


 This is a access sql but it needs to be converted to oracle 9 or 8...
 
SELECT [tblcy-TS-Run].CF_ITEM_PATH, [tblcy-TS-Run].CY_CYCLE, [tblcy-TS-Run].TS_NAME, [tblcy-TS-Run].TS_TEST_ID, Last([tblcy-TS-Run].RN_EXECUTION_DATE) AS RN_EXECUTION_DATE, First(STEP.ST_RUN_ID) AS ST_RUN_ID, STEP.ST_STEP_ID, First(STEP.ST_STEP_NAME) AS ST_STEP_NAME, First(STEP.ST_STATUS) AS ST_STATUS
FROM [tblcy-TS-Run] LEFT JOIN STEP ON [tblcy-TS-Run].RN_RUN_ID = STEP.ST_RUN_ID
WHERE ((([tblcy-TS-Run].RN_RUN_ID) Is Not Null))
GROUP BY [tblcy-TS-Run].CF_ITEM_PATH, [tblcy-TS-Run].CY_CYCLE, [tblcy-TS-Run].TS_NAME, [tblcy-TS-Run].TS_TEST_ID, STEP.ST_STEP_ID
HAVING (((First(STEP.ST_RUN_ID)) Is Not Null))
ORDER BY [tblcy-TS-Run].CF_ITEM_PATH, [tblcy-TS-Run].TS_NAME, STEP.ST_STEP_ID;
0
Comment
Question by:manassahu
8 Comments
 
LVL 9

Accepted Solution

by:
pratikroy earned 500 total points
ID: 12200064
Well, first of all, I suggest that you get rid of hyphens when creating the tables on Oracle.

Then get rid of opening and closing square brackets. So, when you are selecting CF_ITEM_PATH from tblcy-TS-Run, it should appear as "SELECT tblcyTSRun.CF_ITEM_PATH ...."

Hope this would help
0
 

Author Comment

by:manassahu
ID: 12200749
what would the code be in sqlplus or toad..just by getting the  brackets out its not helping..first( ), last( ) all of this is not working..
0
 
LVL 12

Expert Comment

by:geotiger
ID: 12200885

SELECT t.CF_ITEM_PATH, t.CY_CYCLE, t.TS_NAME, t.TS_TEST_ID, Last(t.RN_EXECUTION_DATE) AS RN_EXECUTION_DATE,
            First(s.ST_RUN_ID) AS ST_RUN_ID, s.ST_STEP_ID, First(s.ST_STEP_NAME) AS ST_STEP_NAME,
            First(s.ST_STATUS) AS ST_STATUS
FROM tblcy-TS-Run t, STEP s
WHERE t.RN_RUN_ID Is Not Null  and t.RN_RUN_ID  =  s.ST_RUN_ID (+)
GROUP BY t..CF_ITEM_PATH, t.CY_CYCLE, t.TS_NAME, t.TS_TEST_ID, s.ST_STEP_ID
HAVING (((First(s.ST_RUN_ID)) Is Not Null))
ORDER BY t..CF_ITEM_PATH, t.TS_NAME, s.ST_STEP_ID;
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 12200948
Change FIRST to FIRST_VALUE and LAST to LAST_VALUE
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:manassahu
ID: 12201327
Sir/mam,
Thank You so much for helping and being patient and taking the time to code
I have a situation here  i changed tbl-cy-ts-run to QRYCY_TS_RUN  which is ok but after that in toad

SELECT t.CF_ITEM_PATH, t.CY_CYCLE, t.TS_NAME, t.TS_TEST_ID, Last(t.RN_EXECUTION_DATE) AS RN_EXECUTION_DATE,
            First(s.ST_RUN_ID) AS ST_RUN_ID, s.ST_STEP_ID, First(s.ST_STEP_NAME) AS ST_STEP_NAME,
            First(s.ST_STATUS) AS ST_STATUS
FROM QRYCY_TS_RUN t, STEP s
WHERE t.RN_RUN_ID Is Not Null  and t.RN_RUN_ID  =  s.ST_RUN_ID (+)
GROUP BY t.CF_ITEM_PATH, t.CY_CYCLE, t.TS_NAME, t.TS_TEST_ID, s.ST_STEP_ID
HAVING s.ST_RUN_ID Is Not Null
ORDER BY t.CF_ITEM_PATH, t.TS_NAME, s.ST_STEP_ID


WHEN I validate the  qry it says line 3 col 13 has an error  and then it high lights First and says 0ra 00904 invalid column name

what shd I do
0
 

Author Comment

by:manassahu
ID: 12204009
FIRST_VALUE and LAST to LAST_VALUE also do not work..

what would be the syntax for a cross tab query between
2 tables tbla t  and tblB s
which are related to each other by
t.RN_RUN_ID  =  s.ST_RUN_ID  and depending on the last execution date of run table i need to group the rest of the columns.
say there are these columns A,B.C,status,execudate.
A,B,C  values appear horizontally  where as status appears as column name

so the input tables are
t
==
A    B     C          status           RUN_ID   execudate
-   -  -   ------     --------        
a1   LA    x         pass              1             08/04
b1   NH    y        fail                 2             08/04
c1   VA    z        not run           3             07/04

s
==
A    B     C          status           RUN_ID   execudate
-   -  -   ------     --------        
a7   LA    x 1        pass              1             08/04
b9   NH    y1        fail                 2             08/04
c10  VA    z 1       not run           3             08/04


now if i join the 2 tables with (cross tab qry with status as col heading) on run id by the last execution date  of t o/p shd be
A    B     C                     RUN_ID   execudate
-   -  -   ------     --------        
a7   LA    x 1       1             08/04
b9   NH    y1        fail                 2             08/04
c10  VA    z 1       not run           3             08/04



0
 

Author Comment

by:manassahu
ID: 12204029
FIRST_VALUE and LAST to LAST_VALUE also do not work..

what would be the syntax for a cross tab query between
2 tables tbla t  and tblB s
which are related to each other by
t.RN_RUN_ID  =  s.ST_RUN_ID  and depending on the last execution date of run table i need to group the rest of the columns.
say there are these columns A,B.C,status,execudate.
A,B,C  values appear horizontally  where as status appears as column name

so the input tables are
t
==
A    B     C          status           RUN_ID   execudate
-   -  -   ------     --------        
a1   LA    x         pass              1             08/04
b1   NH    y        fail                 2             08/04
c1   VA    z        not run           3             07/04

s
==
A    B     C          status           RUN_ID   execudate
-   -  -   ------     --------        
a7   LA    x 1        pass              1             08/04
b9   NH    y1        fail                 2             08/04
c10  VA    z 1       not run           3             08/04


now if i join the 2 tables with (cross tab qry with status as col heading) on run id by the last execution date  
 
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12215282
I think that you can try this query with MAX and MIN instead of Last and First:

SELECT t.CF_ITEM_PATH, t.CY_CYCLE, t.TS_NAME, t.TS_TEST_ID, MAX(t.RN_EXECUTION_DATE) AS RN_EXECUTION_DATE,
            MIN(s.ST_RUN_ID) AS ST_RUN_ID, s.ST_STEP_ID, MIN(s.ST_STEP_NAME) AS ST_STEP_NAME,
            MIN(s.ST_STATUS) AS ST_STATUS
FROM QRYCY_TS_RUN t, STEP s
WHERE t.RN_RUN_ID Is Not Null  and t.RN_RUN_ID  =  s.ST_RUN_ID (+)
GROUP BY t.CF_ITEM_PATH, t.CY_CYCLE, t.TS_NAME, t.TS_TEST_ID, s.ST_STEP_ID
HAVING s.ST_RUN_ID Is Not Null
ORDER BY t.CF_ITEM_PATH, t.TS_NAME, s.ST_STEP_ID
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now