|
Hi all,
here is my SP
create FUNCTION fn_Ceiling
(
Source IN DECIMAL
)
RETURN DECIMAL
as
DecDiff DECIMAL(16,3);
Ceiling DECIMAL(16,3);
Source1 DECIMAL;
BEGIN
SELECT NVL(Source, 0) INTO Source1 FROM DUAL;
SELECT Source1 - FLOOR(Source1) INTO DecDiff FROM DUAL;
SELECT FLOOR(Source) +
case
WHEN DecDiff between 0.000 and 0.250 THEN 0.000
WHEN DecDiff between 0.250 and 0.750 THEN 0.500
WHEN DecDiff between 0.750 and 1.000 THEN 1.000
else DecDiff
end INTO Ceiling FROM DUAL;
return Ceiling;
END;
/
It is created...
but if i execute the function am not getting the correct value
ex:-
select fn_ceiling(1.75) from dual;
am getting the result as 1
(actaul result should be 1.500)
Dnt know what i did wrong?
|
|
|
|
|
Not sure with Oracle but I would look at Source1, no presision is declared.
Also your case logic is going to cause a problem with 25 and 75. It meets 2 conditions is should be
WHEN DecDiff between 0.000 and 0.250 THEN 0.000
WHEN DecDiff between 0.251 and 0.750 THEN 0.500
WHEN DecDiff between 0.751 and 1.000 THEN 1.000
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If i add the presision also the same result..
|
|
|
|
|
hi,
Below is my table.
Parent ID.......ID.......Name
NULL..........10001.....Region1
NULL..........10002.....Region2
NULL..........10003.....Region3
10001.........501.......Test1
10001.........502.......Test2
10002.........503.......Test3
10002.........504.......Test4
10003.........505.......Test5
10003.........506.......Test6
501...........507.......Test7
501...........508.......Test8
502...........509.......Test9
502...........510.......Test10
I want the output to be the following.
ParentID.......ID.......Name
10001..........501......Test1
10001..........502......Test2
501............512......Armenia
501............536......Brunei
NULL...........10001....Region1
Based on where clause for ID = '10001'
Pls help. I can't figure out on how to add the last entry of "NULL......10001....Region1"
Thx.
|
|
|
|
|
The query logic is not coming out clear from your posting, please elaborate of the selection criteria...
|
|
|
|
|
well actually i need it to be that output for my asp.net treeview control.
if you look the 10001 is the main ParentID and the ChildID is based on ParentID. in the ID = 501 is also fall inside 10001 ID group, and there is a SubChild for the ChildID = 501
I hope u understand.....i donno how to say...hehe
its a TREEVIEW control.
Thx.
|
|
|
|
|
Not sure if I understood your question correctly. Try this:
select * from TableName where
(ParentID = '10001') or (ID='10001') or
(ParentID in (select ID from TableName where ParentID = '10001'))
May be this can help you.
|
|
|
|
|
Normally I arrane to do this by a simple sort on the parent and ID columns but your data is all over the place. Either fixt up your ids to sort the way you want them to come out or use a union query to split the sorting
Select * from table where parentid is not null order by parentid, id
union all
Select * from table where parentid is null order by id
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What database are you using - SQL 2005 onwards provided the WITH; command whicah can be used for recursive tree patterns like this.
|
|
|
|
|
hi,
i have the following table.
Parent ID.......ID.......Name
NULL..........10001.....Region1
NULL..........10002.....Region2
NULL..........10003.....Region3
10001.........501.......Test1
10001.........502.......Test2
10002.........503.......Test3
10002.........504.......Test4
10003.........505.......Test5
10003.........506.......Test6
501...........507.......Test7
501...........508.......Test8
502...........509.......Test9
502...........510.......Test10
Actually im doing a Treeview control in my ASP.NET application.
I can see the Parent Name, and its child popping out, BUT how to see the ID (501, 502) is having child in ParentID??
My SQL in ASP.NET is "SELECT ID, ParentID, Name FROM Test"
In order to show Child in the Parent, the ParentID has to be NULL.
Any idea??
|
|
|
|
|
You can get a clue from a simple SQL query like
Select distinct ID, ParentID, Name from dbo.tblTest where ID In (Select ParentID from dbo.tblTest)
The output would be like
ID ParentID Name
----------- ----------- -------------
501 10001 Test1
502 10001 Test2
10001 NULL Region1
10002 NULL Region2
10003 NULL Region3
|
|
|
|
|
How can I set a password for DB file and just open it in C# ?
|
|
|
|
|
SajjadZare wrote: How can I set a password for DB file and just open it in C# ?
You want to set SQL Server Password using C# ? Or You want to open a database which is already password protected ?
If you want to open the database which has password, then you need to mention it in ConnectionString.
|
|
|
|
|
I think you are asking how to open a .MDF without having SQL Server, that is open the mdf directly from C#.
You can't, sql server requires a SERVER. I'm not sure if SQL Express will allow you to open the mdf directly.
Access will allow you to do this but you need the Jet engine installed.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SQL Express requires the DB Engine also.
I don't speak Idiot - please talk slowly and clearly
I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury
Driven to the arms of Heineken by the wife
|
|
|
|
|
Hello everyone,
I have simple query as:
SELECT so.title,max(so.nu_view)
FROM song as so,singer as si
GROUP BY so.title,so.nu_view
certainly, it will show the result, but to get achievement it needs a performing process, how to view this activity
Example: how to see combination between song and singer, how to see "group by" activity ...
does SQL server 2005 support this fuction ?
help me
|
|
|
|
|
You can use SQL Server Profiler to check the Performance of your Any Query. 
Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.
|
|
|
|
|
oh thanks, but sql server trace rewrite action process, not describe action inside sql server 2005,
Example before publish the result , there are many action such as : combination between many tables, action " WHERE" , "GROUP BY" , I want know the its ' detail
help me
|
|
|
|
|
Generally speaking your queries are executed (processed) in the following order:
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
hi guys
i have a filed in my database named status which values is a bit either 0 or 1 ,
i want to get its values and wanto to translate 0 to inactive and 1 to active
and i want to do it at server end in a udf ,how can i do that ? regards .
Tauseef A Khan
MCP Dotnet framework 2.0.
|
|
|
|
|
Hope you are looking for this.
select <br />
case when status=0 then 'inactive'<br />
when status=1 then 'active' <br />
end as [Status]<br />
from tablename
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
thanks i was just looking for that .
Tauseef A Khan
MCP Dotnet framework 2.0.
|
|
|
|
|
You are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I would use and else to trap the 2, -2 values
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi experts
I am using SQL Server 2005
I have Table Having Str. like
__________________________
Heading | Tax
__________________________
A | Taxx
A | Taxz
A | TAXN
B | 10
B | 20
B |30
C |5
C |5
C |4
_____________________________
i Want Result
like
--------------------------------
A B C
----------------------------------
TaxX 10 5
TaxZ 20 5
TaxN 30 4
--------------------------------
The No's of Column also dynamic
i.e A B C D .... so on
then help but Query in Using to get this Result
Dinesh
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
