• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

convert Acess SQL to Oracle (vvvvvvv imp and urgent)



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
manassahu
Asked:
manassahu
1 Solution
 
pratikroyCommented:
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
 
manassahuAuthor Commented:
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
 
geotigerCommented:

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
pratikroyCommented:
Change FIRST to FIRST_VALUE and LAST to LAST_VALUE
0
 
manassahuAuthor Commented:
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
 
manassahuAuthor Commented:
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
 
manassahuAuthor Commented:
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
 
Helena Markováprogrammer-analystCommented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now