Solved

convert Acess SQL to Oracle (vvvvvvv imp and urgent)

Posted on 2004-10-01
8
379 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

867 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

15 Experts available now in Live!

Get 1:1 Help Now