Help Required - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help Required

Author

Topic

Namita
Starting Member

1 Post

Posted - 2009-11-30 : 08:28:13

Dear All,

I am new to SQL Programming.
I have already written a code that gives me the latest date on which my test case has run.
Now the requirement is that if the latest date value is NULL it should return the previous date which is not null.

The code that I have written is as follows:

select test_case_id , tcrDetailStatus status , tcrid tc_run_id
from
(
select octcr.id tcrDetailId , octcr.nm_status tcrDetailStatus
from odf_ca_release_object ocro, odf_ca_rel_feature ocrf , odf_ca_test_case octc, ODF_CA_TEST_CASE_RUN octcr, odf_ca_test_runs octr--, ODF_CA_REQUIREMENT ocr
where ocrf.ID = 5029004
and ocro.id = ocrf.odf_parent_id
AND ocrf.CODE = octc.INTRO_BY_FEATURE
AND octc.code = octcr.test_case_id
and octcr.odf_parent_id = octr.id
)
left outer join
(
select tc_run.test_case_id test_case_id , max(tc_run.id) tcrid
from odf_ca_test_runs tr, odf_ca_test_case_run tc_run,
odf_ca_test_case tc
where tr.id = tc_run.odf_parent_id
and tc_run.test_case_id is not null
and tc_run.id is not null
and tc.code = tc_run.test_case_id
group by tc_run.test_case_id
) on tcrid = tcrDetailId

I would really appreciate a quick helping hand.

With Best Regards

Namita

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-30 : 08:32:30

I cannot see any dates - only IDs.
What is about table structure, sample data and wanted result?

No, you're never too old to Yak'n'Roll if you're too young to die.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-11-30 : 08:43:09

Hi

SELECT TEST_CASE_ID , TCRDETAILSTATUS STATUS , TCRID TC_RUN_ID
FROM
(
SELECT OCTCR.ID TCRDETAILID ,
OCTCR.NM_STATUS TCRDETAILSTATUS
FROM ODF_CA_RELEASE_OBJECT OCRO, ODF_CA_REL_FEATURE OCRF , ODF_CA_TEST_CASE OCTC, ODF_CA_TEST_CASE_RUN OCTCR, ODF_CA_TEST_RUNS OCTR--, ODF_CA_REQUIREMENT OCR
WHERE OCRF.ID = 5029004
AND OCRO.ID = OCRF.ODF_PARENT_ID
AND OCRF.CODE = OCTC.INTRO_BY_FEATURE
AND OCTC.CODE = OCTCR.TEST_CASE_ID
AND OCTCR.ODF_PARENT_ID = OCTR.ID
)
LEFT OUTER JOIN
(
SELECT TC_RUN.TEST_CASE_ID TEST_CASE_ID , MAX(TC_RUN.ID) TCRID
FROM ODF_CA_TEST_RUNS TR, ODF_CA_TEST_CASE_RUN TC_RUN,
ODF_CA_TEST_CASE TC
WHERE TR.ID = TC_RUN.ODF_PARENT_ID
AND TC_RUN.TEST_CASE_ID IS NOT NULL
AND TC_RUN.ID IS NOT NULL
AND TC.CODE = TC_RUN.TEST_CASE_ID
GROUP BY TC_RUN.TEST_CASE_ID
) ON TCRID = TCRDETAILID

please check the colored part. There is no comma.

-------------------------
R...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources