Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

convert Acess SQL to Oracle (vvvvvvv imp and urgent)

Posted on 2004-10-01
8
Medium Priority
?
402 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 2000 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

Technology Partners: 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!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

618 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