| Author |
Topic |
|
korssane
Posting Yak Master
104 Posts |
 Posted - 2009-06-30 : 13:40:44
|
| Hi all,i am really stuck with this syntax and i want to convert it to sql 2005 one :CASE WHEN (FIRST_VALUE(EV) over (partition by TRID order by re_id DESC) LIKE ''%blabla'') THENRE_DATEELSECASE WHEN (KN_IS_ID IS NOT NULL) THEN KN_IS_BEELSE FIRST_VALUE(CR_DA) over (partition by TRID order by re_id DESC)END |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-30 : 13:45:40
|
| there's no function like FIRST_VALUE in sql server. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-06-30 : 13:48:22
|
quote:
Originally posted by visakh16
there's no function like FIRST_VALUE in sql server.
That is true.But maybe you can code a "workaround" using row_number().
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-30 : 13:52:00
|
| before that we need know what he'd trying to achieve..may be with the help of some sample data |
|
|
|
korssane
Posting Yak Master
104 Posts |
Posted - 2009-06-30 : 14:24:02
|
| Sorry guys i am just translating anOrcale code to sql server..trying to understand this sysntax..It returns the first value in an ordered set of valuesCASE WHEN (FIRST_VALUE(EV) over (partition by TRID order by re_id DESC) LIKE ''%blabla'') THENRE_DATEtrying to order vallues EV that are like dinf values that ''%blabla'' if this condition apply ..i wll take the RE_DATE value..2) Also do you have an idea what is the equivalent of the "LEAD FUNCTION" ( that lets you query more than one row in a table at a time without having to join the table to itself.) in SQL .thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-30 : 14:25:54
|
| i think you need self join or union in sql server for 2 |
|
|
|
korssane
Posting Yak Master
104 Posts |
Posted - 2009-06-30 : 14:34:53
|
| thanks visakh16,i think i am lost..lol..i have renmoved this : CASE WHEN (FIRST_VALUE(EV) over (partition by TRID order by re_id DESC) LIKE ''%blabla'') THENand replaced it By this " CASE WHEN (EVENTNAME LIKE '%Unlink Known Issue') THENi am not sure if this gonna helpcuz the querry is too long and i am not sure of the result.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-01 : 12:24:58
|
| i cant realy make out what you're trying to achieve. post some sample data and then explain what you want with o/p. then we will be able to help you. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-07-01 : 14:19:52
|
quote:
Oracle/PLSQL: Lead FunctionIn Oracle/PLSQL, the lead function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from the next row in the table. To return a value from a previous row, try using the lag function.
Besides the fact that the order of data in a database should have no apparent meaning, seems like in Oracle (and MS Access) it does.You do know that more than likely, the results of these function like first are as only valid as you last REORG, ORDER BY, DISTINCT, etchttp://www.techonthenet.com/oracle/functions/lead.phpBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-07-01 : 14:21:39
|
quote:
Originally posted by visakh16
i cant realy make out what you're trying to achieve. post some sample data and then explain what you want with o/p. then we will be able to help you.
ZoooooooooooooooooooooooooooooooooooooooooommmmmmOn to 50,000Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
|
I am stuck in here with this syntax