|
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'ONE_ROW_TABLE';
Will get me the column names, ok, when this table is meant to be a one-row table, how can I get the column names of that table that has a value of say '0'
Example:
ONE_ROW_TABLE
COL1 COL2 COL3 COL4 COL5
---- ---- ---- ---- ----
1 0 1 0 0
I want to see an output like:
COL1
COL3
Thank you guys!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(> <)
<div class="ForumMod">modified on Wednesday, October 22, 2008 4:45 AM</div>
|
|
|
|
|
You can create a query using UNION to make it row based or a dynamic query using the case statement for a column based answer.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
Most of this sig is for Google, not ego.
|
|
|
|
|
How??
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
What you could try:
- select everything from the table
- use PIVOT to 'turn' the results (make columns rows and vice versa)
- use the result as inline view in FROM section
- create a select around inline view and restrict rows based on values
so the keyword in this is PIVOT.
Hope this helps,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Dear Friend's
 i have an float value 2.796 and 2.792 i want it in two digit value when i use round keyword it give me output as 2.80 and 2.79 but i want it as 2.79 and 2.79 can it possible if yes please tell me how to do it in sql server 2005
i have use Round and STR keyword from sql for this but in vain
CODE
--USING STR
select STR(2.792,25,2) as ID
select STR(2.796,25,2) as ID
OUTPUT
2.79
2.80
--END
--USING ROUND
select round(2.792,2) as ID
select round(2.796,2) as ID
OUTPUT
2.790
2.800
--END
Sasmi
|
|
|
|
|
Actually, you're asking for this output!, if you dont want a decimal, then simply skip it! here
SELECT ROUND(2.792) FROM ....;
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Actually i want last two digit i mean if i have value 2, 2.7312467 and 2.796 then the output should be 2.00, 2.73 and 2.79
thanks for the code but it's giving me error "The round function requires 2 to 3 arguments."
CODE
select round(2.792) as ID
select round(2.796) as ID
OUTPUT
Msg 189, Level 15, State 1, Line 7
The round function requires 2 to 3 arguments.
Msg 189, Level 15, State 1, Line 8
The round function requires 2 to 3 arguments.
Sasmi
|
|
|
|
|
Sasmi_Office wrote: 2, 2.7312467 and 2.796 then the output should be 2.00, 2.73 and 2.79
Q: Do you know what the hell is "Rounding"?? 
A: It is bringing the number to the closest whole number, "and in case you specify a decimal place(s), to the closest number of those decimals"
then 2.01 >> 2
and 2.55 >> 3
Sasmi_Office wrote: thanks for the code but it's giving me error "The round function requires 2 to 3 arguments."
Then give it a 0 as the second parameter
SELECT ROUND(2.88,0)...
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
An old trick
declare @a float,@b float
select @a = 2.796, @b = 2.792
select round(@a - 0.005,2),round(@b - 0.005,2)
Both return 2.79. Its because round follows the maths rule of going up at .5 and over.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
thanks dear.
it's working now, But i have one question you add 0.005 to the value, should every time we have to add 0.005 or you add it with some specific reason i mean i found my value (2.792 or 2.796) is with 3 digit and you add 0.005 which also have 3 digit if my value is more then 3 digit (2.79546) then should i add the same 0.005 value or i have to add 0.00005
can you clarify me...
Sasmi
|
|
|
|
|
It doesn't matter how long your number is, you need the same number of zeros as you want to round to. For example, if you want to round to 4 decimal places you use .00005, to round to 3 decimal places you use .0005
Hope this clarifies.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks for the clarification.
Sasmi
|
|
|
|
|
Hi all,
I want to write a store proc that queries across db, and whenever I am at one db location, i want to query for a list of records which I want to store because I am going to perform an update on each of these based on the results that i gather earlier. once im done, ill loop and move on the next db.
Looks something like this;
1. Retrieve all db links
2. Loop to go to the first db link provided
3. Query table @current db link and use cursor to store the results
4. Loop results to perform update
5. Once, done go back to (2) and move to the next db until there is no more left
Now the question is, how to I access query across db dynamically? Currently i am using EXECUTE IMMEDIATE method. Next, can i use crusor when using execute immediate?
Any advice? Greatly appreciate it
Regards, Jensen
|
|
|
|
|
You can either use DBMS_SQL package to define and use the cursor or simply use REF CURSOR.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi,
How can i get a column sum by checking similar rows in database.I know its simple,but pls help me.
thanks
|
|
|
|
|
select sum(mark1) from table1 group by roll_no having roll_no=10
I think this will help u.
|
|
|
|
|
Thank u but the problem is i need to sm up the values depending on a flag
ie if flag is set then add or else substract.
|
|
|
|
|
Try something like this
create table #b1(usr sysname, flag char(1), val float)
insert into #b1(usr,flag,val) select 'a','n',22
insert into #b1(usr,flag,val) select 'b','Y',22
insert into #b1(usr,flag,val) select 'a','n',11
insert into #b1(usr,flag,val) select 'b','n',2
insert into #b1(usr,flag,val) select 'a','Y',100
select usr,sum(case when flag = 'y' then val else val * -1 end) from #b1 group by usr
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi friends
I have two table , name of the table is Table1, table2
table1 values
**************
rollno name tot
1 a 100
2 b 75
3 c 76
table2 values
*************
rollno reference_name
1 SSSS
1 RRRR
2 MMMM
i written the query like this
select table1.rallno,table1.mark,table2.refrence_name from table1 left join table2 on table1.rallno=table2.rallno
I want all the vale from Table1 and Table2 Roll No 1 has more reference name but I want only 1 Reference name
How can i write the query plz tell
|
|
|
|
|
If I understood you correctly, you're having a problem with the query because, based on your sample material, it returns 4 rows instead of 3.
If this is correct, you should use for example GROUP BY clause, but then you cannot have individual values from table2, only aggregates. So you should choose what do you want from table2. For example:
select table1.rallno,
table1.mark,
max(table2.refrence_name)
from table1 left join table2 on table1.rallno=table2.rallno
group by table1.rallno,
table1.mark
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi, a newbie to asp.
I have the following code which when run complains with error 'expected end of statement.'
What it is suppose to do is read in the ip address of a remote host and then do a comparison to an ip field within the database. It should also time stamp this entry if logic permits.
]]>
Would appreciate some help from anyone of you gurus of asp,sql out there.
Regards Phil.
|
|
|
|
|
please provide me with a link for studying all concept and features of a datatable
|
|
|
|
|
|
Very comprehensive link!
Should cover all the areas specified. 
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi all ;
I have two tables Table1 and Table 2 (I work with sqlserver 2005)
Table 1:
champ1---- champ2 ---- champ3
10 ---------- 14---- aaa
10 ---------- bbb----- 15
10 ----------- 13---- ccc
Table2
champ1 ------- champ4 -------- champ5
10 -------------wpm -------------- 1000
10 -------------- xcv -------------- 1200
I try to have the following results:
field1 ------ yyyy------ bbb -----cccc ------ wpm------ xcv
10----------- 14------------ 15 ------ 13-------- 1000 ----- 1200
I made half with pivot table:
field1 yyyy ------ ------ ----- bbb cccc
10----------- --- 14--------- 15 ------ 13 --
but I do not know how to add two columns wpm and xcv
help me thank you.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
