|
Why? Parsing SQL is best left to the actual database engine you're using.
|
|
|
|
|
Hi all,
Iam using Sql reporting services .iam having a field Invoicereq in one table.I want to group only the invoicereq="y" and get the result..
I have given the group field as invoicereq="y" in reporting services and got the result but i am getting a null value along with it .
(totally only one row but the result produce is 2 rows (one row null))
how to eliminate nuull values?
in where condition i cant use the invoicereq="y" option because i want all th rows from tat table but group tat separate
can anyone help me?
|
|
|
|
|
Exclude the null records from your query using
where
table.column is not null
Another option, if you want the nulls returned is
isnull(table.column, 'null')
To which you could then report on.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego.
|
|
|
|
|
i want to use it in a dropdown list where if I select a country value from dropdwonlist I get corresponding state and when I select a state corresponding city (Table in sql server)
|
|
|
|
|
OK, firstly though, how are you doing the GUI (WinForms C#, WebForms etc.) etc... ?
(edit - sorry missed the Sql Server bit)
Regards,
Rob Philpott.
|
|
|
|
|
Hi
In SQL 2005, I am trying to get a csv strin like "1212,1122,1123,1124" out of following xml.
<group>
<id>1212
<id>1122
<id>1123
<id>1124
Please advise how it can be done?
Thanks
Pankaj
|
|
|
|
|
The data you posted isn't in xml format so it's quite hard to say what the correct way would be. But let's suppose you have something like in the following example, then your query could be
declare @testdata xml
set @testdata =
'<Root>
<element>1212</element>
<element>1122</element>
<element>1123</element>
<element>1124</element>
</Root>'
SELECT @testdata.query('for $e in /Root/element return concat(string($e),'', '')')
|
|
|
|
|
Thanks. With little tweak I can get desired result.
|
|
|
|
|
You're welcome
|
|
|
|
|
Hi,
I am calling a sql server stored procedure from a C# app. I have my C# app writing to the console, but would like my stored procedure to do the same. In Oracle I can do this, but I've not been able to find a way in SQL Server.
Thanks in advance for any sugguestions...
|
|
|
|
|
AFAIK there's no such mechanism than in Oracle to write to console. However, you could for example write to SQL Server errorlog, create a table to write to or send information by email.
|
|
|
|
|
If I raise the error, will it return to C# with the error?
|
|
|
|
|
If I remember correctly, when setting both state and severity to 0, it isn't handled as an error.
|
|
|
|
|
Is there any query that can let me know the case-sensitivity of a database, table and/or column. I have the following queries that might help with some of these objects.
sp_server_info 16 - case sensitivity at the database level
sp_help '<tablename>' - case sensitivity at the table level
?? - case sensitivity at column level
Let me know if these are sufficient or if there are better ways.
Thanks.
|
|
|
|
|
You could also try using system tables. Something like:
For databases:
select name [Database],
collation_name [Collation],
case
when collation_name like '%\_CI\_%' escape '\' then 'Case insensitive'
when collation_name is null then 'N/A'
else 'Case sensitive'
end
from sys.databases;
For columns:
select sys.tables.name [Table],
sys.columns.name [Column],
collation_name [Collation],
case
when sys.columns.collation_name like '%\_CI\_%' escape '\' then 'Case insensitive'
when sys.columns.collation_name is null then 'N/A'
else 'Case sensitive'
end
from sys.tables
inner join
sys.columns
on sys.tables.object_id = sys.columns.object_id;
|
|
|
|
|
How about checking the tables case sensitivity?
|
|
|
|
|
What do you mean by table's case sensitivity?
Columns have case sensitivity and database has (which for example controls how columns in system tables behave), but a case sensitivity on a table itself wouldn't make sense.
|
|
|
|
|
Hi!!
I have the following database :
id_student-------id_module-----id_validat-------years
1000--------------11------------2----------------2006
1000---------------11-----------2-----------------2007
2000---------------11-----------3----------------2006
2000---------------12-----------1-----------------2007
3000---------------11------------2----------------2009
3000---------------11------------3-----------------2008
I try to show id_student who id_module=11 and id_validat = 2For two different years
in this example the student who id_student=1000 verefier the condition.
but my sql is false
SELECT COUNT(*) AS Expr1, id_student,years
FROM T1
WHERE (id_MODULE=11 )AND (id_validat= 2)
GROUP BY id_student,years
HAVING (COUNT(*) = 2)
Can you help me?
Thanks
modified on Wednesday, February 4, 2009 8:50 AM
|
|
|
|
|
SELECT COUNT(idstudent) AS IDStudentCount, idstudent,<br />
(SELECT TOP (1) t1.years<br />
FROM myTable AS t1<br />
WHERE (t1.idstudent = myTable.idstudent)) AS Years<br />
FROM myTable<br />
WHERE (idvalidat = 2) AND (idmodule = 11)<br />
GROUP BY idstudent<br />
HAVING (COUNT(idstudent) = 2)
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.
|
|
|
|
|
Your query was very close. Just don't group by on years column:
SELECT id_student,
COUNT(*) as years
FROM T1
WHERE (id_MODULE=11 )
AND (id_validat= 2)
GROUP BY id_student
HAVING (COUNT(*) = 2)
|
|
|
|
|
Is this run order is true ?
6 SELECT
1 FROM
2 WHERE
3 GROUP BY
4 HAVING
5 ORDER BY
Best Regards...
|
|
|
|
|
|
Although it is interesting the order of a SQL statement running, my question is: "Why does this matter ?"
Just wondering.
|
|
|
|
|
Hi,
I am working on MSSQL 2005
I want query help
Suppose in table t1, I have field f1 and f2
now the details in the tables are as folows:
f1, f2
1 a
1 b
1 c
1 d
1 e
I want in my select query I should get the information as follows:
f1 f2
1 a,b,c,d,e
Can I get this
Please help
|
|
|
|
|
So if the a,b,c,d,e is a comma separated list in one column, you could write a function to concatenate the result and return it to the query. For details, see: CREATE FUNCTION[^]
|
|
|
|