Empty Field Query
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
View Complete Forum Thread with Replies
Related Forum Messages:
Empty Field
I have a comment field with data or no data. The problem is when I transfer the data into AS400 if field is empty it translates to end of record, however in reality it is not. How do I fix this issue?
View Replies !
Empty Field From Web Service
All of our RS report have web service datasources. If the first row from the datasource is empty, that is: <Legacy /> Then I, get the following error and the column is not handled by the report: [rsMissingFieldInDataSet] The data set €˜Journal€™ contains a definition for the Field €˜Legacy€™. This field is missing from the returned result set from the data source. [rsErrorReadingDataSetField] The data set €˜Journal€™ contains a definition for the Field €˜Legacy€™. The data extension returned an error during reading the field. There is no data for the field at position 20. Any suggestion to get around this? Phil
View Replies !
Checking If A Field Is Empty
i want to check if one of the fields is empty. however i cant seem to get it working string strSQL = "select * from 15Course_Listing where Package_listingNo =''"; it does not return anything, however i have empty fields in package_listingNo i have been trying to get around this for some time and its killing me. need help please
View Replies !
Required Field Empty - How Could This Happen?
Hello! I have a web form in VB.NET & ASP.NET 1.1, with data being saved to a SQL 2005 database with multiple tables. The primary key on all these tables is a unique identifying number that the user enters. However, one record in this database (across all tables) has no identifier. All other information seems to have saved without problem, but there's no identifier, which is very wierd for several reasons: 1) the form should not allow the user to proceed without it; 2) all the tables in the database do not allow nulls for the column, and 3) it's the primary key! Any thoughts on how this might have happened? Thanks! Kaiti
View Replies !
How To Update An Empty Date-type Field
Hi, I have update function that updating sql database table. In the table I have fields like: name, address, phone, …, install_date updateScreen(txtSName.text, txtSLocation.text, txtSPhone.text, txtSAddress.text, txtSPostal.text, ddSCity.SelectedItem.text, ddSCountry.SelectedItem.text, txtSOwner.text, txtSInstall.text ) The function work fine except the part whent install_date field is empty then I have the following message: System.InvalidCastException: Conversion from string "" to type 'Date' is not valid. Ii would like to use the same update function to make an updates even when date field is empty. If someone has any idea how make it work, I would appreciate that Alex
View Replies !
SQL - Updating Empty Text Field Is Enterring -1
I'm trying to do a simple update like I have done countless timesbefore.However, when I update the empty fields in this table it places a -1into the field rather than the enterred value.It is indexed with duplicates allowed. It says that zero lenght isset to yes.What can I do about this?if Request.form("ouserid") <> "" Thenvar2 = " and userid = '" & Request.form("ouserid") & "' "Elsevar2 = " and userid is null "End ifif Request.form("orepid") <> "" Thenvar1 = " repid = '" & Request.form("orepid") & "'"Elsevar1 = " repid is null "End if"Update [Website Settings] set userid = '" & REquest.form("userid") &"' and repid = '" & Request.form("repid") & "' where " & var1 & var2& var3Thanks!
View Replies !
CeWriteRecordProps Failling With A Empty FILETIME Field..
Hi All, Does anyone have already made an application that writes some data into the EDB Pocket database ? I´m trying to insert a record with a empty FILETIME field and I´m getting the Error 87 (INVALID_PARAMETER), I´ve tested with some date in the field and the record is added sucessfull, it seems that the database doesn´t accept anymore empty FILETIME fields? Is it true? I didn't find anything in the docs.thanx in advance.
View Replies !
How To Pass Null Value To The Database If A Form Field Is Empty?
How can I pass into the database (@User_fax = null) if the fax form field is empty, from a command type Stored Procedure? For example: Dim CmdUpdate As New SqlCommand("Form2_NewUser", strConnection) CmdUpdate.CommandType = CommandType.StoredProcedure CmdUpdate.Parameters.Add("@User_fax", SqlDbType.char, 9) CmdUpdate.Parameters("@User_fax").Value = fax.Text() ... strConnection.open() CmdUpdate.ExecuteNonQuery strConnection.close() And, the stored procedure inside Sql server: USE market1 GO ALTER PROC Form2_NewUser @User_id bigint, @User_fax char(9),... AS SET NOCOUNT ON UPDATE Users SET User_fax = @User_fax, ... WHERE User_id = @User_id SET NOCOUNT OFF GO Thank you, Cesar
View Replies !
Possible To Create An OPTIONAL Multi-value Field That Can Be Left Empty?
Greetings, I have several reports for which the user has asked to have an optional muti-value parameter. They want to be able to select zero, one, many, or all values in the parameter list. The parm list is created through a query and the values are not static. I would like to allow the user to leave the muti-value field empty if they want to allow all values to appear on the report. I've read some discussion about populating a multi-value default with the same query that produces the multi-value list values - presto, everything is selected. However, this is not a desirable solution for me because I "echo" the users parameter selections in the report heading. Selecting all values (and some parms have a lot of values) would cause the "parm feedback" section to grow large and unreadable. In short, I don't want to tell the user they have to select everything when they really want to select nothing. Is there any way to have a muti-value parm that won't insist the user select one or more values? Thanks, BCB
View Replies !
Field Names Missing In MDX Data Set When Using NON EMPTY Clause
So I have an MDX query in an SSRS data set. Here is my MDX query: Code SnippetSELECT { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, { ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS This query returns 4 fields. Media Property, Promo Code Description, Gross Sales, and Net Sales. For the given query the measures are empty or null. I do not want any data to show up when the measures are null so I put in NON EMPTY clauses before the COLUMNS and before the ROWS. So now my query looks like this: (I only added the NON EMPTY clauses) Code Snippet SELECT NON EMPTY { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, NON EMPTY{ ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS Adding the NON EMPTY returns nothing... not even field names. This is a problem because, I have a table in the report that looks at this data set and when there are no fields, the report can't run. How can I still have NON EMPTY functionality and still show the fields? Is this a problem in SSRS?
View Replies !
Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?
i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this: IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE('')) Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression" thanks
View Replies !
Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?
i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this: Code: IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE('')) Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression" thanks
View Replies !
What Is The Difference Between Updating Null Value Vs Empty String To Varchar/char Field?
Hi, What is the difference updating a null value to char/varchar type column versus empty string to char/varchar type column?Which is the best to do and why? Could anyone explain about this? Example: Table 1 : tCountry - Name varchar(80) nullable Table 2 :tState - Name char(2) nullable Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state So,when the state is not present for the country ,i have two options may be - null,'' tCountryDetails.State = '' or tCountryDetails.State = null?
View Replies !
Empty Parameters For SQL Query
Hi, I have a simple stored procedure which expects three parameters. However I want to return results regardless of whether all three parameters are supplied. This is a simple search engine, which if only one parameter is supplied then the other two will be set to wild card values. When I run the stored procedure I have it fails to return the correct values ( 0 records ). I am presuming SQL Server doesn't replace the empty parameter value with a wildcard value in the sp "like" statement. Can anyone help me out with this? sp_WWW ====== select distinct(substring(bus_name,1,1)) as letter from member where bus_name like '%nissan%' and bus_category like '%@search_category%' and bus_address like '%@search_location%' order by letter asc exec sp_WWW "myCompany","","" fails to return anything. But if I use select distinct(substring(bus_name,1,1)) as letter from member where bus_name like '%nissan%' and bus_category like '%%' and bus_address like '%%' order by letter asc I get the expected 3 records returned. TIA Tony
View Replies !
Concatination Of Empty Field And Removal Of &&"&&" Symbols
Hello... I am going crazy trying to figure out how to do this. I have a flat file which I am massaging the data and loading into a table here is an example of a line out of the flat file: "ABC NUTRITIONAL PRODUCTS","550","","","N","FAIR OAKS","","","COLORADO SPRINGS","C0","","","","","","","","A","","","" My problem is that I have one field which is this address in a concatinated form. The fields that do not apply to this entry are suite#, floor# and other columns which are designated by the "" characters. The final concatinated addres field looks like this: "550""""N""FAIR OAKS""" I would like to remove the "" characters in the concatinated string. I just don't know the best way to do this? I was told DTS had a way of removing the "" from the flat file source. Since I have not used DTS extensively I am not sure if this is true. I was wondering how in SSIS I could go about removing the "" marks without removing the "" say if someone is quoted eg. John said "This is only a test". Removing the quotation marks in this instance would be changing the data. I am not sure how to do this and any help or advice is greatly appreciated! Thank you...
View Replies !
Query Help, What Columns For All Records Are Empty
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.
View Replies !
Need Query To Determine If Database Empty
I would appreciate a query to determine if a database is empty? Empty meaning that no user tables have any data in them. I expect the system tables in the database to have data in them. An alternative idea, but not exactly the same thing, would be the ability to determine it the database is in its pristine state (creation state, but not yet user modified). Thanks in advance for your advice/help!! Gary A.
View Replies !
Query On Empty Strings In A Table
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. :)
View Replies !
Aggregate Task When Query Returns Empty.
To create a strike file for a text file output, I piped the output of a query throught a mulitcast. One output is the actual flat file, the other output is the strike file. The next task for the strike output is an aggregate count and sum functions. If the original query returns no records, the count shows zero, but the sum shows NULL. Further down the process I have to pad out the values with zero's, but that NULL is causing problems. How do I convert the Null to either an empty string, or a value of zero? Thanks
View Replies !
MDX Query To Display No Records For Empty Rows
Hi, I am facing with the following problem. I am using bar chart to display my report. My MDX query is as follows: SELECT NON EMPTY { [Measures].[SUM_COUNT] } ON COLUMNS, TopCount ( Filter ( {[DIM].[NAME].[NAME]}, [Measures].[SUM_COUNT] <> 0 ) , 10, [Measures].[SUM_COUNT] ) ON ROWS FROM [USAGE] where <criteria> I want to show the topmost 10 records. For some criteria I get the results in the chart. But for some criteria or say for wrong criteria, there are no records. In such a case the X-axis contains all values for {[DIM].[NAME].[NAME] and value for the Y-axis is all 0. Its kind of blank report it will restrict to 10 records. In such a scenario I want to show a message to the user saying "No records found", which I have set in the No Rows property of the chart. If I remove the TopCount clause then I get the above message, which is obvious. So how do I acheive the same message but at the same time limiting the records to 10? How can I acheive this in my scenario? Can something be done at the query end? any help is appreciated. Thanks in advance!
View Replies !
Dataset Becomes Empty After Adding Query Parameter
Hi all, hope anyone can help me on this one. I have a parameterized report based on a MDX query (so no drag and drop of filters and fields). Now i have to ad a parameter. So i open the query parameter dialog box and i ad my parameter. when i click ok , my dataset becomes empty , resulting in an error on my report saying that the fields on the report cannot by found and should be in the scope of the dataset. Has anyone experienced this problem before, or am i forgetting something here? thanks in advance Steven J
View Replies !
Data Columns Empty In Management Studio But Not Query Analyzer
we have a database hosted online on sql server 05. at work, we have a sql 2000 query analyzer on a machine, and sql 2005 management studio on a machine. both machines can connect to the database, both can pull up the data from a certain table. but there are two columns that display info in query analyzer that show up as empty on management studio. not null, just empty. any ideas why?
View Replies !
Insert Query Fails (if Form Fields Left Empty)
Dear All, I have created a table in my SQL server database, the problem i am facing is my insert query fails if i leave any form field empty (leave it blank). On my back-end table, only one field is mandatory, and others have been set with the constraint "allow null". As per our business requirement, except one value is complusory while others are optional. If I enter all values in the form it works perfectly fine. Can you see in the code below - where am i possibly going wrong ? <script language="VB" runat="server" > Sub Page_Load(Src As Object, e As EventArgs) If Page.IsPostBack Then Dim ConLath As SqlConnection Dim comLath As SqlCommand Dim insertcmd conLath = New SqlConnection("Data Source=SQLas;Initial Catalog=settle;User ID=sa;Password=password") ConLath.Open() insertcmd = "Insert into His_set values (@t_d,@s_p,@p_s,@v_oq,@i_oq,@v_qn,@i_qn,@v_qw,@i_qw)" comLath = New SqlCommand(insertcmd, ConLath) comLath.Parameters.Add(New SqlParameter("@t_d", SqlDbType.DateTime, 12)) comLath.Parameters("@t_d").Value = trade_date.Text comLath.Parameters.Add(New SqlParameter("@s_p", SqlDbType.Decimal, 8)) comLath.Parameters("@s_p").Value = sett_price.Text comLath.Parameters.Add(New SqlParameter("@p_s", SqlDbType.Decimal, 8)) comLath.Parameters("@p_s").Value = post_close.Text comLath.Parameters.Add(New SqlParameter("@v_oq", SqlDbType.Int, 8)) comLath.Parameters("@v_oq").Value = vol_oq.Text comLath.Parameters.Add(New SqlParameter("@i_oq", SqlDbType.Int, 8)) comLath.Parameters("@i_oq").Value = oi_oq.Text comLath.Parameters.Add(New SqlParameter("@v_qn", SqlDbType.Int, 8)) comLath.Parameters("@v_qn").Value = vol_qn.Text comLath.Parameters.Add(New SqlParameter("@v_qw", SqlDbType.Int, 8)) comLath.Parameters("@v_qw").Value = vol_qw.Text comLath.Parameters.Add(New SqlParameter("@i_qn", SqlDbType.Int, 8)) comLath.Parameters("@i_qn").Value = oi_qn.Text comLath.Parameters.Add(New SqlParameter("@i_qw", SqlDbType.Int, 8)) comLath.Parameters("@i_qw").Value = oi_qw.Text Try comLath.ExecuteNonQuery() Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else Message.InnerHtml = "ERROR: Could not add record, please " _ & "ensure the fields are correctly filled out" Message.Style("color") = "red" End If End Try comLath.Dispose() ConLath.Close() End If End Sub </script>
View Replies !
MS ACCESS Query Returns Empty Recordset From Odbc Linked Sql Server Tables
Hi,I'm developing a asp.net application with a MS Access 2000 database (for reporting purposes etc.) using an ODBC link. Some tables of it are linked (another ODBC link) to a SQL Server 2005 database. I need to show some records in my application coming from a MS Access query running on LINKED tables (not imported tables) from the SQL database.Problem: when I IMPORT the tables, recordset is complete, when I LINK the tables, the recordset is empty (no records, only colum heads, no error message). Probably this is caused by the differences in SQL syntax (thank you Bill ;-)).This is my query in Access:SELECT IIf([dbo_cicntp.cnt_email]="",LCase([dbo_cicmpy.cmp_e_mail]),LCase([dbo_cicntp.cnt_email])) AS EMAIL, UCase(CStr([dbo_cicmpy.ID])) AS CODE, dbo_cicmpy.cmp_name AS BEDRIJF, [cnt_l_name] & " " & [cnt_f_name] AS FAMILIENAAM, dbo_cicntp.Gender AS MVJ, IIf(UCase([dbo_cicntp.taalcode])="NL","NL",IIf(UCase([dbo_cicntp.taalcode])="FR","F","E")) AS TAAL, IIf([dbo_cicntp].[active_y]=1,True,False) AS ACTIEF, dbo_cicmpy.SubSector AS VESTIG, "CATEGORIE" AS CAT, dbo_cicntp.cnt_f_tel AS TEL, Trim([dbo_cicmpy.cmp_fadd1] & " " & [dbo_cicmpy.cmp_fadd2] & " " & [dbo_cicmpy.cmp_fadd3]) AS STRAAT, dbo_cicmpy.cmp_fpc AS POSTNR, dbo_cicmpy.cmp_fcity AS GEMEENTE, dbo_cicmpy.cmp_fctry AS LAND, etc...FROM (dbo_cicmpy INNER JOIN dbo_cicntp ON dbo_cicmpy.cnt_id = dbo_cicntp.cnt_id) INNER JOIN dbo_pred ON dbo_cicntp.predcode = dbo_pred.predcodeWHERE (((dbo_cicmpy.DivisionCreditorID) Is Null));Is there any solution?Could this be a solution: make an first query to the SQL database just to gather the "clean" fields (I mean, without IIFs, Lcases, Ucases and other functions unknown by SQL Server I use now) and then adapt my query here above mentioned, and apply my IIF, Lcases and Ucases on the recordset returned by my first query?Thanks a lot for your time.Cl.
View Replies !
Values In LEFT JOIN Need To Be Empty Strings (was &"SQL Query Question&")
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!
View Replies !
Max Of Date Field In Query
I have a table with date fielde.g. SrNo Date 1 1-MAR-2008 2 3-Mar-2008 3 7-Mar-2008 4 10-Mar-2008 5 15-Mar-2008 I need a query to find out Max date which is less than '8-Mar-2008' . i.e i need single output '7-Mar-2008'anybody helpThanks
View Replies !
How To Split The Field In The Sql Query?
I have the field LocationID (string)which has values like "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers, I want to split it like "AZ" ," 001" "AZ","002" "IN" "002" "IN" "004" now i will populate dropdownlist with unique values of "AZ" "IN" according to first dropdownlist how i will populate second dropdownlist? So how to write sql query for splitting? and then populating the dropdownlist ?
View Replies !
SQL DateTime Field Query
I was wondering if someone could help me here with some reporting I'm trying to do. For website visits, I currently use getdate() to have SQL insert the date and time of the visit in the table. I was wondering, is there a way to grab just the month from the field? I would like to chart this and I need to group the visits by month. I'm not sure how I would go about filtering just the month out of the entire date and time fields.
View Replies !
How Do I Query For Particular Criteria In Every Field In The Db?
I need to query a table for a record only if there is a certain text string in ANY OF THE TABLE'S fields. I know of using the WHERE clause with logical OR (e.g. - WHERE field1 = 'xyz' or field2 = 'xyz' or ...) but there is a limit of 40 expressions with an Access database, and I have 102 fields to look through! That's my dillema. I know I may need to go to MSSQL or Oracle, but I'd rather stay with Access. Is there a syntactical way to express the query I need? Or do I have to look somewhere other than Access for my database needs? Thanks for the advice!
View Replies !
Use Calculated Field In Same Query
Right now I have one view that grabs records and sums up related records etc.... and returns a result. So basically it has the ID number and the number I calculated. THen I have another view that takes that number and performs calculations on it into three different columns. Is there any way to make these two view into one without a lot of repetative statements? Here is an example: SELECT (tblTest.Quantity * tblTest.Price) as SubTotal, SubTotal * 1.06 as Total Obviously that doesn't work, but what could I do to get that basic thing to work? Thanks!
View Replies !
SQL Query: Missing Field Value
Suppose in a table, there is some data in a field: 1, 2, 3, 5, 6, 8, 9. I need a sql query which will list the missing numbers: 4,7 (Missing digits) Query: Select * From table1 field ------ 1 2 3 5 6 8 9 Expected query:?? filed1 ------ 4 7 Please help me to get a query which will provide my expected data. Thanks. /Fakhrul(mfhossain@gmail.com)
View Replies !
Query Detail As A Field.
I have two tables(Order and OrderDetail) of master-detail relationship. I have a nchar field in the detail table called itemno. I want to query like: Select Order.OrderNo, Order.Date, SUM(OrderDetail.ItemNo) as ItemNos,.... From Order Inner Join OrderDetail on Order.OrderID=OrderDetail.OrderID Where Order.OrderID=10 so that the resulting field ItemNos will become a string in format "ItemNo01, ItemNo02, ItemNo03,...." How can I write this query in T-SQL? Thanks
View Replies !
Really Simple Query Needs Another Field
I have written this query in SQL select * from wce_contact where (Mail3Date <= '2007-09-25') AND NOT (Mail1Date is NULL ) AND NOT (Mail2Date is NULL ) AND (Mail4Date is NULL ) AND (Mail5Date is NULL ) AND (Mail6Date is NULL ) AND (Mail7Date is NULL ) AND (Mail8Date is NULL ) AND (Mail9Date is NULL ) AND (Mail10Date is NULL ) AND (Mail11Date is NULL ) AND (Mail12Date is NULL ) AND (Mail13Date is NULL ) AND (Mail14Date is NULL ) AND (Mail15Date is NULL ) AND (Mail16Date is NULL) Little bit of background info - We have a database of over 500k websites with email addresses. We use email marketing to contact these sites and everytime they get mailed i need to fill in the MailDate field. Obv Mail5Date means they have been emailed 5 times etc The above qry is trying to find all contacts that have been mailed 3 times and havent been mailed in the last 42 days. I also have an IDStatus field that allows me not to use the contacts that have opted out our mailing or website shutdown etc I try adding this to the bottom of the query AND (IDstatus is Null) But the query runs and finds 0 contacts which i know isnt the case, as when i use our front end database application it produces results. I was wondering if anybody could possibly help me? Thanks!
View Replies !
Sort On Field Constructed In Query
I want to sort on a field that I construct during a query....how can I solve this?Basically what i am doing is selecting mediafiles with comments in descending order. (so mediafile with most comments on top)Here's my (not working) query, because it throws the error that the mediaComments column is unknown....SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,mediaComments=(select count(*) from MediaComments where mediaid=m.mediaid)FROM Media m WHERE m.usercode>0group by m.title,m.usercode) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
View Replies !
Query To Sum The Same Field Twice In The Select Statement
Hello friends , I have table (MoneyTrans) with following structure [Id] [bigint] NOT NULL, [TransDate] [smalldatetime] NOT NULL, [TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw' [Amount] [money] NOT NULL I need to write a query to generate following output Trans Date, total deposits, total withdrawls, closing balance i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate ) I am working on this for past two days with out getting a right solution. Any help is appreciated Sara
View Replies !
DISTINCT SELECT Query With More Than One Field
Hello, I want to do a DISTINCT SELECT query with more than one field, for example a ID field with a Type field, as if both fields make the primary key, like (ID 1 ,Type 1) ,( ID 1, Type 2) and (ID 2, Type 1) is ok but not (ID 1, Type1) and (ID 1,Type 1) if its not possible to do a distinct with more than one then what other techniques are possible to get the duplicate data out. the reason why I want to use distinct is that I can use that query to export that data to where both of these fields make the primary key. Thanks in advance
View Replies !
SQL Query - Search Field For String
Hi, I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers. Thanks
View Replies !
Help! - Query On Time Field In SQL Server - How?
Hi,I have an orders database and I need to be able to write a querythat groups sales by:1. Date2. Time of day (both am and pm)I currently have a date/time field named "Submitted" that storesjust the date.However, how do I store the time?I know I can store the date/time in the "Submitted" field Icreated. However, because of the time being stored in that field, Ican't execute an easy query such as:select * from database where submitted = '2/10/05'(reason being, the time portion makes each "Submitted" fieldrecord unique, so just the date won't be recognized)I realize there probably is a much better way to do this in theSQL query language that I am not aware of.Two questions:1. Based on what I'm trying to do -- query based on date and groupby number of sales for each hour of the day (am and pm) -- what is thebest approach to store the time?2. What does the query look like that querys by date and groupsorders by time of day (am and pm)?Thanks for your help.J
View Replies !
How To Query By Keyword For Image Field.
If the data type of field is "varchar",we can use "like" to query if it hassome substring.Such as "where custom.valuevariant like '%Verizon%' ", it will query out allrecords that contains string "Verizon".But how to do when data type of field custom.valuevariant is "image"?Thanks
View Replies !
Referencing A Calculated Field In A Query
I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005 SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2 This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it. The only way how to avoid the issue in the SQLExpress seems probably to be - Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss) - Utilize computed columns if possible (no idea on performance impact here) Am I right or is there any other way how to reference a calculated field? Any suggestion is greatly welcomed! Thanks in advance.
View Replies !
Help - Updating A Field In Query Analyzer
Hi All, I'm trying to create a script that updates a field in a table, based on data in another table. It should be simple, but I'm doing something wrong. Here's the code: USE DBMyDatabase UPDATE TblToBeUpdated SET IDField=TblOther.IDNew WHERE IDField=TblOther.IDOld SELECT Pk, IDField FROM TblToBeUpdated What am I doing wrong? The error code I get is: Server: Msg 107, Level 16, State 3, Line 1 The column prefix 'TblOther' does not match with a table name or alias name used in the query. Server: Msg 107, Level 16, State 1, Line 1 The column prefix 'TblOther' does not match with a table name or alias name used in the query. Thanks. Henry
View Replies !
Query/select The First 10 Characters In A Field
I'm comparing two tables and need to compare the first 8 characters of one field in table A to the first 8 characters in another field in table B. So instead of where 'John Smith' = 'John Smith' it would compare where 'John Smi' = 'John Smi' I know I've done this before but can't find a good reference. It's something like this when calling a query within a WHILE loop: SELECT * FROM blah WHERE FID = '".$row['FID']."' AND LEFT('TRACK', 8) = LEFT('".$row['TRACK']."',8) Am I close? Any help would be appreciated.
View Replies !
Query To See If An Int Field Starts With A Certain Number
How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!
View Replies !
|
TRACKER
