|
View previous topic :: View next topic
|
| Author |
Message |
Niki
Active User
Joined: 20 Sep 2008
Posts: 106
Location: Bangalore
|
|
|
|
Hi all,
I am having a querry like beow,
| Code: |
SELECT MIN(UPDT_TS)
FROM Table
WHERE Field_1 = '1111111111'
and Field_2 = '03'
AND UPDT_TS >= (SELECT MAX(UPDT_TS)
FROM Table
WHERE Field_1 = '1111111111'
and Field_2 != '03' )
WITH UR;
|
The above Querry will fetch the UPDT_TS value when the Field_2 has been last time changed.
But in some cases the inner querry can result with null output.
At that time if i tried runnning the above querry i m getting - as an output.
So whenever the inner querry's output is null, The querry should be like below,
| Code: |
SELECT MIN(UPDT_TS)
FROM Table
WHERE Field_1 = '1111111111'
and Field_2 = '03' |
So copuld you guys please help me to have a querry which can perform both the things in a single Select statement.
Thanks in advance |
|
| Back to top |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Code: |
SELECT MIN(UPDT_TS)
FROM Table
WHERE Field_1 = '1111111111'
and Field_2 = '03'
AND ( UPDT_TS >= (SELECT MAX(UPDT_TS)
FROM Table
WHERE Field_1 = '1111111111'
and Field_2 != '03' ) OR 1=1 )
|
|
|
| Back to top |
|
|
Niki
Active User
Joined: 20 Sep 2008
Posts: 106
Location: Bangalore
|
|
|
|
Hi Ashimer,
Thanks for your Reply.
But by using the above query provided by u i m getting the output when the inner querry is null but at the mean time it is avoiding the inner query itself.
My actual requirement is like below,
Input - 1
TABLE
| Code: |
Field-1 field2 UPDT-TS
1111111111 03 1993-06-28-18.55.08.419262
1111111111 03 1993-06-07-19.35.54.394090
1111111111 03 1993-06-07-14.52.43.580583
1111111111 03 1993-06-05-06.34.08.427870
|
So when all the column in the field2 =03 for a particular field-1= 11111111111 Then the o/p Should be the minimum of UPDT-TS. I.e 1993-06-05-06.34.08.427870
When the input is like below,
TABLE
| Code: |
Field-1 field2 UPDT-TS
1111111111 03 1993-06-05-06.34.08.427870
1111111111 01 1993-06-07-14.52.43.580583
1111111111 02 1993-06-07-19.35.54.394090
1111111111 03 1993-06-28-18.55.08.419262
1111111111 03 2009-03-19-01.41.21.877979
|
I should get the output as
1993-06-28-18.55.08.419262 |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Niki ,,your requirement is clear now ...
try this ...
| Code: |
SELECT MIN(UPDT_TS)
FROM Table
WHERE Field_1 = '1111111111'
and Field_2 = '03'
AND UPDT_TS >= (SELECT COALESCE(MAX(UPDT_TS) , '')
FROM Table
WHERE Field_1 = '1111111111'
and Field_2 != '03' )
|
|
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
as your check is on timestamp field please use this coalesce marker.. .
| Code: |
UPDT_TS >=
(SELECT COALESCE(MAX(UPDT_TS),'0001-00-00-00.00.00.000000')
.......
......
|
|
|
| Back to top |
|
|
Niki
Active User
Joined: 20 Sep 2008
Posts: 106
Location: Bangalore
|
|
|
|
Hi Ashimer,
Thanks for your concern regarding my issue.
I used the function COALESCE and its working fine.
Thank you very much. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
You are welcome  |
|
| Back to top |
|
|
|
|
