asked on

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;

ASKER CERTIFIED SOLUTION

pratikroy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

manassahu

ASKER

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..

geotiger

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;

pratikroy

Change FIRST to FIRST_VALUE and LAST to LAST_VALUE

manassahu

ASKER

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

manassahu

ASKER

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

manassahu

ASKER

Helena Marková

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