Help with this simple? query


Ok, I am hoping someone could help me out here. I have a query which is not working properly and is driving me crazy.
I have two tables
data_table = my main working table
daily_removed = same structure as main table, but less records

what I would like to do is
update the column (Status) in the data_table, for all records where the PiNumber is listed in the daily_removed table

This is where it gets weird for me... when I do

Code MySQL: select count(data_table.PiNumber) from data_table inner join daily_removed ON data_table.PiNumber=daily_removed.PiNumber
I get a total of 1528 (which is the correct number) of matching records in the two tables
But when I go to update the Status column using

Code MySQL: UPDATE data_table INNER JOIN daily_removed ON data_table.PiNumber = daily_removed.PiNumber SET data_table.Status='removed';
only 1214 Records get updated. '

Also, as a side note I added exactly 1214 records to the daily_removed table today ...

What could possibly be happening\t
Let me know if I need to post additional info, but any advice would be greatly appretiated.
Posted On: Monday 31st of December 2012 01:06:36 AM Total Views:  455
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Performance Issue( URGENT plz)

my query is: i selected 6 columns from database table. then i wrote CASE for 2 virtual columns as q1py_bu and q1py_tot. like this i need to generate more than 70 columns this select statement. so it takes huge time for the query which i mentioned below. select Rhead,BU,Sub_bu,report_order,sub_bu_order,q1py, case when sub_bu=''ISS'' then (select q1py from sam a where a.sub_bu=''ESS'' and a.rhead=b.rhead) when sub_bu=''TS'' then (select q1py from sam a where a.sub_bu=''Services'' and a.rhead=b.rhead) when sub_bu=''SW'' then (select q1py from sam a where a.sub_bu=''SW'' and a.rhead=b.rhead) else NULL end as q1py_bu, case when sub_bu=''ISS'' then (select q1py from sam a where a.sub_bu=''TSG'' and a.rhead=b.rhead) when sub_bu like ''R&D'' and rhead in (''Region owned opex'',''% of revenue'',''Region opex'',''Region opex%'') then (select q1py from sam a where a.sub_bu like ''TSG'' and a.rhead=b.rhead) else NULL end as q1py_tot, last_update from sam b where sub_bubu and eport_order
VIEWS ON THIS POST

125

Posted on:

Tuesday 20th November 2012
View Replies!

Sql Like + Freetext + Contains

Hi Guys I am in need of some assistance please. I am using SQL Server 2005 and C#(winforms). For example if I have a winform with a textbox and search button. If I type in ABCD1234. I won't know what the data is coming into the X Table. So there will be hundreds of description data in the description field in X table. So in the X Table, there are ABCD1234,ABCD_1234,ABCD/1234,ABCD 1234 But I will only pick this 'ABCD1234' record up, and the rest won't be picked. They are variations of the same description. It needs to pick also ABCD_1234,ABCD/1234,ABCD 1234 If also type in 5678, in another column like code in X Table. It would have 005678,05678,56780. But I will only pick this '5678' record up, and the rest won't be picked. They are variations of the same code. It needs to pick also 005678,05678,56780 I have tried LIKE, FREETEXT and CONTAINS. But another idea is a table of alternate descriptions for those that you know about and look those up and search for them all. But how can that be done Any ideas please with examples if possible.
VIEWS ON THIS POST

120

Posted on:

Tuesday 20th November 2012
View Replies!

Help retrieving data correctly and displaying correctly

I need to pull information from three tables which would generally not be a problem. I need to pull these fields from the Dealer_Profile table Dealer_Number Dealer_Name Address1 City State Zip Phone Fax Mfg_Id Manufacturer2 table -- joins dealer profile table on the mfg_id ManufactureID ManufactureName The data is kinda 'whack' to say it nicely as I don't believe the tables are fully relational. My issue is that the database structure was designed wrong in the first place over 10 years ago. I would like one row per Dealer if possible, even if they have more than one manufacturer (which most of them do) Possible Deed Layout which is my question at hand: Dealer Info....,Manf1, Manf2, Manf3... Make sense Any thoughts
VIEWS ON THIS POST

152

Posted on:

Tuesday 20th November 2012
View Replies!

Date + sql max???

Ive tried the following sqlstatement to get the greatest date from a column in my table- but doesnt seem to be working- what am i doing wrong Does the sqlmax function support dates\t Code: sub sqlCmd2( ) '==SQLSTATMENT================================ SET objRs2 = SERVER.createObject( "ADODB.recordSet" ) dim sqlStatment sqlStatment = sqlStatment + "SELECT MAX(Gross-val) FROM sldetail objRs2.OPEN sqlStatment,objConn,1,3 '=============================================' end sub Im calling the sql statement as Code: Call sqlCmd2( ) Response.write(objrs2("Gross-val")) any suggestions\t
VIEWS ON THIS POST

102

Posted on:

Tuesday 20th November 2012
View Replies!

Totalling higher of two columns by row.

Assume that I have a table containing two fields, Total and Total2. I need the sum of whichever is higher for each row, for example, with the following sample data: Code: Total | Total2 1 | 3 2 | 1 5 | 10 The query should return 15(3+2+10). Any good ideas on how to do this simply
VIEWS ON THIS POST

121

Posted on:

Tuesday 20th November 2012
View Replies!

Code inserting some records twice

, thank you for taking time to read this post...I hope someone can help. I am working on an online survey, the problem is that my SQL query is inserting some entries twice. This survey is for members only so therefore require you to login ( so I can capture their membership number) The problem is the Membership Number is being inserted into the database(one record - membership number, no survey answers) as soon as the survey page is opened. When all of the survey questions are answered the Membership number and the responses are entered as a new record (one record - Membership number and survey answers) I am trying to use an if statement but the code is not to be working. Here is my code:
VIEWS ON THIS POST

113

Posted on:

Tuesday 20th November 2012
View Replies!

Need help with store procedure using Oracle AND VS?

I am using Oracle database with Visual studio.NET 2003, how can i create a store procedure, because i right click on the store procedure node however there isn't a option to create a new procedure, so do i have to create the procedure in oracle then\t
VIEWS ON THIS POST

112

Posted on:

Tuesday 20th November 2012
View Replies!

I must be missing something

Good day all. I'll post the summary first, then go into further detail. The query pulls information from multiple tables. To simplify for this example the tables are Items, Orders, and ItemDetails. SELECT Items.Item, ItemDetails.Title, Orders.Qty FROM Orders INNER JOIN Items on Items.Item = Orders.Item INNER JOIN ItemDetails on Item.Item = ItemDetails.Item Item | Title | Qty ----------------------- 1 | ABC | 5 1 | ABC | 6 2 | DEF | 7 3 | GHI | 8 I want it to display: Item | Title | Qty ----------------------- 1 | ABC | 11 2 | DEF | 7 3 | GHI | 8 When I try using a sum function and grouping by the Item: SELECT Items.Item, ItemDetails.Title, Sum(Orders.Qty) as Qty FROM Orders INNER JOIN Items on Items.Item = Orders.Item INNER JOIN ItemDetails on Item.Item = ItemDetails.Item Group By Items.Item I get this error: Column 'ItemDetails.Title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Any insight as to what I'm doing wrong and how to fix it would be appreciated.
VIEWS ON THIS POST

120

Posted on:

Tuesday 20th November 2012
View Replies!

Sql subselect..

hi i have a string in my code which is select fields from two different tables which are in my sqlserver database tables... my string is below as follows Code: dim sqlstr as string=SELECT survey96.STU_CODE,survey96.TITLE, survey96.FIRSTNAME,survey96.SURNAME,survey96.ADDRESS1,survey96.ADDRESS2,survey96.ADDRESS3,survey96.ADDRESS4, survey96.POSTCODE, survey96.STU_CAEM,survey96.FACULTY,survey96.DIVISION, survey96.COURSENAME,survey96.COURSECODE,survey96.FEESTATUS, survey96.YEAROFSTUDY,survey96.MODE,survey96.AWARD,survey96.GENDER, survey96.ETHNICITY,survey96.DOB,survey96.JACS2,survey96.JACS3, survey96.vYEAR,survey96.vETHNICITY,survey96.vFEESTATUS,survey96.vLEVEL, survey96.vAGE,survey96.VSTU_DOB,survey96.vGENDER,survey96.AGEGENDER,survey96.MODEvLEVELvYEAR,survey96.JACS1, tblQuestions.Question, tblStudentSurveyDetails.surveycode, tblStudentSurveyDetails.stu_code AS stu_code, tblStudentSurveyDetails.questionid, tblStudentSurveyDetails.ansnumeric, tblStudentSurveyDetails.anstext FROM tblStudentSurveyDetails INNER JOIN tblQuestions ON tblStudentSurveyDetails.questionid = tblQuestions.QuestionId INNER JOIN survey96 ON tblStudentSurveyDetails.stu_code = survey96.STU_CODE now i have another string which in its syntax is using the select from .. the select from it is using is from the sql syntax which i have above.. so basically when i end up with Code: Dim strSelect As String = "select Question FROM " & sqlstr & " GROUP BY Question" when i run this is sql query analyzer i keep on getting this error message Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'SELECT'. can someone plz tell me how is can solve this.. all i want to do is select a field from a query by passing the parameter name of the query..
VIEWS ON THIS POST

112

Posted on:

Tuesday 20th November 2012
View Replies!

Show All Tables?

Here's what im trying to do; im trying to show all the tables i have in a database (SQL) and its on a remote server; The purpose is to make a database control panel so i can edit/delete/add/etc my database from the browser; i know how to show all the files in a specific table but i need help getting all the tables to show; Any ideas, suggestions; I know its probably really simple , i just cant find a script;
VIEWS ON THIS POST

81

Posted on:

Tuesday 20th November 2012
View Replies!

Dynamic SQL Question

I have to get a count of records using dynamic sql. I have the following and I'm getting errors (Syntax error converting the varchar value 'SELECT ' to a column of data type int.): Code: DECLARE @sCalldate varchar(10) DECLARE @SQL varchar(8000) DECLARE @RC int SELECT @sCalldate ='20050712' SELECT @sEmployeeNameId = '0' SELECT @sAgentid ='0' SELECT @SQL = 'SELECT ' + @RC + ' = Count(*) FROM dbo.tmPunchTime WHERE scalldate = ' + @sCallDate + ' AND ISNULL(sRawLogout, ''x'') = ''x''' EXEC (@SQL) If I put the @RC inside the quotes I get error: Must declare the variable '@RC'. I tested by printing the statement and it prints properly just errors when executing. How can I make this work
VIEWS ON THIS POST

107

Posted on:

Tuesday 20th November 2012
View Replies!

Calculating employees weekly hours

The pay period begins on a sunday and ends on a saturday. Some employees work on the weekends, some don't. How can I calculate by week how many hours an employee has worked This is what the employee table looks like: Date EmpNumber Hours 20050522(sun) 123 7.5 20050523(mon) 123 8 20050524(tues) 123 8.5 20050523(mon) 246 9 20050524(tues) 246 6.5 20050526(thur) 246 7.5 20050529(sun) 345 8 20050530(mon) 345 7 20050531(tues) 345 6.75 I need the outcome to look like this: PayPeriodWeek EmpNumber Hours 20050522 123 24 20050522 246 23 20050529 345 21.75
VIEWS ON THIS POST

102

Posted on:

Tuesday 20th November 2012
View Replies!

Login script problems

Hi all, I'm trying to create a login page using the below code that is activated from a form submit: Code: I'm new to this and can't seem to see whats wrong. Instead of giving an error or redirecting it just comes up with a HTTP 500 error.
VIEWS ON THIS POST

108

Posted on:

Tuesday 20th November 2012
View Replies!

selecting non duplicate fields from a table

may i know hows the statement like for selecting a field with different values, but there are duplicate values in the column..
VIEWS ON THIS POST

115

Posted on:

Tuesday 20th November 2012
View Replies!

DISTINCT Statement

sorry about duel post, i didn't see this thread... hello , this is my first post on these forums so i'm pretty new! I work at a jewelers retailing company. We are going throuh a conversion and I'm recreating screens from an A/S 400 server to asp to view historical data. Im working with access databases. Items have a group number and i want my SQL statement to Distinctly caputre results by this group number (to display a group of items in an invoice) Here is my code for the SQL: 'Create database connection SET conn = Server.CreateObject("ADODB.Connection") conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\websites\inventory\inv.mdb" SET rs = Server.Createobject("ADODB.recordset") If len(invoice) = 0 Then SQL_query = "SELECT Distinct Gmmmno, Gmcode, Gmmmdt, Gitem FROM Inv01p where Gmcode = '" & code & "' and Gmmmdt NULL and Gmmmdt >= "& fromdate &" and Gmmmdt = "& fromdate &" and Gmmmdt
VIEWS ON THIS POST

63

Posted on:

Tuesday 20th November 2012
View Replies!

insert command

Originally posted by : Nitesh (friend_nitesh@rediffmail.com)How to insert values in more than one table just using a single command withod using procedure and tigger
VIEWS ON THIS POST

112

Posted on:

Tuesday 20th November 2012
View Replies!

How to connect to oracle using ADO in ASP.net

Originally posted by : krishna kumar (krishna@dcons.com)pls can i connect to oracle using the same code as in ASP,or else let me know the code,thanx,Krishna
VIEWS ON THIS POST

103

Posted on:

Tuesday 20th November 2012
View Replies!

How to compare strings in c#?

Originally posted by : kamp (kamph@netease.com)i want compare user's password and the password from a database(m$sql), but it doesn't work!when i print both two password on the web-browser, they are the same! The following is my code: SQLConnection myConnection = new SQLConnection("server=Qacomputer;uid=sa;pwd=;datab ase=sn"); SQLCommand myCommand = new SQLCommand("select psswrd from login where name='"+idname.SelectedItem.Value+"'", myConnection); myConnection.Open(); myCommand.Execute(out MyReader); MyReader.Read(); if(MyReader["psswrd"].ToString().Equals(password.Text.ToString())) hell.Text = "password is corrected"; else hell.Text = "password is not corrected " +MyReader["psswrd"].ToString() + " " + password.Text.ToString(); MyReader.Close(); myConnection.Close();
VIEWS ON THIS POST

97

Posted on:

Tuesday 20th November 2012
View Replies!

How to concat from single column/multiple records

I created a sql statement that based on several things, sums a field. W.orks great. Now they want the names from each of the selected records to be brought in too. So how do I concat the names associated with the records without creating a whole new statement As you can see, it is long, complicated due to fool-proofing. Since it picks out selected records, I need to make sure the names pulled in is for those records only. so, how do I get : Harry Joe Sam for the result of rec(1) when there are 3 records totaling 46 for rec(0) If Size "Slurry" And typez "Transition" Then If IsNull(Size) = False Then sqlstatement = "SELECT sum([ShiftTonnes]) FROM dbo.cur_Train1_Bagging " & _ "WHERE [Date] BETWEEN '" & startdate & "' " & _ " AND '" & enddate & "' AND [grade]= '" & Gradez & "' and [BagSize_lbs]= '" & Size & "'" & _ "and [lotlocation]not like 'slurry'" record.Open sqlstatement, , adOpenStatic Form_Tr1_WR.Act_10_Tonnes_1 = Round(record(0), 1) record.Close End If End If If Size "Slurry" And typez = "Transition" Then If IsNull(Size) = False Then sqlstatement = "SELECT sum([ShiftTonnes]) FROM dbo.cur_Train1_Bagging " & _ "WHERE [Date] BETWEEN '" & startdate & "' " & _ " AND '" & enddate & "' AND [grade]= '" & gradez2 & "' and [BagSize_lbs]= '" & Size & "'" & _ "and [lotlocation]not like 'slurry'" record.Open sqlstatement, , adOpenStatic Form_Tr1_WR.Act_10_Tonnes_1 = Round(record(0), 1) record.Close End If End If
VIEWS ON THIS POST

114

Posted on:

Tuesday 20th November 2012
View Replies!

Getting all Mondays in 2009

hi, i would like to make a query where i can extract all mondays in 2009 and put them in a table (or view) how could i code this
VIEWS ON THIS POST

62

Posted on:

Tuesday 20th November 2012
View Replies!

Index on XML datatype column--Query performance tuning

Background: There is a table with a column whose datatype is xml. There is an index created on that column. There is a trigger that contains an update statement that tries to delete 4 nodes from the single cell of the column which contains xml type data for all the records in the table . For each record the cell that stores xml type data contains huge xml data. Imagine an xml file that stores data such as File name, FileDesc, File owner, File Size etc for about 1000 files. This update statement takes excess amount of time resulting in timeout error. As a result of this the Application misbehaves resulting in multiple generation of automated mails. Workaround: Instead of deleting nodes change the text of node to some dummy text. But this change also is not resulting in executing the query faster. Observation: The Update query executes in seconds when the index on xml column is removed/disabled. Removal of index might impact other places which is not deable. Looking for a solution where in the index is retained and the nodes are either deleted or their text is changed resulting in faster performance of update query. Example of Update Query: UPDATE Scan SET xmlreport.modify('delete (/MSR_XML/Status/MSR/File/FileName)') FROM Scan where scanID='76077D9F-D65D-4B28-8FAB-8CFE6EA1A999' The followingin a WHILE LOOP is the workaround: UPDATE Scan SET xmlReport.modify('replace value of (/MSR_XML/Status/MSR/File/FileName[1]/text())[1] with "******" ') FROM Scan where scanID='76077D9F-D65D-4B28-8FAB-8CFE6EA1A999' The example of XML Structure: test.doc me 10kb
VIEWS ON THIS POST

110

Posted on:

Tuesday 20th November 2012
View Replies!

SQL Select

Okay, I need some advice. I have a function that calculates the distance between two points. It uses latitudes and longitudes stored in a DB to calculate this distance. No problems there. My question is this though: How do I create a SQL statement to incorporate this distance calculate in my SELECT statement In other words, if someone says I want to search for all companies located within 100 miles of zip code 46589, it would run the SQL, and return the results. Is this possible to accomplish in one query Currently, this is my code, however, I am using multiple queries and then having to sort the distances as well (shortest distance to longet): Code: Set grabDist = siteConn.Execute("SELECT distID, distName, distAddress1, distAddress2, distCity, distState, distPhoneNumber, distEmail, distWebsite, distZipCode, distProducts, distLatitude, distLongitude FROM tblDistributors" & findProduct & " ORDER BY distZipCode ASC") If Not grabDist.EOF Then Do While Not grabDist.EOF zipCode = Trim(grabDist("distZipCode")) If Len(zipCode) = 5 Then If grabDist("distLatitude") AND grabDist("distLongitude") Then intDistance = Round(calcDistance(intLatitude, intLongitude, grabDist("distLatitude"), grabDist("distLongitude")), 1) If CInt(intDistance)
VIEWS ON THIS POST

90

Posted on:

Tuesday 20th November 2012
View Replies!

Expert Guidance Required for SQL??

How I can recover my deleted record in ms sql server 2000 from log files, if I delete a record in sql query analyzer without using begin transaction. Can I open to see log file What is filename & extension of log file and location...
VIEWS ON THIS POST

70

Posted on:

Tuesday 20th November 2012
View Replies!

Convert datime to varchar

, i'm a beginner programmer and I'm having problems with a datasource query I'm using for an application I'm working on. I have field called date_of_request and the datatype is 'datetime' for sql server 2005. Now I have a general select statement in my data source and it brings up my date of request and it brings it up as 04/24/2007 12:00 pm. I don't want the time to show, i want it to just show 04/27/2007. I tried doing a Code: select convert(varchar, date_of_request) from table name but it didn't work. I tried looking online and trying things and that still didn't work. Does anyone know a select query that I can use to pull up my data from that field in a mm/dd/yyyy format without showing the time. I appreciate any help
VIEWS ON THIS POST

107

Posted on:

Tuesday 20th November 2012
View Replies!

Reporting Services HELP

I am trying to use Report builder in both SQL 2005 and SQL 2000. I am trying to run the reports off of my created vies. Cany one one explain how to do this. My process is I open VS 2005 Business Itellgence and select Report Model. Then I selecet my datasource , then my Data source view, then I create my Model. However, it only allows me to view mt Tables, not my created views or created Queries. I need to know how to get my views to be accessible and also is Reporting Model Available for SQL 2000 reporting Services and if not why.
VIEWS ON THIS POST

78

Posted on:

Tuesday 20th November 2012
View Replies!

SQL search latest record....

I need a SQL-statement that gives me the most recent information. In one table I have positions from different objects. For example: object__pos___date____time Object1, pos1, 28-4-2006, 15:00 Object1, pos2, 28-4-2006, 19:00 Object1, pos3, 27-4-2006, 16:00 Object2, pos1, 28-4-2006, 18:00 Object2, pos2, 28-4-2006, 19:00 Etc. I need a SQL query to find the most recent position of a object, f.e. Object 1. SO it have to return the second row, because that's the most recent position of Object 1. What I have now...: SELECT * FROM pos WHERE (Id='object1')and..... Can anyone help me to get the rigth SQL statement\t Thnx!
VIEWS ON THIS POST

121

Posted on:

Tuesday 20th November 2012
View Replies!

Rebuilding PK index

I deleted all rows in the table,now I want that indexes start from 1 and won't continue from the last index. How can I rebuild the indexes of the Primary key
VIEWS ON THIS POST

90

Posted on:

Tuesday 20th November 2012
View Replies!

SQL Subquery Problem

I am trying to extract data from two tables in the same database and simply display a COUNT total. The code is as follows: SELECT tbl_personnel.Assignment, COUNT(tbl_personnel.User_Name) AS Troopers_Number, (SELECT COUNT(*) FROM tbl_twelve_hours GROUP BY tbl_personnel.assignment) AS Completed_Surveys FROM tbl_personnel WHERE tbl_personnel.Region='II' AND (tbl_personnel.Rank='Trooper' OR tbl_personnel.Rank='TFC' OR tbl_personnel.Rank='Corporal' OR tbl_personnel.Rank='Sergeant') GROUP BY tbl_personnel.Assignment I am using an ASP movenext loop to display the results. The problem is that when I run it, the subquery does not loop, it displays the same results (shown below in the "Completed Surveys" column) for all the groups. If I change the subquery GROUP BY statement to tbl_twelve_hours.assignment, I receive an error message that it can only return one record. I have been fooling with this for a few hours and am out of ideas. Any suggestions
VIEWS ON THIS POST

160

Posted on:

Tuesday 20th November 2012
View Replies!

how to update local sql server from production server

I have a sql server database with the web hosting company. I need to download and update the new data every few hours into the local database. Is there a way to do that in sql server 2000 or do i need to write code to do that Paurav
VIEWS ON THIS POST

86

Posted on:

Tuesday 20th November 2012
View Replies!

Using DataFormatString or???

Originally posted by : BombKlan (d98ost@du.se)Does anybody knows if I can divide a string by using DataFormatStringThis is my problem.I need to divide the outputs from my SQL query by 60 to get a correct result.or...maybe I should do this operation in the query it selfI'm kinda novice at this but my query looks like this("SELECT Starttime, Stoptime from timetable", myConnection)The output from Starttime and Stoptime generate x minutes after midnight so I need to divide em by 60 to get a correct stroke of the clock.ie. Starttime generates 10.00 instead of 600 and Stoptime generates 12.00 instead of 720!!!Thanx alot!/O.S.
VIEWS ON THIS POST

76

Posted on:

Tuesday 20th November 2012
View Replies!

Additional Licences for MS-SQL when used with asp?

Originally posted by : Simon (schoong@onebox.com)Hi ,I was informed by my ISP that when I use MS-SQL with IIS, I'm required to purchase additional Internet connection licences for SQL from microsoft in order to use in on the internet.Is this true He said that it would cost an additional 300% of the MS-SQL for 5 CAL licences. This is very expensive....If this is true, can I use other oracle 8i with .asp
VIEWS ON THIS POST

85

Posted on:

Tuesday 20th November 2012
View Replies!

How to get a different random number?

Originally posted by : Bob Lai (laipy966@pchome.com.tw)I have problems in getting a different random number using the randomize function or n=Int(10*rnd())+1.How can I check with a key so that the number generated each time will not duplicate itself
VIEWS ON THIS POST

73

Posted on:

Tuesday 20th November 2012
View Replies!

Making text in a text area a hyperlink by highlighting...

Originally posted by : kyoung (k_young2000@hotmail.com)I want to make text in a textarea a hyperlink by highlighting the text, click a button and it will add the proper HTML tags so that someone who does not know HTML can add it. The info in the textarea will be submitted to a db and when it is taken out of the db it will be formatted as a hyperlink. I have looked all over the MS site and everywhere else, active x maybe\t Any ideas\t\t\t\t
VIEWS ON THIS POST

84

Posted on:

Tuesday 20th November 2012
View Replies!

CASE Statement Problem

Attempting to do this query in Access and getting a syntax error in regard to the CASE SELECT [Print Production Table].[Project Name],[Print Production Table].[Print File Name], CASE WHEN [Shipped Date]-[File Create Date] > 0 then 'Y' else 'N' end AS [SLA MISSED] FROM [Print Production Input Table];
VIEWS ON THIS POST

96

Posted on:

Monday 26th November 2012
View Replies!

Max of Sums query....

Need help with a query. Imagine the following table: tblExample ID...Comp...Date.......Value 1....A......1/1/08.....17 2....A......1/1/08.....22 3....A......1/1/08.....19 4....A......1/2/08.....22 5....A......1/2/08.....25 6....A......1/3/08.....15 7....B......1/1/08.....31 8....B......1/1/08.....30 9....B......1/2/08.....28 10...B......1/3/08.....33 What I need is a way to sum each company for each day and then take the max of those sums. For instance, taking the sums of the above companies for each day would yield: Comp...Date.....Value A......1/1/08...58 A......1/2/08...47 A......1/3/08...15 B......1/1/08...61 B......1/2/08...28 B......1/3/08...33 Then, I would need to take the max of these values. So, the overall result of the query should look like this: Comp...Date.....max(value) A......1/1/08...58 B......1/1/08...61 The range is going to specified by the user, but for that given range, I need to sum the values for each day and then take the max and return the day(s) the max occurred and what the max was for each company.
VIEWS ON THIS POST

123

Posted on:

Monday 26th November 2012
View Replies!

Insert Trigger Issue

Hi all, i have created a trigger which when a new record is inserted and a certain feel equals poor it writes the data to a file. However it appears to be sending thousands of records, i tested it as an update and worked fine but not for an insert. What have i done wrong Code: ALTER TRIGGER [dbo].[tr_CustomerSatisfactionAlert] ON [dbo].[tbl_repair_visit] FOR INSERT AS INSERT alertmessagedata (TypeID, Contract, propref, ORDERID, ENGINEER, Sent, JobType) SELECT '5', tbl_property.contract, tbl_repair_visit.propref, tbl_repair_visit.orderid, tbl_repair_visit.engineer, 'False', 'Repair' FROM TBL_REPAIR_VISIT INNER JOIN TBL_PROPERTY ON TBL_REPAIR_VISIT.PROPREF = TBL_PROPERTY.PROPREF WHERE tbl_repair_visit.TENANTSATISFACTION = 'Poor'
VIEWS ON THIS POST

60

Posted on:

Monday 26th November 2012
View Replies!

How is SQL / ODB used?

Now, I hate to ask such a basic question here but I have googled and found no explanation / tutorial that makes sense to my brain. So here's some questions: I've learned a little about accessing SQL databases via ASP as well as MySQL via Telnet. And though everythings still a little fuzzy, one of my main concerns is this: 1. How is a database created No - I'm not looking for anyone to post code. But I'm wondering if an SQL db is something created manually - laid out by the developer/administrator; and then accessed, modified, and appended via code. Or, can you create a database via code at run time 2. Until now I've used ASP filestreams to store user inputted data across simple text files. I'm now moving into writing a new forum/BBS. (something I've done in the past using these simple textfile structures) I'm wondering if an SQL database is a better method of storage Especially since I've noted a tendency of the server to corrupt my textstream created files every now and then. 3. Can anyone suggest some quick and simple tutorials that A.) show how to set up a simple db using MySQL via Telnet, and B.) give basic examples of ODB object access methods in vbScript
VIEWS ON THIS POST

86

Posted on:

Monday 26th November 2012
View Replies!

Probably a simple problem

I have two tables, one has professors names in them (only 3 records) (table name: Name) and the other table (results) has survey records which include the name of the professor as one of the fields. What I would to have is a query that would show me the number of surveys taken for all 3 professors. The problem is that 2 of these professors have no records in the Results table so that when i run a count of ID (primary key in the Results table) it only shows 1 professor with the count of 60 but it doesn't show the other professors with 0. My query only returns 1 record, but I need all 3 with two of them with a zero next to them This is what i have: SELECT Name.Name, Count(Results.ID) AS CountOfID FROM Name INNER JOIN Results ON Name.Name = Results.InsName GROUP BY Name.Name; HELP =)) Thank you for your time.
VIEWS ON THIS POST

78

Posted on:

Monday 26th November 2012
View Replies!

SQL Query - HELP

I have a table called evolution project with four fields: month, proj_id (Project ID), user_id (User ID) and role. The month field is in the format: 200202. the other fields are numbers. Each project has multiple users. A user may be associated with more than one project. The role field is simply a number based on a key for different roles the user can performin the project: developer, passive etc. The number of users associated with a project is called the degree of the project. I want to query out all projects with a degree greater than a certain constant 'c', using SQL. HELP PLEASE.
VIEWS ON THIS POST

58

Posted on:

Monday 26th November 2012
View Replies!

Only Todays Dates

Trying to create a board that only displays todays records out of the database and can't get it to work. CurrDate = Date() x = Date() y = Date() + 1 sql = " SELECT * from calender_event " sql = sql & " where (('" & x & "' >= start_dt) and ('" & y & "' = start_dt) and ('" & x & "'
VIEWS ON THIS POST

76

Posted on:

Monday 26th November 2012
View Replies!

SQL statement to retrieve last two rows

! I am writing a program in vb.net ! Using an oledbdataadapter i generate a dataset and display dataset records in a datagrid The user will be able to add new records to this datagrid which will update the database(access) upon the click on the update BUTTON when the user clicks update i get the number of row added if there are any now what i want to do is retrieve that last rows added from the same table! I.E. if there were two rows added to the datagrid, then i want to retrieve these two rows later for a reason! Is there an SQL statement to retrieve the last two records (rows) from a table in Access pls help
VIEWS ON THIS POST

150

Posted on:

Monday 26th November 2012
View Replies!

Database not being updated

I am using the following asp code to update an existing record in a database, but the database is not being updated. I am not receiving any errors. ASP: 'create and open the database obj Set objConn = server.createobject("ADODB.Connection") objConn.Open Application("IssueConnPICCM") Call ConnErrCheck(objConn) 'check for errors Set objCmd = server.createobject("ADODB.Command") Set objCmd.ActiveConnection = objConn ProblemID = Trim(request.form("ProblemID")) ConfirmDate = Date& " " &Time Comments = trim(request.form("Comments")) Comments = replace(Comments, "'", "''") Status = "Closed" objCmd.CommandText = "{ = CALL sp_upd_confirm('" & _ ProblemID& "','" &_ Status& "','" &_ Comments& "','" &_ ConfirmDate& "','" &_ ReturnCode& "')}" objCmd.CommandType = adCmdText objCmd(0).Direction = adParamReturnValue objCmd.Execute ReturnCode = objCmd(0) Call ConnErrCheck(objConn) 'check for errors and here is the stored procedure it is calling: CREATE PROCEDURE sp_upd_confirm(@problem_id int, @status varchar(20), @comments varchar(4000), @confirm_date datetime, @return_code int OUTPUT) AS UPDATE Issues SET Status = @status, Comments = @comments, ConfirmDateTime = @confirm_date WHERE ProblemID = @problem_id if @@error = 0 begin set @return_code = 0 end else begin set @return_code = 1 end return @return_code GO Any ideas of what I am missing Thank you in advance!
VIEWS ON THIS POST

130

Posted on:

Monday 26th November 2012
View Replies!

Moving SQL Server 7.0

Originally posted by : Alex (AlexLut@aol.com)What is the best way to move entire SQL Server 7.0 database from one server to another
VIEWS ON THIS POST

154

Posted on:

Monday 26th November 2012
View Replies!

sinqle quotes in SQL statements

Originally posted by : PCalabria (PCalabria@pcenterprises.com)I'm trying to update a character field in my access database with a string that contains a sinqle quote. VB sript doesn't mind the quote at all, strBookList="Pat's", but when I try to add the line to an SQL UPDATE statement, all hell breaks loose because strings need to be surrounded with single quotes.
VIEWS ON THIS POST

135

Posted on:

Monday 26th November 2012
View Replies!

Study materials for 1Z0-058?

Is there anyone who passed Oracle 1Z0-058 testany tips to study for itcan you suggest me where can i get the study materials
VIEWS ON THIS POST

116

Posted on:

Tuesday 27th November 2012
View Replies!

Need some help with sql urgently please

hi i have an assignment dude tonight and as usual i have left it to the last minute >< . I am having trouble with the last 3 questions and if u are able to help i would really appreciate it i will donate $10 to a charity or your website the tables are BOOKING HOTEL_NO GUEST_NO DATE_FROM DATE_TO ROOM_NO GUEST GUEST_NO G_NAME H_ADDRESS HOTEL HOTEL_NO H_NAME H_ADDRESS ROOM ROOM_NO HOTEL_NO R_TYPE R_PRICE Question 8 For the Sheraton hotel, list (in the ascending order) the total number of bookings for each room type. SELECT HOTEL_NO, COUNT(*) AS TOTALCOUNT FROM ROOM, BOOKING WHERE H.H_NAME = 'SHERATON' AND B.HOTEL_NO = R.HOTEL_NO GROUP BY HOTEL_NO; Question 9 Find numbers and names of guests who booked rooms (at any hotel) in the price range between 230 and 350 dollars. Show also how many times they booked such rooms. SELECT GUEST_NO, G_NAME, R_PRICE FROM GUEST, ROOM WHERE R_PRICE >230
VIEWS ON THIS POST

87

Posted on:

Friday 28th December 2012
View Replies!

[MySQL 5.0.45-community-nt-log] Sort the ?rollup? in group by

! I found that the "with rollup" option used with group by is very useful. But it does not behave with "order by" clause. Is there any way to order by my way Code: SELECT COALESCE (name, 'GRAND TOTAL') AS name, tot FROM ( SELECT name, COUNT(*) AS tot FROM tbl_p ) GROUP BY name WITH ROLLUP ) Q ORDER BY CASE WHEN name = 'GRAND TOTAL' THEN 1 ELSE 0 END; Output: Code: +------------------------------+--------+ | name | tot | +------------------------------+--------+ | SEAN DUBOIS | 3 | | SEAN MAXWELL | 2 | | GRAND TOTAL | 39 | | DILLINGER BOY | 12 | | DILLINGER GIRL | 22 | +------------------------------+--------+ I need this output: Code: +------------------------------+--------+ | name | tot | +------------------------------+--------+ | GRAND TOTAL | 39 | | DILLINGER BOY | 12 | | DILLINGER GIRL | 22 | | SEAN DUBOIS | 3 | | SEAN MAXWELL | 2 | +------------------------------+--------+ Could you please help
VIEWS ON THIS POST

104

Posted on:

Sunday 30th December 2012
View Replies!

Database Wiki Page

Hi. I have been tasked with creating a wiki page on a chosen database. Being an amateur, I do not know what details of the database I should research on, but I do know that I am required to research the security model of that database. I have chosen to do research on MS-Access 2010. What could I possibly research on
VIEWS ON THIS POST

106

Posted on:

Sunday 30th December 2012
View Replies!

Select Statement for Oracle

Good day for all, I have used MySQL database for my previous projects but now I need to try some on oracle. At the beginner to the Oracle, I decide to familiarize myself with oracle by testing my previous projects in Oracle instead of MySQL. I have the select statement here in the 'try' block which I have used in the java bean. Here's the code: Code: String strQry = "SELECT T_STAFF*, T_DEPARTMENT.CODE, T_ROLE.CODE, T_STATUS.CODE, "; strQry += "T_DEPARTMENT.DESCRIPTION, T_ROLE.DESCRIPTION, T_STATUS.DESCRIPTION "; strQry += "FROM T_DEPARTMENT, T_ROLE, T_STATUS, T_STAFF "; strQry += "WHERE STAFF_NAME = '"+strUserName+"' "; strQry += "AND T_STAFF.STAFF_DEPARTMENT_OBJECTID = T_DEPARTMENT.OBJECTID "; strQry += "AND T_STAFF.STAFF_ROLE_OBJECTID = T_ROLE.OBJECTID "; strQry += "AND T_STAFF.STAFF_STATUS_OBJECTID = T_STATUS.OBJECTID "; rs = stmt.executeQuery(strQry); I need to know, can I use the same string above for Oracle If no, how should the string look like
VIEWS ON THIS POST

88

Posted on:

Sunday 30th December 2012
View Replies!

help with REGEXP

I have a table in which one column stores some alphanumeric values I am trying to get a count of records in my database that have a particular format (and those that don't) The column has values in the following formats AAxxxxxxxxxAAAAA and xxxxxxxxxxxxxxxx where AA are alpha characters and xxxx are numeric characters I tried the following to get the count of ones formated like AAxxxxxxxxxAAAAA I also tried replacing the REGEXP with like but still I am getting 0 all the time Code MySQL: SELECT count( * ) FROM mytable WHERE myfield REGEXP '[A-Z]{2}-[0-9]{9}-[A-Z]{5}';
VIEWS ON THIS POST

72

Posted on:

Sunday 30th December 2012
View Replies!