Solved

convert Acess SQL to Oracle (vvvvvvv imp and urgent)

Posted on 2004-10-01
8
389 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:pratikroy
ID: 12200948
Change FIRST to FIRST_VALUE and LAST to LAST_VALUE
0
 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to Gracefuly recover in Racle stored procedure 1 48
run sql script from putty 4 194
pivot rows to columns 1 46
Need help on decision table structure 7 47
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

730 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