Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







How Do I Exclude Certain Unwanted Data From A Query Result


Hi All,

We've got a basic query that pulls a list of some parts out of our inventory database:

SELECT p21_view_inv_mast.item_id, p21_view_inv_mast.item_desc, p21_view_inv_loc.gl_account_no
FROM dbo.p21_view_inv_loc p21_view_inv_loc, dbo.p21_view_inv_mast p21_view_inv_mast
WHERE p21_view_inv_loc.inv_mast_uid = p21_view_inv_mast.inv_mast_uid

This returns a lot of info over three columns: "item_id" "item_desc" and "gl_account_no"

What I want to do is remove any entries where the "gl_account_no" shows as 011500000

I am by no means a TSQL person, I mostly run the canned queries that our vendor gives us so any help with this would be greatly appreciated.


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
How To Delete Unwanted Data From Multiple Different Tables With One Single SQL Query?
This a microsoft SQL 2000 server.
I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID.
Here is my problem:
Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.

I know how to get the user table names from the DB as follows:

SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U' and name like 'Elect_Sub%Daily'

What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".

delete fromtableNamewhere Date_Stamp = '2008-3-18'

View Replies !   View Related
Exclude Duplicates From Result Set
This should be trivial but I'm ignorant so I'm hopingsomeone can assist. I can find lots of code snippets for removing duplicates, but I can't find a variation that works for my case.

Overview:I'm doing a name look-up, combining first & last names and if it matchesagainst an employee table, getting the employeeID for that person. I need to only return the unique matches, and exclude any names that happen more than once in the employee table.

I have 3 possible results from my select (snippet below):
1) Single match
2) No match
3) Multiple matches (2 or more people, same first and last name)

My simple code below does exactly what I need for case 1 & 2. If there is a single match on the name, it's returns theres_ID and emp_IDas expected, if no match, no recordand that works for me.

The problem I can't solve is if there are two John Smith employees, both records are returned, which is what my query requests, but not what I need. I want ONLY return data which has ONE exclusive match, and exclude all others.

Code for case 1 & 2, doesn't handle 3rd case:

Select distinct ot.res_ID, e.emp_ID
From employee e, @OutputTable ot
Where (e.fName + ' ' + e.lName) = ot.empFullNameText

Employee table has first & list names, plus emp_ID
@OutputTable is a table variable from my proc which has "John Smith" type text names as one string.


This has to be simple, but I'm over my head on this one. All ideas, reference links or other assistance appreciated.

View Replies !   View Related
Exclude Query
I have 16,000 rows in tblClient and 3000 rows in NewTable.SELECT tblClient.*FROM tblClient INNERJOIN [New Table] ON tblClient.NoDossier <> [New Table].NoDossierif I use = (equal) instead of <> (exclude), the query returns 3000 rowswhen I use <> it returns 160000 rows,if I try group by, the query bugswhat is my problem

View Replies !   View Related
Exclude From Query
Hi. hope someone can help me with this thing.

I have a list of numbers that I get from a query to my database. how could I make the query so that I can exclude a couple of numbers. For example the numbers 1234 and 8888. My list is in the range of 1 - 99999.

Thank you
Mr. Newbie

View Replies !   View Related
Using Result From One Query For Data Flow Task
I have a table with five entries. For each row read in that table I want to pass the value

to a data flow task to be used to set the filename in a flat file connection.



Thanks.

David

View Replies !   View Related
How To Exclude Null Data?
I'm new to MS SQL server. Is there a select statement that will include fields that are not null?

View Replies !   View Related
How To Exclude Data From An Average?
I am selecting the following fields

AVG_Back
AVG_Yield

I want to select both fields, like this

Select AVG(AVG_Back), AVG(AVG_Yield)
FROM tblUser
WHERE Date Between '3/1/2008' AND '3/31/2008'

I want to limit the AVG_Back field to exclude all values of 0. So only average AVG_Back if the value > 0. What is the best way to accomplish this? I can't just put it in the where clause or the AVG_Yield will be excluded too.

View Replies !   View Related
How Do I Exclude Data From One Table Based On Data From Another?
I have a table called MasterSkillList which is a list of skills and attributes, eg: [Appraise, INT], [Bluff, CHA] etc
I have a table called Classes, which is a list of all classes available (and some details which are irrelevant), eg: [Fighter], [Assassin] etc.
I also have a table called ClassSkills which holds a list of classes and their applicable skills, eg: [Assassin, Bluff], [Assassin, Open Lock], [Fighter, Appraise], [Fighter, Bluff] etc.
 What I have is a gridview which shows all my classes from the class table. i want to be able to select a class on that gridview and create a checkbox list of all available skills that are NOT allready associated with that class. Eg: assassin has bluff and open lock, so those two skills shouldn't appear on my checkbox list. So i want to show all the skill from the master skills list, excluding all the skills the selected class allready has.
 Alternatively, It would be better if there was a way to display all the skills in existance on my checkboxlist and the ones that class allready has to be checked. Any suggestions?
Here's the query I have: 
SELECT     MasterSkillsList.SkillFROM         ClassSkills INNER JOIN                      MasterSkillsList ON ClassSkills.Skill = MasterSkillsList.SkillWHERE     (MasterSkillsList.Skill <> ClassSkill.Skill)
 Edit:
I just added the following sql query, but when i run it i get no results even though it should show everything except 2 skills. Have I written it wrong?
SELECT     SkillFROM         MasterSkillsListWHERE     (NOT EXISTS                          (SELECT     Skill                            FROM          ClassSkills                            WHERE      (ClassName = @ClassName)))

View Replies !   View Related
Update Table With Result From Data Mining Query
I apply data mining query transformation to data i got from conditional split (Table Employeeswhere salary=null).
I predicted the value of column(Salary) and i want to update column Salary(table Employees) with the predicted value, only for the rows that there is a prediction.

Any suggestions how cani update the table with the result of Data Mining Query Transformation?

Thank you.

View Replies !   View Related
How Do You Exclude Data That Doesn't Exist?
I have a cube that is showing measures that don't exist. Let me give an example. This example will include 3 dimensions, product, location, and time. The fact table measure will be sales.

Here are the distinct values if you were to write a sql query against the dimensionl model that feeds the cube.

Product Location Time Sales
A X 1/04 200
B Y 1/04 100

A X 2/04 300

In the cube, if you were to look at product by location for just 2/04, you would see:

Product Location Sales
All Loc 300
A X 300
Y

All Loc
B X
Y

How do you get rid of the zero's or combinations that don't exist?

Thanks,

Doug

View Replies !   View Related
Saving Query Result To A File , When View Result Got TLV Error
HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View Replies !   View Related
End Result Is Main Query Results Ordered By Nested Result
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View Replies !   View Related
SP To Perform Query Based On Multiple Rows From Another Query's Result Set
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w.  one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users             SELECT u.userName,u.userID                 FROM  tableU u                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID                WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
                FROM  tableU u
                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
                WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!    

View Replies !   View Related
Re-display Result Set Without Re-running Query In Query Analyzer?
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.

View Replies !   View Related
Unwanted Sort
Hi All,I am trying to delete rows from a table using a SQL statement similarto this:DELETE FROM TableA where ID1 IN(Select ID1 From TableB where ID2>= @min and ID2<=@max)Basically I want to delete all rows from TableA that have an ID in arange in TableB. This is done in a stored proc.When I look at the execution plan, it is using the indexes as I wouldhope for. The problem is that it is doing a sort which accounts for73% of the cost. I do not need to sort the results. I don't care whatorder they are deleted in.How can I prevent the sort from occuring? I need this delete to occuras fast as possible.Thanks In Advance

View Replies !   View Related
How To Remove Unwanted Characters In SQL?
Hi all,
I have a stored procedure that generates the following SQL WHERE clause
UserName LIKE 'Adi234%' AND Fname LIKE 'David%' AND LName LIKE 'Justin%' AND
It is good except that it i can not remove the last AND which is not neccessary at the end of the clause.
I want to remove the last AND that come up at the end, my code places AND after each data field(UserName, Fname, Lname) .
 
Thanks

View Replies !   View Related
Unwanted NVARCHAR Padding
I've created an asp.net page that takes the content of text boxes and writes them to a sql table.

The problem is that when I examine the resulting data in the SQL database, I find that the fields written to have had padding added (up to the maximum size of the fields).

I was under the impression that fields of type NVARCHAR did not store padding (only the no of characters being stored).

I've checked it's not the text boxes on the aspx page by explicitly posting values instead of the boxes content and the same thing happens.

Help

example of function i'm using to post data:

Function AddNews(ByVal Category As String, ByVal ApplicRole As String, ByVal NewsType As String, ByVal Description As String, ByVal News As String, ByVal Hyperlink As String, ByVal Email As String, ByVal BirthDate As Date, ByVal KillDate As Date, ByVal Parent As String) As Integer
Dim connectionString As String = ConfigurationSettings.AppSettings("AuthentConnection")
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [News]([Category],[ApplicRole],[NewsType],[Description],[News],[Hyperlink],[Email],[BirthDate],[KillDate],[Parent]) VALUES (@Category,@ApplicRole,@NewsType,@Description,@News,@Hyperlink,@Email,@BirthDate,@KillDate,@Parent)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand

dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_Category As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Category.ParameterName = "@Category"
dbParam_Category.Value = Category
dbParam_Category.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Category)
Dim dbParam_ApplicRole As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_ApplicRole.ParameterName = "@ApplicRole"
dbParam_ApplicRole.Value = ApplicRole
dbParam_ApplicRole.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_ApplicRole)
Dim dbParam_NewsType As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_NewsType.ParameterName = "@NewsType"
dbParam_NewsType.Value = NewsType
dbParam_NewsType.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_NewsType)
Dim dbParam_Description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Description.ParameterName = "@Description"
dbParam_Description.Value = Description
dbParam_Description.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Description)
Dim dbParam_News As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_News.ParameterName = "@News"
dbParam_News.Value = News
dbParam_News.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_News)
Dim dbParam_Hyperlink As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Hyperlink.ParameterName = "@Hyperlink"
dbParam_Hyperlink.Value = Hyperlink
dbParam_Hyperlink.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Hyperlink)
Dim dbParam_Email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Email.ParameterName = "@Email"
dbParam_Email.Value = Email
dbParam_Email.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Email)
Dim dbParam_BirthDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_BirthDate.ParameterName = "@BirthDate"
dbParam_BirthDate.Value = BirthDate
dbParam_BirthDate.DbType = System.Data.DbType.Date
dbCommand.Parameters.Add(dbParam_BirthDate)
Dim dbParam_KillDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_KillDate.ParameterName = "@KillDate"
dbParam_KillDate.Value = KillDate
dbParam_KillDate.DbType = System.Data.DbType.Date
dbCommand.Parameters.Add(dbParam_KillDate)
Dim dbParam_Parent As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Parent.ParameterName = "@Parent"
dbParam_Parent.Value = Parent
dbParam_Parent.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Parent)

Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

Return rowsAffected
End Function

View Replies !   View Related
Remove Unwanted Logins .
Hi ,

I have lot of SQL logins which do not have access to any databases . They do not show any database access when i check their properties .
My server is SQL 7.0 with SP 3 .

I want to clean up those logins with the help of script instead of deleting every login manually which is time consuming as i have to check the database access before deleting the login.

Is there any where script available or any other way to delete these unnecessary logins ? Also will it be a problem on the SQL Server if i delete those logins at one command ?

Any info. will be helpful to me.

Thanks
Sandeep .

View Replies !   View Related
Unwanted Auto-incrementing
Here is a simplified version of my problem:
I am inserting data into a table using a stored procedure. The table has an identity column that increments with each insert. When I use erroneous data in the other fields the insert fails….no surprises there! But when the next insert occurs with valid data I find that my identity field has increased even with those inserts that failed, so my sequence has jumped a few numbers. How do I get the identity inserts to roll back if the rest of the data in a row doesn’t insert successfully?
Marcha x

View Replies !   View Related
How To Remove Unwanted Characters During ETL?
Can unwanted characters (e.g. control codes) be replaced or removed in varcharfields during extraction inside DTS package?

SQL Server/SSIS 2005.

Thanks, Andrei.

View Replies !   View Related
Unwanted Date Conversion - Please Help
Hello, I have a report that is based on a dynamic column. The column is specified by the user, and the column tyep may be text, datetime, or bigint. The user specifies the column name, and there is also a parameter that specifies the type. I allow my users to format the date via passing in a date format string, so my code looks something like:



=Switch

(

Parameters!primarycolumntype.Value = "text",

Fields(Parameters!primaryitemcolumn.Value).Value,

Parameters!primarycolumntype.Value = "date",

[DO DATE FUNCTIONS HERE, e.g. Year(Fields(Parameters!primaryitemcolumn.Value).Value)],

Parameters!primarycolumntype.Value = "numeric",

Fields(Parameters!primaryitemcolumn.Value).Value

)



The trouble I am running into is that I am getting invalid date conversion errors whenever a text value is being supplied - in other words, the code in the "DO DATE FUNCTIONS HERE" is being executed regardless of whether my primary column type parameter is text OR date. It would appear that all conversion fuctions are calculated at runtime prior to running other logic. If so, this is a bad thing for me, as that means I cannot mix data types in the same report and figure it out all dynamically. Is this true? I had always thought that the date code (in the example above, the call to Year()) would not be executed unless that statement in the switch statement was true. I've had the same luck with If statements, and Choose statements, and I've even tried using custom code.



I think my whole problem relates to a misunderstanding of how the functions are processed. Can anyone shed some light?



Thanks!



Michael

View Replies !   View Related
Return Result From One Query As A Column In Other Query
I'm having a bit of a trouble explaining what I'm trying to do here.

I have 3 "source" tables and a "connecting" table that I'm going to use

tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)

They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID

One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:

ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]

I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)

Kirikiri

View Replies !   View Related
Save Query Result At CSV Or TXT File From The Query
We can save query output save as CSV file directly from the Query Analyzer window. I have done it at last few year before. Now I need it.Can anyone please give the one example for the same.

Thanks
AmitK Patel

View Replies !   View Related
Removing Unwanted Stored Procedures.
How do I remove old stored procedures that are no longer being used?


Thanks,

rm

View Replies !   View Related
Remove Unwanted Character From String
In SQL How do you take a field like the phone field and replace the unwanted Character Like - ( ).

View Replies !   View Related
Deleting Unwanted Entries At The Subscriber .
Hello ,

I have created a publication from SQL Server 7.0 and subscribed the publication it to one of the SQL 2000 server std. edition .

Now there are some entries in the subscription list at the subscriber which need to be deleted .
The list shows the name of the publication , the database name and the date .

I wanted to know hoe can we delete those unwanted entries. If i select the entry and right click on it , there is no delete option .

Are those entries to be deleted from one of the tables at the subscriber or the publisher ? If yes, then what is the name of the table ?

Any ideas ?

Thank you very much .

View Replies !   View Related
Inconsistent Result Using Data Reader Source To Get Data From DB2
I am having a very wierd issue regarding a DB2 sql query. I need to get data from Db2 and insert into our sql server database. Using data flow task, to get data I am using the data reader source. COnnection is ado.netodbc connection.
THis sql query also has some comments in it.

The first wierd thing is...

1. On Development server, when I run this query manually, meaning using toad, winsql(connection to the db2 database), the query runs fine. Brings back approx 667 rows which is correct. ON the same serverwhen I try torun this query, via a SSIS pkg, data flow task, using data reader source, gives me error on those comments that exist in that query. But if I run the sameSSIS pkgon another server (Integration server). It runs fine. The same pkg also runs fine if I run it from my machine. SO What is different on my Dev server compared to the Integration server.

2. Say if I take those comments out from the sql query, then try to run the ssis pkg. The query is stuck at the first record and goes in an infinite loop mode. though my query is nota procedure, it is just a sql statement. But this ssis pkg with the query runs absolutly fine on the other server. I aslo tried using the other types of connection and ole db source but still the same problem on the Dev server.

What do I need to look for that is so different on the dev server compare to the INT server. I also checked the version on both these server for Visual Studio 2005(by going to About Microsoft VisualStudio), it is the same.

This is what I have on both the servers....
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00


Please HELP !!!!

Thank you.
Jinita

View Replies !   View Related
Unwanted Conversion Fails Sorting Code
I have some code that sorts a column logically even if it contains both numbers and text. But I've found that SQL converts certain characters automatically....

Here is the code...

SELECT * FROM TABLE

order by
case when isnumeric(fieldname)=1 then
right(replicate(' ', 10 ) + convert(varchar,convert(numeric(10),fieldname)),10)
else fieldnameend --10 is the length of the field


The issue I've run across is that if my column contains the following strings(5D000,5E000,4E001) it thinks they are numeric then the THEN part of the condition causes a typecasting error....

I found out that any certain combinations of number + E or D + 0. Does anyone know a way to get around this? Try it out select isnumeric('5E0303') will return 1

View Replies !   View Related
Separating Unwanted String From Text File
Hi.. I have a problem with inserting data from text file into a table on SQL. The problem I'm having is that the text file provided has a lot of unwanted string that causes bcp operation failed. I wanted to insert the text data into a table. Usually I would use bcp operation to do that, however the newest text file I've got has some unwanted string that causes bcp to fail. I can insert the data into a table, but because the data is horizontally long in view, it would not be inserted according to the right row, meaning that the second row data would still be half of the first row data. Anyone know how to insert the data in a correct manner ? thanks.

View Replies !   View Related
Hidden Graph At Top Of Report Causes Unwanted Whitespace


Hi,

I have a need to create a report that has a graph at the top and a table at the bottom. The graph at the top can optionally be made hidden because they cause problems when exported to Excel as images. However, when I set the Hidden property of the graph to true, positions of all items on the report remain absolute. Meaning of course that the table that is located half-way down the page remains half-way down the page and there is a lot of nothing on the first half where the graph used to be.

It would be desirable to have the ability to have the table move up when the graph is not visible, however it obviously doesn't do it automatically and also refuses me the ability to change the position with an expression.

Any advice is appreciated, thank you!

View Replies !   View Related
Unwanted Conversion Of Non Latin ASCII Characters
hi

we get ASCII data inserted into a SQL Server database by ODBC connection from an old UNIX system.

Example: INSERT INTO test.db VALUES ('123abc', '456ПРО')

All characters > 128 are converted to "?" automatically.

We tried to setup the database to the appropriate codepage, but we allways get "?" inserted.





View Replies !   View Related
How To Remove Partially Duplicate Rows From Select Query's Result Set (DB Schema Provided And Query Provided).
Hi, 
Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. 
I have three tables namely – Forums,Topics and Threads  in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- 
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
ORDER BY t.topicid ASC,th.lastpostdate DESC 
Whose result set is as below:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
1
Java Overall
a@b.com
2008-01-27 14:44:29.000
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  
On modifying the query to:- 
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)
ORDER BY t.topicid ASC,th.lastpostdate DESC 
I get the result set as below:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
4
Swings
p@q.com
2008-01-27 15:12:51.000

I want the result set as follows:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. 
The scripts for creating the tables and inserting test data is as follows in an already created database:- 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO 
CREATE TABLE [dbo].[Forums] (
            [forumid] [int] IDENTITY (1, 1) NOT NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Threads] (
            [threadid] [int] IDENTITY (1, 1) NOT NULL ,
            [topicid] [int] NOT NULL ,
            [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [replies] [int] NOT NULL ,
            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Topics] (
            [topicid] [int] IDENTITY (1, 1) NOT NULL ,
            [forumid] [int] NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Forums] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [forumid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Threads] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [threadid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Topics] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [topicid]
            )  ON [PRIMARY]
GO  
ALTER TABLE [dbo].[Threads] ADD
             FOREIGN KEY
            (
                        [topicid]
            ) REFERENCES [dbo].[Topics] (
                        [topicid]
            )
GO 
ALTER TABLE [dbo].[Topics] ADD
             FOREIGN KEY
            (
                        [forumid]
            ) REFERENCES [dbo].[Forums] (
                        [forumid]
            )
GO  
------------------------------------------------------ 
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum'); 
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion'); 
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
 

View Replies !   View Related
Getting Result Of Query Inside Another Query
Hi to all,I just need to get two fields from a table and manipulate the resultsin next query of a procedure.I planned to code like what you seebelow,create procedure marks1as@ sql1 as varchar(50)@ sql1=select registerno ,subjectcode from mark;beginselect * from marksetting where registerno='@sql1.registerno' andsubjectcode='@sql1.subjectcode';endcan it be possible to get the results as shown in the code? elsepropose an alternative for this scenario.Thanks in Advance.

View Replies !   View Related
How To Get Sum() Result In Query?
mail_delivery_master----------------------ml_id       ml_from_mail_id                          ml_to_mail_id                                ml_user_id      ml_subject                     ml_content             ml_file_attached  ml_no_of_file_attached ----------- ---------------------------------------- -------------- ------------ --------------------------------------------------------------------------------------------------------------------------------1           aa                                       bb                                                cc              dd                             ee                                                                                                                                                                                                                                                                            2           joe@hotmail.com                          tt@gmail.com,                                   JOHNYJP         Forum answer                   somebody answer          0                1                                                                                                                                                                                                                                                                      3           joe@hotmail.com                          mailme@hotmail.com,                             JOHNYJP         Forum answer                   somebody answer          -1               0                                                                                                                                                                                                                                                              4           joe@hotmail.com                          mailme@hotmail.com,janani@ajsquare.net,         JOHNYJP         Forum answer                   somebody answer           0                0                                                                                                                                                                                                                                                             5           joe@hotmail.com                          janani@ajsquare.net,                            JOHNYJP         Forum answer                   somebody answer          0                0                                                                                                                                                                                                                                                               6           joe@hotmail.com                          dff@gg.com,                                     JOHNYJP         Forum answer                   somebody answer           0                0                                                                                                                                                                                                                                                           7           joe@hotmail.com                          tt@gmail.com,janani@ajsquare.net,               JOHNYJP         Forum answer                   somebody answer          0                0                                                                                                                                                                                                                                                             8           Durai@gamial.com                         yogesh@gamail.com                               durai           test                           test                      0                0                                                                                                                                                                                                                                                                9           janani@ajsquare.net                      devi@ajsquare.net                               janu            test                           hi                        0                0                                                                                                                                                                                                                                                             10          janani@ajsquare.net                      devi@ajsquare.net                               JANANI          test                           Miss u                   -1               2                mail_attachementsml_id       ml_file_name                 ml_file_format       ml_file_size      ml_file_select_from                                                                                  ----------- ---------------------------- -------------- ------------ ------------------------------------------ 1           chocolate                       .jpg                         114528            F: empchocolate.pjg                                                                                2           Tiger Caspian Blue.jpg          .jpg                     1114407      F:imageTiger Caspian Blue.jpg                                                                      3           Autumn.jpg                      .jpg                         66287        F:imageAutumn.jpg                                                                                  4           Ascent.jpg                      .jpg                           63244        F:imageAscent.jpg                                                                                  5           Azul.jpg                        .jpg                              61365        F:imageAzul.jpg                                                                                    6           daisy.jpg                       .jpg                             8197         F:imagedaisy.jpg                                                                                   7           Stonehenge.jpg                  .jpg                       59600        F:imageStonehenge.jpg                                                                              i want the result :ml_id , ml_from_mail_id, ml_to_mail_id  from mail_delivery_master   ,,,, and sum(ml_file_size) for that mail from mail_attachements give me the code solution regards samuel chandradoss      

View Replies !   View Related
Query Result As O/p From SP
Hi thereI want a stored procedure that contains a Select query. I want SP that return result of query(rows)how could i do this?please help

View Replies !   View Related
How/what Should Be The Query For This Result
I have 1 table "Progress"P_no b_no status build_date----------------------------------------------------------------25 1 First_slab 2006/4/525 1 second slab 2006/5/625 2 first slab 2006/1/225 2 third slab 2006/2/3o/p should be asPno,bno, status, max(build_date)sample o/p can be as below25 1 second slab 2006/5/625 2 third slab 2006/2/3Thanks in Advance.

View Replies !   View Related
Query: How Can I Get This Result?
Hello all,I tryed to simplify the problem as much as possible. I'll start with theDDL:----------------------------------CREATE TABLE #MyTable(NoID INT,Type CHAR,DateTransaction DATETIME)INSERT INTO #MyTable (NoID, Type, DateTransaction)SELECT 1 AS NoID, 'A' AS Type, '2004-01-01' AS DateTransaction UNION ALLSELECT 2, 'C', '2004-01-01' UNION ALLSELECT 3, 'B', '2004-01-01' UNION ALLSELECT 4, 'C', '2004-01-02' UNION ALLSELECT 5, 'B', '2004-01-02' UNION ALLSELECT 6, 'C', '2004-01-02' UNION ALLSELECT 7, 'A', '2004-01-03' UNION ALLSELECT 8, 'B', '2004-01-03' UNION ALLSELECT 9, 'A', '2004-01-03' UNION ALLSELECT 10, 'C', '2004-01-03' UNION ALLSELECT 11, 'B', '2004-01-03'----------------------------------What I want is all the same Type which, for a same DateTransaction, as adifferent Type inserte beetween them when data is sorted by DateTransactionand NoID. In this case I would like:Type DateTransaction------ -----------------C 2004-01-02 /* B is between two C (NoID = 5) */A 2004-01-03 /* B is between tow A (NoID = 8) */B 2004-01-03 /* A and C are between two B (NoID = 9 and10) */All of these have for the corresponding date at least one transaction with adifferent type between.In the real situation NoID is an autoincrement field, the PK. And theDateTransaction hasn't any time, just a round date.Any suggestion?Thanks for your time.Yannick

View Replies !   View Related
Using Result Of One Query In Another
hi,

i've tried searching on this but not having much luck, i'm trying to use the result of one and use it in another. is there any way to do this?

So for example, i would use the result of this query and store it in a variable called @tmp_id (if possible)

SELECT TOP 1 ID FROM tblWines WHERE RefNo LIKE 'AB1234';

then use that variable in another query

INSERT INTO tblLogWines (WineID, Date) VALUES (@tmp_id, now());

i've simplied the context to make it a little more understandable, any help is appreciated.

goran.

View Replies !   View Related
Query Result Help
Hi,all,
I ran the two queries and I thought it would be the same, but it's different.
Can you explain to me.

Query 1: result---52 rows
select s.InsuredSurname, s.email from studyUSA s
join interMedical I on s.email=I.email
where convert(char(10), s.enrolldate, 126)>= '2004-01-01'
and convert(char(10), s.enrolldate, 126) <='2005-05-20'
and (s.agentcode not like '162%') and (s.agentcode not like '17%')
and s.agentcode <> '130844'


Query 2: result--14 rows

select s.InsuredSurname, s.email from tis_studyUSA s
where convert(char(10), s.enrolldate, 126)>= '2004-01-01'
and convert(char(10), s.enrolldate, 126) <='2005-05-20'
and s.email IN (Select I.Email from tis_InterMedical I)
and (agentcode not like '162%') and (agentcode not like '17%')
and agentcode <> '130844'

Thanks!
Betty

View Replies !   View Related
Need Help With SQL Query And Result Set.
Good day.

I have a working knowledge of T-SQL but apparently not sufficient to solve my problem.

I have3 tables defined as:



CREATE TABLE [dbo].[Complaints](

[ComplaintId] [int] IDENTITY(1,1) NOT NULL,

[FileNumber] [varchar](15) COLLATE Latin1_General_CI_AS NOT NULL,

[DateCreated] [smalldatetime] NOT NULL,

[DateReceived] [smalldatetime] NOT NULL,

[Classification] [tinyint] NOT NULL,

[Misconduct] [tinyint] NOT NULL,

[Status] [tinyint] NOT NULL,

[OccurrenceDateTime] [smalldatetime] NOT NULL,

[OccurrenceCityTown] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,

[OccurrenceRegion] [tinyint] NOT NULL,

[OccurrenceCountry] [tinyint] NOT NULL,

[CreatorId] [int] NOT NULL,

[CreatedFrom] [tinyint] NOT NULL,

[Flags] [tinyint] NOT NULL,

[Summary] [varchar](8000) COLLATE Latin1_General_CI_AS NOT NULL,

[Comments] [varchar](8000) COLLATE Latin1_General_CI_AS NOT NULL,

CONSTRAINT [PK_Complaints_1] PRIMARY KEY CLUSTERED

(

[FileNumber] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]




CREATE TABLE [dbo].[ComplaintSubjects](

[ComplaintSubjectId] [int] IDENTITY(1,1) NOT NULL,

[FileNumber] [varchar](15) COLLATE Latin1_General_CI_AS NOT NULL,

[SubjectId] [int] NOT NULL,

[SubjectType] [tinyint] NOT NULL,

[SubjectIdentity] [tinyint] NOT NULL,

CONSTRAINT [PK_ComplaintSubjects] PRIMARY KEY CLUSTERED

(

[ComplaintSubjectId] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]



CREATE TABLE [dbo].[Persons](

[PersonId] [int] IDENTITY(1,1) NOT NULL,

[FirstName] [varchar](32) COLLATE Latin1_General_CI_AS NOT NULL,

[MiddleNames] [varchar](64) COLLATE Latin1_General_CI_AS NULL,

[LastName] [varchar](32) COLLATE Latin1_General_CI_AS NOT NULL,

[AddressNumber] [varchar](10) COLLATE Latin1_General_CI_AS NULL,

[AddressStreet] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,

[AddressCity] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,

[AddressRegion] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,

[AddressCountry] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,

[AddressPostalZip] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,

[PhoneHome] [varchar](25) COLLATE Latin1_General_CI_AS NULL,

[PhoneWork] [varchar](25) COLLATE Latin1_General_CI_AS NULL,

[PhoneMobile] [varchar](25) COLLATE Latin1_General_CI_AS NULL,

[PhoneFax] [varchar](25) COLLATE Latin1_General_CI_AS NULL,

[DateOfBirth] [smalldatetime] NOT NULL,

[Status] [tinyint] NOT NULL,

[Type] [tinyint] NOT NULL,

[DateAdded] [smalldatetime] NULL,

CONSTRAINT [PK_Personnel_1] PRIMARY KEY CLUSTERED

(

[PersonId] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Basically, I track complaints and each complaint can have 1 or more subjects associated with it.
The structure is the complaint is referencedina complaintsubject row and I use the persons table to find out detail of the complaintsubject (firstname, middlename, lastname) complaintsubject just holds Id's to normalize the database rather than have all details of persons table copied into every complaint (pretty basic so far).

My first question is regarding structure...since my complaint to complaintsubject relationship is 1 to many I assume the best way to associate the two is to have a FileNumber field in the complaintsubject linking back to a complaint. Is there any other design that is better suited to this type of relationship?

Second question and most important: I need a way to search by a string that appears in any part of the firstname, middlenames, lastname. I have the query for this and its working. But the problem arises when I have more than one match (i.e. 2 or more people are involved in the complaint where the match string fits both- example say we have a match string of 'an' and in complaint xyz 2 people involved are Sandy Blah and Andy Blue). Now when I do my join to pull out the complaints that involve any people with a name containing 'an' the result set returns the same complaint twice, one for Sandy Blah and the other for Andy Blue). Since I am only interested in certain complaint details and not interested in having the actual people involved returned in the result set, I would like to return only 1 row for each complaint when more than one match is found for the firstname,middlenames,lastname. I could live with having the duplicate rows and process them easily application side, but I am sending a lot of duplicate information over the wire for nothing and would like to optimize this.

I hope I have described this well enough and would greatly, greatly appreciate any help you can provide.

Best Regards.


View Replies !   View Related
Getting A Query Result Into A Textbox
This query should always return 1 row with columns visid, cid, visdate, comment. How can I get the value of visdate in textbox1?  This already works when the query is in a vb sub but I want to know how to do it this way too.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"     ConnectionString="<%$ connection string here%>"     SelectCommand="SELECT * FROM [vis] WHERE ([visID] = @visID)">
   <SelectParameters>      <asp:ControlParameter ControlID="Label1" Name="visID" PropertyName="Text" Type="Int32" />   </SelectParameters></asp:SqlDataSource>
<asp:TextBox ID="TextBox1" runat="server" Style="left: 117px; position: relative; top: 160px"></asp:TextBox>

View Replies !   View Related
Query Result As Excel
hi,
I am trying to send a query result from SQL server 2000 as Excel format and send it to an e-mail address every week without using SQLMail ? How Can  i do this
( Already i use ASP.net as an interface to this application , can i do it by asp.net
Thanks

View Replies !   View Related
Is There A Way To Display This Query In 1 Result Set?
I am trying to count the number of Part that is repaired and those that is not repaired, is there a way to combine the following into one result set instead of returning 2? The bold line is the only condition that's different between this 2 query.
I want to display these fields: date_complete, part_categoryid, part_model, repaired, not_repaired
/* parts being repaired */select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model, count(DISTINCT part_id) as repaired from  tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partidwhere (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')and part_replaced = 0and (part_flag_nff = 0 and part_flag_ntf = 0 and part_flag_beyondrepair = 0)group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid,part_modelorder by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0)
/* parts completed but not being repaired */select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model, count(DISTINCT part_id) as not_repaired from  tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partidwhere (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')and part_replaced = 0and (part_flag_nff = 1 or part_flag_ntf = 1 or part_flag_beyondrepair = 1)group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid, part_modelorder by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0)

View Replies !   View Related
Sort Result According The Query
I have a query:
SELECT *
FROM Mobile_Subscriber
WHERE (sub_ID IN (17, 2, 19))

The result return:
sub_ID sub_name
2 John
17 Alice
19 Eddy

But what I want is:
sub_ID sub_name
17Alice
2John
19Eddy

Is it possible to return the result order by the query with: sub_ID IN (17,2,19)?

View Replies !   View Related
Column Name As The Result Of A Query?
Simple example would look like that in MS SQL

SELECT 'a' AS (SELECT language_name FROM language WHERE language_id = 1)

So that the display is

English
a

as we assume that

SELECT language_name FROM language WHERE language_id = 1

returns only English

I have tried to that with a variable but it does not work

declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as @that

LOL

I just tried another way as i was going to post

declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as "@that"

and it worked!

Posting anyway so people might answer with a better solution with no variable

Thanks a lot

Mordan

View Replies !   View Related
Query Result Sent Via E-mail
Hi all,I've a query running under MS SQLServer2000 (Stored Procedures).I'd like to send it's result via e-mail, formatting the output in txt formor html form.Someone knows how to do it ? Have you got examples of code ?Thanks for the adviceMarco Garutti

View Replies !   View Related
How To Query This Table Into The Result Like This?
im not very good at sql but need to query the database to use in myprogramming script.if the database is just like thisid name parent_id1 A null <----- root2 B 13 C 14 D 15 E 26 F 27 G 38 H 49 I 410 J 411 K 9the data in the above table is just like some sort of tree datawhich have parent and child nodeand now,how to query into the result like thisthe deepest node is in level 4what i want is how deep this tree data is?anyone show me the query script or store procedure to find theresult please?many thanks in advance,--Posted via http://dbforums.com

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved