Hey
Is it possible to search for a column without a value?
$query="select id from table1 where col2=''"; (this didnt work, but how do I do it??)
I need the id for the row that has the col2 empty.
:)
Hello,
I have a SQL database where I am attempting to perform a complicated query that I cannot seem to figure out. I am using SQL Server.
I have 4 tables (TableA, TableB, TableC, and TableD). TableA and TableB are guaranteed to have a relationship.
TableC and TableD are guaranteed to have a relationship.
The trick is, I need to link between TableA and TableC essentially using a LEFT JOIN. I need to retrieve all of the values from TableA regardless and the information from TableC and TableD if there is a link, if there isn't a link, then the values from TableC and TableD need to be empty strings.
Does anyone know how I can do this? I've been trying for the last 5 hours without any luck. I feel I'm close, but there is something I feel I'm overlooking.
Thank you SO much for your help!
Hello,
I have a transformation in which the column of data at the flat file source is nine characters long, and typically contains a string of six or seven zeros with a non-zero number in the last two or three characters. If none of the records in that column were an empty string, I think I could get away with this:
DTSDestination("TTLCrd") = CInt(DTSSource("Col004"))
The destination is a SQL Server 2000 table, and the column is of type Integer. What do I do when Col004 is an empty string? I've tried a couple of different IF statements, but they have not worked. Empty strings need to become zero values.
Thank you for your help.
cdun2
************* Edited by moderator Adec ***************
Inserted missing < code></ code> tags. Always include such
tags when including code in your postings. Don't force the
moderators to do this for you. Many readers disregard
postings without the code tags.
**************************************************
Well met,
Let's say I have a web form that allows users to insert and update records in a SQL database. Is it better to set empty web controls (textbox, etc.) to DBNull or let it go as an empty string into the database?
Example code:
if (tboxNAME.Text == "")
{
sqlCommand1.Parameters["@NAME"].Value = DBNull.Value;
}
else
{
sqlCommand1.Parameters["@NAME"].Value = tboxNAME.Text;
}
Is the above overkill? It seems like it would be a good idea to set fields which the user empties back to Null rather than an empty string.
Thanks,
-Tony
i am makin an address block
trying to make it (address1 + ' ' + address2 + ' ' + address3) as address
is there a way to get rid of the address2 if there is nothing in it
I have always (or at least intended to) treat NULL and empty strings separately in my SQL querying history. Now I have run across something that mystifies me (but probably shouldn't) that I would like an explanation for.
Consider this bit o' code:
DECLARE @ORDER CHAR(10)
SET @ORDER=
(
SELECT NULL
)
IF @ORDER <> ''
PRINT 'Not an empty string'
IF @ORDER IS NULL
PRINT 'It is NULL'
Run this and you will get:
It is NULL
I was expecting:
Not an empty string
It is NULL
Why is NULL not passing the 'not an empty string' test? In other words, how does NULL = '' ? Is NULL cast to an empty string for this comparison?
Am I right in assuming that when I have a column where all fields contain NULL, this does not increase the total data storage size if my database? Also, what kind of impact would it have on performance?
And what if I inserted "" in varchar columns? I would think the increase in size would be marginal?
The reason I'm asking is that I want to use an existing table and stored procedures for another purpose, but only need half of the columns. But it would significantly simplify application development.
I am having data where there are empty string in the business keys which should be used for Slowly changing dimesnion type 2, how do i over come this as due to empty strings i am getting new rows even though the rows havent really changed.
example of data is name and salary are business keys
name salary age address
dev 23 klddldldlk
sdfg 24 34 kdlddlkd
when the same is given as input the row
dev 23 klddldldlk
is coming as anew row where it already exists how do i over come this
Will the fuzzy grouping task match a null value to an empty string (or spaces)? I've got 5 columns I'm matching on, and one of them may be null for certain rows but an empty string for others. Given the 4 other columns may match, will this difference stop similar columns being grouped together?
(Someone's modified my grouped data since it was deduped, which takes a while, and I'm hoping for a quick answer on this).
Thanks in advance.
Ben
The sql below is a simple example of LIKE query with a table of wildcard strings. I hope this helps others.
declare @LikeTable Table
(
LikeValue nvarchar(50)
)
insert into @LikeTable (LikeValue) values ('%blah')
insert into @LikeTable (LikeValue) values ('%abc%')
insert into @LikeTable (LikeValue) values ('%edf%')
insert into @LikeTable (LikeValue) values ('car%')
insert into @LikeTable (LikeValue) values ('%ome%')
declare @CompareValue nvarchar(50)
set @CompareValue = 'some value'
select * from @LikeTable where (@CompareValue like LikeValue)
All, Using access 2003 frontend and sql server 2008 backend. I have an append query to insert 80000 from one table to an empty table. I get an error:
"Microsoft Office Access set 0 field(s) to Null due to a type conversion failure, and didn't add 36000 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations."
I know this error normally comes if there are dups in a field that doesnt allow.
I want to check if a table is empty using query code?
How to do this?
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
I am using query strings to pass data from web form to web form and I have two questions. First if i use a asp:sqldatasouce to fill up a grid view and I have my select command set to a paramater that get whatever is in the query string it will not work because whatever is in the quers string gets a " ' " put in front and in the back of it. So if the query string was 5 whene it does the sql statement it sets my paramater = '5' not just 5 so its wont work. How can I fix this using the asp:sql datasource my aspx code looks like
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Rental PropertiesConnectionString %>"
SelectCommand="SELECT * FROM [APARTMENTS] WHERE ([PROPERITY_ID] = @PROPERITY_ID)">
<SelectParameters>
<asp:QueryStringParameter Name="PROPERITY_ID" QueryStringField="key" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
Also since i have not been able to get around this so i have been wrting code in vb.net to attact a dataset to a grid view to populate it based on the query string i would do the following in vb.net to get ride of the ' in front and behind the query string
Dim y as string = "'" // " ' "
key = Request.QueryString("key").trim(y.tochararray)
But now i am doing another project in C# and I have re-written the above code in C# it will run but it will not take the " ' " out form infront or behind key. How does this need to be changed up?
string y = "'";
key = Request.QueryString["key"].trim(y.tochararray());
Hi, I have a SQLDataSource binding to a GridView and can come to the page either with or without a query string attached:
/ProjectManagement/reporting/project.aspx
/ProjectManagement/reporting/project.aspx?portfolio=3
When it comes with a query string, I can see in SQL Server profiler it executes and I get all the right data. When it is an empty string, or with no "?portfolio=" on it, it won't even execute against SQL server. Any ideas?
<asp:GridView ID="grid" runat="server" Width="600px"
ShowHeader="false"
AutoGenerateColumns="false"
DataSourceID="DSportfolio"
AllowSorting = "true"
AllowPaging = "true">
<Columns>
<asp:TemplateField>
<ItemTemplate>
.............
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="DSportfolio" ConnectionString="<%$ AppSettings:SQLConnection1 %>"
SelectCommand="uspSELECT_PROJECT"
SelectCommandType="StoredProcedure"
runat="server">
<SelectParameters>
<asp:QueryStringParameter Name="p_PORTFOLIOID" QueryStringField="portfolio" />
</SelectParameters>
</asp:SqlDataSource>
Thanks,
James
Hi, I am having a problem looking up querystrings in my DB, I have the following code
<asp:SqlDataSource ID="SqlData_products" runat="server" ConnectionString="<%$ ConnectionStrings:ProductDatabaseConnectionString2 %>" SelectCommand="SELECT * FROM [tbl_subProduct],[tbl_subCategory],[tbl_topCategory],[tbl_Material],[tbl_topLevelProduct] WHERE numSubCategoryID = @Category OR numMaterialID = @Material OR txtOrderCode = @Keyword OR txtMovexCode = @Keyword OR txtUKMapCode = @Keyword"> <selectparameters> <asp:QueryStringParameter Name="Category" QueryStringField="SearchCatString" /> <asp:QueryStringParameter Name="Material" QueryStringField="SearchMatString" /> <asp:QueryStringParameter Name="Keyword" QueryStringField="SearchKeyString" /> </selectparameters> </asp:SqlDataSource>
My Querystrings are in the VB file:- SearchCatString = Request.QueryString("txtSelCat")
SearchMatString = Request.QueryString("txtSelMat")
SearchKeyString = Request.QueryString("txtKeyword")These come from a previous page, where a dropdown list copies the ID of the selected item into a text box. My problem is that this is not working please help
I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.
I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.
This is the basic logic goes soemthing like this:
select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database
The output would be
Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/
So far, nothing I have tried has worked.
Any ideas?
Let me start by asking that no one try to convince me to use Stored Procs. The examples below are a lot more simplistic then my real world code and it just gets too complicated to try to manage the quantity of SPs that I would need.
I have an application that displays a lot of data and I've created a system for users to filter the data using checkboxlist controls, dropdown controls, etc. From this, I have a "core" query that selects the fields that display in my GridView. It has a base Select clause, From clause and Where clause. From this I then add more to the Where clause to apply these filter values.
Here's an example "core" query:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCode
From this if a user want's to only display profiles from NC, they could select that from the CBL and the query would be modified to:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.State IN ('NC')
My code would add the last line above since the user specified that they only wanted NC profiles.
This is very simple and I have this already going on with my application. Here's the problem. In order to accommodate all of the various filters, I have to inner join and left join a bunch of various tables. Many times I include tables that have no data to display or filter on and therefore impacts performance. Here's an example:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentID
From the query above, I have included the Agent table that holds the agent's contact information. One of my filters allows the user to type in an agents name to find all profiles assigned to it. Here's what that would look like:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentIDAND Agent.Name = 'Smith, John'
You can see now that it was necessary to have the Agent table already joined into the query so that when I used the agent name filter, it wouldn't crash out on me.
The obvious thing would be to only include the Agent table when searching for an agent name. This is ultimately what I'm looking to do, but I need a solid method to go about doing this. Keep in mind that I currently have 16 tables in my "core" query and many of those are not needed unless the filters call for it.
If anyone has any ideas on how to simplify this process I'm selcome to suggestions. We're using SQL 2000, but are looking to upgrade to SQL 2005, if that makes any difference. I know that the way I do table joins is compliant with SQL 2005 and I'm certainly open to suggestions that will make it forward compatible.
This app is using .NET 2.0 and written in VB.NET. Thanks for any help!
Hi all,
If I have a query string that is to be stored in a database, for example
Code:
SELECT prod_id, prod_name, prod_desc FROM products WHERE prod_id = 'variable'
how can I put a variable identifier into this string so that when I need to run the query I call it from the database and simply insert the relevant variable in the correct place.
Is there an appropriate way of doing this in MS SQL Server?
Thanks
Tryst
Hi friends,please help me in selecting values from the tablethe record is as follows:Id HomePhone WorkPhone Mobile Email20 2323223 323232232 Join Bytes!i have to select values as follows.Id DeviceType DeviceInfo20 HomePhone, Mobile, Email 2323223, 323232232, Join Bytes!the one solution is:select'HomePhone, Mobile, Email' AS DeviceTypeHomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email ASDeviceInfofrom table where Id = 20but here the work phone number is not available so that informationhas to be truncated...Thanks in AdvanceArunkumar.D
View 3 Replies View RelatedI am building myself a datadriven menu control and I have got one table where I store all menu items as rows. On each row there is a column for roles, in this column I have added one or several roles as a string. Today I can retrieve all menu items (rows) for a requested role, but how can I retrieve menu items for two or more roles? I have each role inserted as rows in a temp table (@Role), if that makes it easier?DECLARE @Role TABLE ( role varchar(15)) SELECT SiteMap.Id, SiteMap.Title, SiteMap.UrlFROM SiteMapWHERE (CHARINDEX(@Roles, SiteMap.Roles) > 0 ) Regards, Sigurd
View 2 Replies View Relatedhi,
please check this query and reply back with the appropriate solution.
len(ltrim(rtrim(exec('select' ' ' + 'pay' +convert(substring(@y1,3,2), varchar 2)))))<>0
here the concept is concatenating two string then that result is used as column and retreiveing data.but this is considering it as string instead of column.
can anyone give an appropriate solution.
Regards,
Uma.
Hi Friends,
I have one query that i have to Replace the Empty Value of a filed with some other value how can i do in SQL??
ID Phone Name
1 122 abc
2 xyz
3 444 mmm
4 525 ccc
5 nvb
Now i want replace the blank (Empty) filed with some charaters Numbers how can i do that?? any Help
Ashu
Hey, i have multiple search strings and I don't want to keep track of the search strings in SQL format.
As in, i don't want to do this anymore:
Select *
From tbl_Name
where FirstName = 'John' or
FirstName = 'Mike' or
FirstName Like 'Jen%' or
...
and the list continues for at least a 100...
what i want to do is store all the search values in a table and then join on that table as a search...
Create Table as tbl_SearchValues
[FirstName] as Varchar(50)
Insert Table tbl_SearchValues values ('John', 'Mike', 'Jen%')
Select *
From tbl_name n Join tbl_SearchValues s
on n.FirstName Like s.FirstName
This doesn't work cause I tried it... but does anyone know a different workaround??
Hi All,I have what seems to me to be a difficult query request for a databaseI've inherited.I have a table that has a varchar(2000) column that is used to storesystem and user messages from an on-line ordering system.For some reason (I have no idea why), when the original database wasbeing designed no thought was given to putting these messages inanother table, one row per message, and I've now been asked to providesome stats on the contents of this field across the recordset.A pseudo example of the table would be:custrep, orderid, orderdate, comments1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer askedfor a brown model2, 10002, 2004-04-12, :Comment 3:Comment 4:1, 10003, 2004-04-12, :Comment 2:Comment 8:2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled orderSo, what I've been asked to provide is something like this:orderdate, custrep, syscomment, countofsyscomments2004-04-12, 1, Comment 1, 12004-04-12, 1, Comment 2, 22004-04-12, 1, Comment 3, 12004-04-12, 1, Comment 8, 12004-04-12, 2, Comment 1, 12004-04-12, 2, Comment 3, 12004-04-12, 2, Comment 4, 22004-04-12, 2, Comment 6, 22004-04-12, 2, Comment 7, 1I have a table in which each of the system comments are defined.Anything else appearing in the column is treated as a user comment.Does anyone have any thoughts on how this could be achieved? The endresult will end up in an SQL Server 2000 stored procedure which willbe called from an ASP page to provide order taking stats.Any help will be humbly and immensely appreciated!Much warmth,Murray
View 7 Replies View Relatedhi everyone,
i was just wondering, is there anyway to empty a table...like, in mysql u can go EMPTY TABLE
and it resets the identity stuff, so your id's start at zero again...?
Thanks, Justin
I am wanting to make a query to find out if there are any columns in a table that are null for all records.
So far I am using a cursor that holds all the column names of that table and then a stored procedure that would use a loop to cycle through all the column names in a query to essentially count the number of nulls for each column and then an If statement to check to see if the #of nulls = to the number of records.
What isn't working is my query to count the number of nulls...Here is my query
select count(*)
from XYZ
where @cName is null
Ive tried declaring cName as a sysname, and varchar, neither work.
Also, I've tried using COL_NAME(OBJECT_ID('XYZ'), someColumnIndx)...I think this would be the better choice just because I wouldn't need to use a cursor, but both aren't working.
I am a bit new to MSSQL and don't know my way around the system tables. Is there a sys table that would give me this info so I wouldn't need to perform a query?
Any info would be great.
Hi Guys,
I'm wondering if an idea I'm playing with is feasible and if so, how you would recommend implementing it.
Let's say I have a Dictionary table, 2 columns:
Word | Definition
And I have a string - "The cat sat on the dog"
If there's a definition for "cat" in the dictionary table, I want to alter the string so it becomes "The >>cat<< sat on the dog"
At the same time, if there's also a definition for "dog" then my string now becomes "The >>Cat<< sat on the >>Dog<<"
The idea being that when I manipulate the data in my ASP I can replace() the >><< with specific HTML code. (I'm trying to recreate the "in text" advertising thing that lots of people seem to be using - but not doing adverts, just information for our users - Someone hovers over a highlighted word, and with a little bit of Ajax, I can pull the definition out...
I'm not sure (but I'm suspecting) that it would make more sense to do this as I'm storing the string in a table, rather than as I'm pulling it out ready for use (don't want to be slowing my end users down )
Any ideas?
Thanks in advance
-Craig
I ran across this technique being used in an application the other day. It seems not a good idea to me. What do you think?
1. The proc builds a basic query, nothing real fancy, into a string variable called @SQL defined as varchar 2000. Depending on the result desired, the group by clause can be one of three different sort orders.
2. The string is executed via EXEC @SQL.
It seems to me that the whole process can eliminate the EXEC and just use some other construct. All the parameters are passed in via the initial call to the stored proc. It also seems that every time this is executed it will result in a new query compile and cache useage, no matter what. Wasteful? Should I take the developers aside and knock heads? I think the app was coded by some folks who were rookies then but may be willing to crack open their code. Or, am I the one that is a rookie?
Thanks for your inputs.
I am creating three tables in a DataSet using three stored procedures and I need a way to know that either the tables have at least one row or preferably which if any has no rows. How can I do that?
using three SP's, one for each table:SqlCommand cmd = new SqlCommand("CompanyCheck", con);SqlCommand cmd2 = new SqlCommand("ContractorVerify", con);SqlCommand cmd3 = new SqlCommand("StoreLocation", con);
cmd.CommandType = CommandType.StoredProcedure; cmd2.CommandType = CommandType.StoredProcedure; cmd3.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CompanyID", CompanyID); cmd2.Parameters.AddWithValue("@CompanyID", CompanyID); cmd3.Parameters.AddWithValue("@CompanyID", CompanyID); SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd; DataSet ds = new DataSet(); try { da.Fill(ds, "CompanyInfo"); da.SelectCommand = cmd2; da.Fill(ds, "ContractorInfo"); da.SelectCommand = cmd3; da.Fill(ds, "StoreInfo"); } catch { throw new ApplicationException("Data error"); } finally { con.Close(); }
Thank you
I have a particular production database that on a few recent occasions has suddenly had one of it's tables empty of content. Now there are no jobs or triggers in place neither is any stored procedures that could perform a delete or alter table operation in place. The permissions have been set not to allow this either. The datatype is text only. What is intriguing is the QA server has an exact mirror and this dosen't happen. Has anyone come across this sort of phenomenon?
View 2 Replies View RelatedI can use the Design Table option in every database and any user table on my server except for two databases which were transfered from a SQL 6.0 server.
I can't even use Design Table to create a new table in these databases. I get the Design form up and it allows me to add columns, but when I try to save the changes I get the following error message:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'full'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'full'.
One thing I have noticed is under Users for the database, all my users come up as 'via group membership' for the Database Access column for the database I am having problems with. All other databases the users come up as 'Permit' for the Database Access column. I assume this has to do with security. But I have tried everything to change this. I can not get a user to come up as 'Permit' under these two converted databases.
Does anyone have any ideas?
I'm running SQL 2000 (SP3) on top of Windows NT 4.0 (SP6).
I have a couple of processes that look at the sysperfinfo table every day to collect some performance/metric type information. It works for all my other servers (mostly SQL 2K/Win2K) except for this one.
Why is the table on this one server empty?
I do have one other SQL 2K/Win NT server. The sysperfinfo table on that one is fine.
the only other clue/difference is that the server which has the empty table is a named instance install of SQL 2K.
Any hints?
Regards,
hmscott