Select Statement Eliminate Field Name
Hi
I have tabelA, Which has 10 columns, I need to select 10 column values only no field names. Is there any way I can select only table values not field names. I don't want to see field name in my query result set. Please let me know. I appreciate your help.
Thanks
Regards
-Leong
View Complete Forum Thread with Replies
Related Forum Messages:
How To Eliminate Column Header From Select Statement
Hi Everybody: I want select data from SQL server and save to a file without column header. Anyone has any suggestions? I try PLAIN option in select statement which is described in msdn library as: "PLAIN: Prevents column headings from appearing in the query output that is displayed. You can use PLAIN whether or not a TO clause is present. If an INTO clause is included, PLAIN is ignored. " However, I tried in both SQL 2000 and SQL 7, none of them supports it. Any ideas? Thanks in advance. Joan
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
How To Eliminate The 'NULL' Field Values
I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?
View Replies !
The Select Statement Is In A Field
Ok, I inherited this database and there is a field that stopres a selectstatement. Is there anyway possible to execute the value of the fieldwithin a select statement?For example:the table:Name "george"lookupForName "Select orders from Ordertable"So maybe something like select name, execute(lookupforname) as ordersSorry, I didn't design this, just inherited :)george
View Replies !
Select Statement With A New Identity Field
Hello, Is it possible to generate a identityfield dynamically upon select, like this: SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username ASC I want the output to be: 1 - Name12 - Name23 - Name3 The reason for this, is that i want to change the sort order in many diffrent ways, but i need to get the IDs from 1-?? even when the sort order changes. Like: SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username DESC I want the output to be: 1 - Name32 - Name23 - Name1 Patrick
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 !
How Do I Call A Select Statement Properly When The Field Is A Yes/no?
Hello, i am pretty new to asp. I am trying to do a select statement for sending an email to everyone who is not an admin. the code is below, i know it must be fairly simple, yet i do not know how to do it. With the code below, I select everyone. I want to know how to do it properly, similar to the second which does not work. Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers", conn) DOES NOT WORK: Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers WHERE Admin = 'N'", conn) Thanks in advance.
View Replies !
SELECT Statement - How To Not Get Column Field Names?
I do a SELECT * from table command in an ASP page to build a text fileout on our server, but the export is not to allow a field name rows ofrecords. The first thing I get is a row with all the field names. Whydo these come in if they are not part of the table records? How do Ieliminate this from being produced? Here's the ASP code....<html><head><title>Package Tracking Results - Client Feed</title></head><body><%' define variablesdim oConn ' ADO Connectiondim oRSc ' ADO Recordset - Courier tabledim cSQLstr ' SQL string - Courier tabledim oRSn ' ADO Recordset - NAN tabledim nSQLstr ' SQL string - NAN tabledim objFSO ' FSO Connectiondim objTextFile ' Text File' set and define FSO connection and text file object locationSet objFSO = CreateObject("Scripting.FileSystemObject")'Set objTextFile =objFSO.CreateTextFile(Server.MapPath("textfile.txt"))'Response.Write (Server.MapPath("textfile.txt") & "<br />")Set objTextFile = objFSO.OpenTextFile("C: extfile.txt",2)' write text to text file'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"' SQL strings for Courier and NAN tablescSQLstr = "SELECT * FROM Courier"' set and open ADO connection & oRSc recordsetsset oConn=Server.CreateObject("ADODB.connection")oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &"c:/Database/QaTracking/QaTracking.mdb" & ";"set oRSc=Server.CreateObject("ADODB.Recordset")oRSc.Open cSQLstr, oConnResponse.ContentType = "text/plain"Dim i, j, tmpIf Not oRSc.EOF ThenFor i = 1 To oRSc.Fields.CountobjTextFile.Write oRSc.Fields(i-1).NameIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineWhile Not oRSc.EOFFor i = 1 To oRSc.Fields.CountIf oRSc.Fields(i-1) <"" Thentmp = oRSc.Fields(i-1)' If TypeName(tmp) = "String" Then' objTextFile.Write "" &_'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""' ElseobjTextFile.Write oRSc.Fields(i-1)' End IfEnd IfIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineoRSc.MoveNextWendEnd IfobjTextFile.CloseSet objTextFile = NothingSet objFSO = NothingoRSc.CloseSet oRSc = NothingoConn.CloseSet oConn = Nothing%></body></html>
View Replies !
Select Statement With Run Time Field Selection
I have this SELECT statement. SELECT [issueID], [name] FROM [MyIssue] What I wanted to do is in addition to the above statement, I want to add two run time fields like this: 99 [issueID],'All Issues' [name] So let's say the above select statements generates this list: Summer 2007 Issue Winter 2007 Issue The two addition fields will make the result list like this: 01 Summer 2007 Issue 02 Winter 2007 Issue 99 All Issues How do I accomplish this? Any help is much appreciated.
View Replies !
Convert A Time Field In The Select Statement Of The Query
Hi, I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query. Can anyone help me please? Thx
View Replies !
How To Eliminate NULL Display Space During SELECT
I have a table with 3 fields. when I type select * from test -- I am getting the results as below. NAME AGE DEPT AAA 23 AOD BBB 27 NULL CCC NULL NULL DDD 23 POD DEPT,AGE are displayed with "NULL" WHEN THERE IS NO value for that field . How can I eliminate this. I need space instead of NULL. When I export to text file there also contains NULL. Let me know how can I eliminate this. Thanks in advance
View Replies !
Need To Set A Field In A Select Statement Equal To Yes Or No If Record Exists In Separate Table
Hey gang, I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query') I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling. Here's my current Query this works fine: Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#') What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note. I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No) Any advice would be greatly appreciated.
View Replies !
In Code Behind, What Is Proper Select Statement Syntax To Retrieve The @BName Field From A Table?
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName) I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER " error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'. Source Error: Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View Replies !
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Help With Delete Statement/converting This Select Statement.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement: SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View Replies !
Select Statement Problem - Group By Maybe Nested Select?
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
View Replies !
WHERE Field=(select Field From Tables)??????
I need some help.I am trying to write a query which does the followingSELECT * from table1 where field1=(SELECT distinct field1 FROM table1WHERE field2='2005' or field2='2010')I need all the values from table1 which match any value from field 1from the subquery.Any help is appreciated.thanks
View Replies !
SQL Select Statement To Select The Last Ten Records Posted
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
View Replies !
Using Select Statement Result In If Statement Please Help
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
View Replies !
If STATEMENT Within Select Statement Syntax
Hi, I am a newbie to this site and hope someone can help.... I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is: if(TL_flag= '1', "yes") as [Trial Leave] it is coming up with an error.... I can use Select case but I should not need to as this should work? Any ideas?
View Replies !
Field Names From SQL Statement
Is there anyway to determine what the resulting Field Names are going to be from a SQL Statement? For example: SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD3, TABLE2.FIELD1 AS ANOTHERNAME FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.PK = TABLE2.FK resulting field names: FIELD1 FIELD2 FIELD3 ANOTHERNAME Seems easy enough splitting all values before "FROM" by comma and doing some manipulation to remove table names and anything before the word "AS". However, it gets more difficult when you have complex CASE statements embedded in you query that may also contain commas. Just a shot in the dark because I don't know if anyone has already done something like this before. Thank you in advance, Jeff
View Replies !
Include ID Field In GROUP BY Statement
I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one. The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits': ID FruitName Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 Pear 0.83 And run the query: select max(Cost),FruitName From Fruitsgroup by FruitName It'll correctly return: FruitName CostApple 0.63Pear 0.89 Now i need the ID also returned by my query so i go: select max(Cost),FruitName,ID From Fruitsgroup by FruitName,ID This doesnt return the above results with the ID appended to it, it instead returns: ID FruitName Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 Pear 0.83 As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of: ID FruitName Cost2 Apple 0.634 Pear 0.89 Thanks.
View Replies !
Checkbox - Using In Update Statement As Bit Field
hi I have a bit field in sql server represented by a checkbox ... I am updating the database in code ( ie not using formview generated update .. ) the line that is falling over is .Parameters.Add("@archive", SqlDbType.Bit).Value = txtarchive.Checkedit falls over saying failed to convert string parameter to boolean the value of txtarchive.checked is either true or false - how do i convert this to 1 or 0 or something that sql is happy with ... thanks
View Replies !
SQL Insert Statement That Returns The ID Field
I'm fairly new to SQL, so this might be simple question: I am adding records to an SQL7 database by using the INSERT statement. The table has an IDENTITY field which is auto-incremented, so a value is not needed for the field in the query. Is there any parameters for INSERT that returns to me the value of the IDENTITY field for the record I just created?... Any help or suggestions would be appreciated.
View Replies !
EXECUTING A SQL STATEMENT AGAINST DATA FIELD
HELLO I'M NEW OF SQL SERVER. I'VE BEEN CHARGED BY MY COMPANY TO MOVE THE MDB WHICH WE WORKED TILL NOW TO SQL. OBVIOUSLY I HAVE NOW TO CREATE THE DATABASE'S UPGRADING PROCEDURES WE WERE USING ON ACCESS. MY PURPOSE IS TO MAKE RUN THE FOLLOWIG PROCEDURE, ONLY IF THE VALUE OF THE FIELD 'UPTGRD11' IS EQUAL AT '1', BELOW THE SQL STATEMENT I'M USING. THIS OPTION IN ACCESS IS VERY EASY, BY MACRO OR BY VBA, I'M NOT ABEL TO DO IT IN T-SQL IT DOESN'T RUN LIKE THAT IF 'dbo.uptgrdt.uptgrd11' = '1' BUT IT RUNS USING IS NOT NULL IF 'dbo.uptgrdt.uptgrd11' IS NOT NULL is there an easy way to do what i'm trying to?
View Replies !
Select Max Value Of One Field For Value Of Other Field(s)
I would like to query a table for a max value of one field for a distinct combination of two other fields. Let's call these fields RowID, ObjectID, and ObjectType. RowID is an auto-increment field, so for each distinct combination of ObjectID and ObjectType, there will be many values of RowID. To visualize an example: RowID, ObjectID, ObjectType 1 , 1 , 1 2 , 1 , 2 3 , 1 , 3 4 , 1 , 1 5 , 1 , 2 6 , 1 , 3 Of these rows, I would only want 4, 5, and 6 (max values for distinct combination of ObjectID and ObjectType). I hope I explained this clearly. I would imagine I'd need to use some form of nested query, but nothing I have tried so far has worked. I am using SQL 2005. Thanks!
View Replies !
SQL Update Statement Set Field Value To Column Default
Is there a way to set a field value to the column default in an update statement? Eg. UPDATE Table2 SET field1 = DefaultValue where DefaultValue is the field1 column default in the table definition. The reason I need to do this is when I delete a record from Table1, I need to set the foreign key in Table2 to the default (I don't want to delete the record in Table2, just want to set the key to a default key). I could hard-code the default value in the stored procedure but I think that's just not clean. If I create a new instance of the DB and the default value changes, I'd need to change the stored procedure(s). Just not clean... To avoid a drawn-out discussion, there are reasons why I can't setup a relationship between the two tables and use "ON DELETE SET DEFAULT". Any info greatly appreciated.
View Replies !
Error In Image Field When Using CASE Statement
I've this Stored procedure on a SQLserver 2000 SP3: SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage FROM Squadre WHERE squadra = @squadra this is a flag that returns if the image field is present or not.. i've a lot of this type of stored procedures.. but this one returns me an error.. --------------------------- Microsoft SQL-DMO (ODBC SQLState: 42000) --------------------------- Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. --------------------------- OK --------------------------- An i can't save.. why? reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??
View Replies !
Running A LIKE Statement When Searching For A Date Field...
I am trying to run a like statement that has a datetime column and for some reason it does not return any values. I looked in the SQL help files and in states in there that when trying to select using a datetime that the preferred way of doing it is using a like statment. Does anybody know a better way of doing this? Here is my example: (I have dates in this column ie 2006-02-13 11:30:54.220) SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'
View Replies !
SQL Eliminate Duplicates
I am working with SQL 8.00. I have the following tablesTABLE ClientInfoCheckNum Account Name Addr1 City State Zip---------------------------------------------------------------------12345 11111 John 123 Mary St Miami FL3313954321 22222 Mary 321 River Side Clifton NJ0705598765 33333 Tom 12 Main St Miami FL33139and TABLE ClientAcctCheckNumAccount Cost Credit Notes---------------------------------------------------------------------12345 11111 1Yes Great12345 11111 11Yes Well12345 11111 111No Bad54321 22222 2Yes Fine54321 22222 22No OK98765 33333 3Yes I like itThis the end result that I want.CheckNum Account Name Addr1 City State ZipSUM ofMax(Notes)of Cost orMin(Notes)-----------------------------------------------------------------------------12345 11111 John 123 Mary St Miami FL33139 123Great54321 22222 Mary 321 River St Clifton NJ07055 24 Fine98765 33333 Tom 12 Main St Miami FL33139 3I like itI need to avoid duplicate rows(Note only if the Account field areequal).I need to get the fields shown above including the SUM ofClientAcct.Cost and the MAX or Min of ClientAcct.Notes.I have searched the web andhave tried DISTINCT, UNION, GROUP, COUNT(*) = 1 AND COUNT(*) <> 1 butI can't get the correct results.This statement give me too many rows(duplicate)--------------------------------------SELECT [Name], ClientInfo.account, addr1, City, State, ZIP, COST,Notes from ClientInfo JOIN ClientAcct onClientInfo.CheckNum=ClientAcct.CheckNumDoes any body have a solution to this?Thanks in advanceJulio
View Replies !
Eliminate Division By Zero
This query is part of a larger query that updates a table that holds statistics for reporting. It yields actual Unit per Minute by plant by month. Some of the plants don't produce anything in certain months, so I'm ending up with a Divide by Zero error. I think I just need to stick another CASE statement in for each month, but that seems like it could get pretty ugly. Any suggestions on how to improve this? SELECT FL.REPORT_PLANT, [JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END), [FEB]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.HOURS*60 ELSE 0 END), [MAR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.HOURS*60 ELSE 0 END), [APR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.HOURS*60 ELSE 0 END), [MAY]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.HOURS*60 ELSE 0 END), [JUN]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.HOURS*60 ELSE 0 END), [JUL]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.HOURS*60 ELSE 0 END), [AUG]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.HOURS*60 ELSE 0 END), [SEP]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.HOURS*60 ELSE 0 END), [OCT]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.HOURS*60 ELSE 0 END), [NOV]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.HOURS*60 ELSE 0 END), [DEC]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.HOURS*60 ELSE 0 END) FROM PRODUCTION_CMPLT PC INNER JOIN FACILITY_LINES FL ON PC.MANUF_SITE = FL.MANUF_SITE AND PC.PROD_LINE = FL.PROD_LINE INNER JOIN PROD_MASTER PM ON PC.PRODUCT=PM.PRODUCT WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE()) AND PM.UOM<>'LB' GROUP BY FL.REPORT_PLANT
View Replies !
Is There A Way To Eliminate Logging?
We use truncate table because it's lack of logging activity increases our performance. Our company is pinching pennies and we have been forced to a new server where the logs and data reside on one harddrive. Another performance killer. I'm wondering if there is a way to eliminate logging activity for when we run a DTS package or query to update our summary tables? There is no value in having this activity logged from my perspective.
View Replies !
Eliminate Duplicate
Table A JobNoClaimShipType A1100I A1200II Table B JobNoCost A150 A1100 Result Expected JobNOCost Claim Shiptype A150 100I A1100 200II Hi all, i've given a table structure with data and the expected result . I want to establish it in SQL server (7.0) If i establish the inner join i get 4 rows (2*2) Please let me know how to get the result Thanx in adv Tarriq
View Replies !
Eliminate Transaction Log
The Transaction Log which I'm working is so loaded. I trucated it, but isn't enough, is still so big. What can i do for clean the log and have free space again?
View Replies !
How To Eliminate The Job Schedule?
I tested the job schedule several times on real DB and then I came up with serious problemes. Every 3 minute several scheduling jobs are executing. I am in a very desparate situation. Could you help me stop or delete the job schedule. I couldn't find where to stop it. Thanks!
View Replies !
How To Eliminate Double Row
'My table' is below with double row lot value date 2 300 3/2/06 3 200 6/5/05 4 100 5/21/07 5 340 6/23/06 2 250 4/3/06 My query such as SELECT lot, value, date FROM my table How can I eliminate 1 row of lot 2 and chose the recent date only? Thanks for your help Daniel
View Replies !
Eliminate Time
Hi, In my query where clause I am using between to get data. Because of time in the data I need to eliminate that and compare, how can I eliminate. my where clause is as below. due to that my query performance is falling down. please help. CONVERT(DATETIME, CONVERT(CHAR(20), OpportunityDate,110)) BETWEEN CONVERT(CHAR(20),@FDate,110) AND CONVERT(CHAR(20),@TDate,110) Thanks Sreenu
View Replies !
Help Eliminate Dupes
I am VERY new to SQL and I am having a heck of a time biulding a script to find and remove duplicate entries. Here is the table structure. CREATE TABLE [dbo].[SecurityEvents]( [EventLog] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RecordNumber] [int] NULL, [TimeGenerated] [datetime] NULL, [TimeWritten] [datetime] NULL, [EventID] [int] NULL, [EventType] [int] NULL, [EventTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [EventCategory] [int] NULL, [EventCategoryName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SourceName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Strings] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ComputerName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Message] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Data] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO This small script seems to eliminate the dupes, but I can't seem to figure out to properly replce the table the with output of the script with all the dupes gone. select distinct * from dbo.SecurityEventsTest where recordnumber IN (select recordnumber from dbo.SecurityEvents) order by recordnumber Could someone help?? Thank You, John Fuhrman http://www.titangs.com
View Replies !
Eliminate Repeating ID
Hi., I dont know to eliminate the repeting record in the ID column how to do that., for ex., i have given an example., create table Example (ID int, Name varchar(30)) INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Chirag') INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Shailesh') INSERT INTO [Example] ([ID],[Name]) VALUES (2,'Dipak') INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Mihir') INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Piyush') select * from Example., i will get., ID Name ----------- ------------------------------ 1 Chirag 1 Shailesh 2 Dipak 4 Mihir 4 Piyush ..... but i need like ID Name ----------- ------------------------------ 1 Chirag Shailesh 2 Dipak 4 Mihir Piyush I dont want repeated ID., How can i do that.,
View Replies !
|
TRACKER
