UPDATE Statement With A JOIN Condition
Hi all,
HERE IS MY UPDATE STATEMENT:
Update WACTS_Inventory_Part_Loc_Tbl SET WIPLT_Part_New_Qty = WIPLT.WIPLT_Part_New_Qty + tmp.MATIN_QTY FROM
WACTS_Inventory_Part_Loc_Tbl WIPLT
INNER JOIN
Temp_MatIn_Data tmp
ON
WIPLT.WIPLT_Part_Number_MSBA=tmp.PARTS_ShortID
WHERE
WIPLT.WIPLT_Location_Code='MF'
I have a Problem with this satment becoz my Temp_Matin_Data_Tmp table contains two records with the same PartNumber that is (ShortId) and the two records have two different quantities.
The WACTS_Inventory_Part_Loc_Tbl has only one Part NUmber Record and the key is the PartNUmber and Location_Code.
Now when the Update Satement runs it updates the qty only from the first record of the temp table and does not update the quantity of the second record from tne source table.
I woould appreciate any help on this.
Thanks,
Jothi
View Complete Forum Thread with Replies
Related Forum Messages:
Update Statement Performing Table Lock Even Though Where Condition On Clustered Primary Index?
Hi All,I have a database that is serving a web site with reasonably hightraffiic.We're getting errors at certain points where processes are beinglocked. In particular, one of our people has suggested that an updatestatement contained within a stored procedure that uses a wherecondition that only touches on a column that has a clustered primaryindex on it will still cause a table lock.So, for example:UPDATE ORDERS SETprod = @product,val = @valWHERE ordid = @ordidIn this case ordid has a clustered primary index on it.Can anyone tell me if this would be the case, and if there's a way ofensuring that we are only doing a row lock on the record specified inthe where condition?Many, many thanks in advance!Much warmth,Murray
View Replies !
Help With Inner Join In Update Statement
Here is my update statements which doesn't work, can you show me an example or provide a hint. thanks update property inner join propinv on propinvid=propertyinvid set property.lotsize='100' where property.lotsize <> '' and property.lotize is not null Thank you
View Replies !
Update Statement With Join
Hey guys, Up to this point I've been dealing with mostly select statements but time has come, and I need to do an update. Basically I'm not sure how to structure the query. I'd like to update the field "new_applicationreceived" to the value of "new_lastcontact" based off the results of the following select query: select new_lastcontact from lead LEFT JOIN StringMap ON Lead.SalesStageCode = StringMap.AttributeValue AND StringMap.AttributeName = 'SalesStageCode' AND StringMap.ObjectTypeCode = 4 where new_applicationreceived is null and lead.salesstagecode = 5 I'd really appreciate your help - I'm very worried about messing up the data and don't want to screw something up.
View Replies !
One Statement Update - Join, No Cursor ?
HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!
View Replies !
Can We Have An Inner Join Clause In An Update Statement
Hi, I'm trying to inner join an update statement. Something like this: update #point_connection_temp AS a inner join #point_connection_temp_two as b on a.key_fld = b.key_fld set a.next_point = b.next_point where #point_connection_temp.next_point is null order by a.key_fld I'm getting an error message:Incorrect syntax error near AS Any help will be greatly appreciated.Thank you!!!!!!!!!1
View Replies !
IF Condition In Join??
Dear GroupI'd be grateful if you can send me on the right track in achieving this.I have three tables A,B,C outlined as follows:Table: AField: RowIDField: EntityIDField: TypeIdentifierTable: BField: RowIDField: NameTable: CField: RowIDField: NameLet's assume I've the following records:Table A:1,1,02,1,1Table B:1,SmithTable C:1,XYZCorporationThe table joins are as follows:A.EntityID = B.RowIDA.EntityID = C.RowIDI would like to select all records from Table A and display the Names fromeither Table B or Table C, depending on the Field TypeIdentifier.E.g.: SELECT Name FROM A JOIN B ON (A.EntityID = B.RowID) JOIN C ON(A.EntityID = C.RowID) IF TypeIdentifier = 0 SELECT Name FROM B IFTypeIdentifier = 1 SELECT Name FROM CResultset:SmithXYZCorporationIs this somehow possible?Thanks very much for your time & efforts!Martin
View Replies !
Difference On Condition In JOIN && WHERE
I appreciate how difficult it is to resolve a problem without all theinformation but maybe someone has come across a similar problem.I have an 'extract' table which has 1853 rows when I ask for all rows whereperiod_ = 3. The allocation table for info has 210 rows.I have two scripts below. The first script where I specify a period on ajoin, brings back 1853 lines and works. The second script where I specifythe period in the where clause only brings back 1844 rows. I have locatedthe missing 9 rows and they don't look any different to the other 1844 rows.Can someone educate me as to the difference between specifying a conditionon a join and a condition in a where clause.SELECTa.costcentre_,b.nett_,a.*,b.*FROMextract aLEFT OUTER JOINallocation bONa.e_reg_ = b.reg_no_ANDb.period_ = 3WHEREa.period_ = 3--------------SELECTa.costcentre_,b.nett_,a.*,b.*FROMextract aLEFT OUTER JOINallocation bONa.e_reg_ = b.reg_no_WHEREa.period_ = 3ANDb.period_ = 3
View Replies !
JOIN Based On LIKE Condition
I'm trying to join two tables based on a like condition. The first table contains the full IP, e.g. '166.27.12.24' and the second contains a 2 octet range, e.g. '166.27', which I need to join. Table 1 -> TRAFFIC (Time, SourceIP) Table 2 -> IP_ROSTER (IP2OctetRange, Administrator) I've tried the following, but it does not seem to work: SELECT TOP 100 SOURCE_IP, r.IP2OctetRange, r.Administrator FROM TRAFFIC LEFT JOIN IP_ROSTER AS r ON SOURCE_IP LIKE RTRIM(LTRIM(IP2OctetRange))+'%'
View Replies !
Condition In Left Outer Join
Hi, I want to get join of two tables A and B in such a way that all the Data from A for a particular Date should be extracted and then from table B Reqired column should selected against that data (it can be null). But if i use the following query: 1) Select A.*,B.Reqired from A Left Outer Join B on A.Same=B.Same Where A.Date = '1/22/2008' it first joins the Table A and B (A contains millions of records) which takes a lot of time and then where is applied which takes a lot of time. And if I use the following query: 2)Select A.*,B.Reqired from A Left Outer Join B on A.Same=B.Same AND A.Date = '1/22/2008' in this case A.Date = '1/22/2008' has no effect and all the data from the table A comes. I have also tried first making the temp table for A.Date = '1/22/2008' and the joining with B but its cost is same as 1. For this purpose Oracle allows the use of (+) operator like: 3)Select A.*,B.Reqired from A,B Where A.Same=B.Same(+) AND A.Date = '1/22/2008' which means all data from A for '1/22/2008' Plus B.Required against it whether its null or not. Please let me know if there is a way around in MS SQL SERVER 2005. Thanx.
View Replies !
SQL-Question: Natural Join With Condition
Hello! I have two tablestable1: table2:person Manager---------- -----------------*id *id*Manager_id *Name*Branch *Title Now I want to select person.* and Mangager.Name, where person.Manager_id=Manager.id.Ok, that would be easy for me, but the point is, that the column person.Manager_id might be empty or =0 (as integer), and in that case I want to have the person.* data too.So, something like Select person.*, Manager.id From person, Manager Where person.id=4 AND person.Manager=id=Manager.id doesn't work. Can somebody explain me how to fix it? I'm using MS-SQL2000 if it is important.... Best regards!
View Replies !
Some Question About Left Join On Condition?
Left join on condition has no effect in the left table itself? I have two tables: one named student studentID studentName -------------------------------------------------- -------------------------------------------------- 1 Jom 2 Jim 3 Tom 4 Kate 5 LinDa 6 DaiAnna The other is grade table studentID subject grade -------------------------------------------------- -------------------------------------------------- ----------- 1 math 90 2 Chinese 90 1 Chinese 80 3 math 98 4 math 76 And I write two SQLs 1: select students.studentName , grades.grade as math_grade from student as students left outer join grade as grades on students.studentID = grades.studentID and grades.subject = 'math' 2: select students.studentName , grades.grade as math_grade from grade as grades left outer join student as students on students.studentID= grades.studentID and grades.subject = 'math' After execute, the answer is 1: studentName math_grade -------------------------------------------------- ----------- Jom 90 Jim NULL Tom 98 Kate 76 LinDa NULL DaiAnna NULL 2: studentName math_grade -------------------------------------------------- ----------- Jom 90 NULL 90 NULL 80 Tom 98 Kate 76 The math_grade of the second answer is all the grade rows of the grade table. on grades.subjec='math' has no effect. Why SQL will act it like this? I know it's not a bug. But I really wanna know the inside work. Thanks in advance, with very appreciation.
View Replies !
Opinion/Standard For Join Condition
Hi, I was having a conversation with another developer about the order that the join conditions are placed. More specifically, about the order the tables are referenced in the join condition. There are two ways to do it, for example:-- Here the table that is referenced first was "declared" first. SELECT* FROM Logon LEFT OUTER JOIN Thread ON Logon.LogonID = Thread.LogonID LEFT OUTER JOIN Message ON Thread.ThreadID = Message.ThreadID -- Here the table that is referenced first is the table being joined directly above it. SELECT* FROM Logon LEFT OUTER JOIN Thread ON Thread.LogonID = Logon.LogonID LEFT OUTER JOIN Message ON Message.ThreadID = Thread.ThreadID I realize this is not that big of a deal, but I was wondering if anyone had a good/valid reason for doing one versus the other. Just curious, -Ryan
View Replies !
Join Condition In Remote Server Problem?
Hi Everybody, Remote Server Setup:- <sql6.5 - local server> & <sql7.0 - remote server> ==================== 1. sql6.5 server is acting as Local Server. 2. sql7.0 server is configured as remote server for sql6.5. 3. Somebody has written a stored procedure in sql6.5 server(it is not a remote stored procedure in sql7.0). In that stored procedure(in sql6.5 server) they are comparing the tables of local server(I mean sql6.5 server tables) with the remote server tables(I mean sql7.0 tables). That is, this is nothing but displaying(selecting) the values using join conditions. 4. I tell u the actual coding they written in the stored procedure, select a.col1,a.col3,b.col4,c.col8,z.col6,count(z.col8) from table1 a,table2 b,table3 c,database11..table4 z where a.col1 = b.col2 and a.col3 = c.col3 and a.col1 = z.col1 group by a.col1,a.col3,b.col4,c.col8,z.col6 order by a.col1,a.col3,b.col4,c.col8,z.col6 The above is an example(similar to the actual coding) used in the stored procedure(local server). In the above example <table1,table2 & table3> are belongs to the local server(sql6.5) and <database11..table4> belongs to the database <database11> of remote server(sql7.0). 5. I said it is not possible from 6.5 to 7.0(I mean the above join condition). But they are telling that it was working fine for several months. Now only they r facing problem like this. 6. I hv given a solution to them by accessing from 7.0 to 6.5. But they r not accepting this. Can anybody tell me is it possible to extract datas by using the above joing conditions from 6.5 to 7.0. tks in advance, Vasu
View Replies !
Like Condition In Sql Statement
This is my sql query Select DOJ AS 'JoiningDate' from emp where DOJ like '%2008/09/04%' there are 8 records on this time.But it displays no result . The column is datetime datatype year month date or we need to mention any thing other than this.
View Replies !
Hi, How Could Put A Condition In Statement.
Hi, i wanted to put a condition based on my codes below.. but where i had to put the condition? like where QTY=3 please help.. INSERT INTO magpatoc.dbo.RSOTransfer(RSONO, Customer, ItemCode, ItemDescription, Source, MOQ, QuantityRequired, Remarks, ZeroStock, NewProduct, ProjectForecast, WithMotherPO, Other, RequestedBy, RequestedDatetime, NotedBy, RecievedBy, RecievedDatetime, PreparedBy, PreparedDatetime, ApprovedBy, ApprovedDate, ReservationNoDate, PurchaseOrderNo) SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','c:CopyOfRSODB.mdb';'admin';'',FinalCustItemRSO) ed9teenMagnaza
View Replies !
If Condition In Select Statement...
Hi, I need to write an if condition in SELECT statement. Below is the code for the same. But its throwing error. Can some refine the code below. SELECT tblCustomer.Customer_LegalName, (IF (tblCustomer.IsNRACustomer = TRUE) SELECT tblCustomer.Customer_PassportNo ELSE ISNULL(tblCustomer.Customer_TaxId, tblCustomer.Customer_PassportNo)) AS TAXID, tblCustomer.Customer_DoingBusinessAs, tblSeed_EDDCategory.CategoryName, '2' AS DCS, tblUser_OfficerCode.User_OfficerCode, tblCustomer.Customer_AreaId, tblCustomer.Customer_BranchId, CONVERT(VARCHAR(11), tblCustomer_EDDCategory.CreateDate) AS CreateDate, tblSeed_EDDCategory.EDDCategoryId, tblCustomer_EDDCategory.Category_CreateEmpId, tblCustomer_EDDCategory.CustCatId, tblCustomer.Customer_Id, tblSeed_Area.AreaName, tblSeed_Employee.Name, tblUser_OfficerCode.User_OfficerName, tblCustomer.Customer_TaxId, tblCustomer.IsNRACustomer, tblCustomer.Customer_PassportNo FROMtblCustomer INNER JOIN blCustomer_EDDCategory ON tblCustomer.Customer_Id = tblCustomer_EDDCategory.CustomerId INNER JOIN tblSeed_EDDCategory ON tblCustomer_EDDCategory.EDDCategoryId = tblSeed_EDDCategory.EDDCategoryId INNER JOIN tblSeed_Employee ON tblCustomer.Customer_CreateEmpId = tblSeed_Employee.EmployeeId INNER JOIN tblUser_OfficerCode ON tblCustomer.Customer_CreateEmpId = tblUser_OfficerCode.EmployeeId INNER JOIN tblSeed_Area ON tblCustomer.Customer_AreaId = tblSeed_Area.AreaId Thanks, Rahul Jha
View Replies !
'IF' Condition On Select Statement?
Can you implement the same type of feature in SQL as MS Access uses with it's "immediate if" or iif() function? In other words can you look at a specific row and change the contents of one field based on the contents of another field in the same row? I am trying to create a view like the the following: CREATE VIEW AS Test SELECT name, city , IF city = 'NY' state ELSE country FROM address_table The view's 3rd field will be either [state] or [country] depending on the contents of [city] Thanks in advance! ..Marisa
View Replies !
Need Help On Select Statement Wit Like Condition
I tried following code and it return no data after query run successfully. I m testing it on msaccess . SELECT Book.ID, Book.Title, Book.Arthor, Book.Publisher, Book.Year, Book.Price, Inventory.B_ID, Inventory.Quantity FROM Book INNER JOIN Inventory ON Book.ID=Inventory.B_ID WHERE Book.ID=Inventory.B_ID And ("Book.Arthor" Like '%es%'); ------- I have test and test2 in Arthor column ... Thanks all !!
View Replies !
Condition In Select Statement
col1 col2 col3 col4 36930.60 145 N . 00 17618.43 190 N . 00 6259.20 115 N .00 8175.45 19 N .00 18022.54 212 N .00 111.07 212 B 100 13393.05 67 N .00 In above 4 col if col3 value is B then cursor has to fectch appropriate value from col4. if col3 value is N then cursor has to fectch appropriate value from col1. and also wants the sum of result above conditions when column 3 has two values(i.e N,B) for single col2 value here col2 values are unique take an example for col2=212 if col3=B then result is 100 if col3=N then result is 18022.54 for a single col 2 value ,if col3= N and B then we want sum of above 2 conditions i.e (100+18022.54) but not the sum of (100+18022.54+111.07+0.0) . Can any one reply for this..............
View Replies !
WHERE Condition In SELECT Statement
Hi All, I want get the result in a single SELECT statement SELECT SUM (PAID_LOSS), SUM (PAID_EXP) GROUP BY COMPANY FROM VIEW_POLICY WHERE Accounting_Period GE 200701 and LE 200712 -************************************ SELECT SUM (MEDICAL_RESERVE) GROUP BY COMPANY FROM VIEW_POLICY WHERE Accounting_Period LE 200712
View Replies !
Derived Column From A Condition Statement
I've found that there is no such thing as a Case Statement in the Derived Column task in the data flow objects. I've written a ternary statement instead. I can't seem to get it to work exactly how I want it to. For example AccountCategory == "E" ? 1 : 2 Works fine but the following doesn't AccountCategory == "E" ? CreditAmount : DebitAmount The CreditAmount and DebitAmount Fields are spelled correctly, the field type of AccountCategory is String and the Field type of CreditAmount and DebitAmount is numeric, but that seems to be the same situation that I have in the first example that works. Am I missing something? I'd appreciate any advice anyone has to offer. Thanks, Bill
View Replies !
Update Query Condition
How can update / insert records from table1 colum to table2 colum with condition ? I want to insert/update records like this. update employee set joine_date = select joine_date from master_table where emp_id.employee=emp_id.master_table regards Martin
View Replies !
How To Put Condition In Select Statement To Write A Cursor
col1 col2 col3 col4 36930.60 145 N . 00 17618.43 190 N . 00 6259.20 115 N .00 8175.45 19 N .00 18022.54 212 N .00 111.07 212 B .00 13393.05 67 N .00 In above 4 col if col3 value is B then cursor has to fectch appropriate value from col4. if col3 value is N then cursor has to fectch appropriate value from col1. here col2 values are unique. Can any one reply for this..............
View Replies !
Update Condition(store Procedure)
can anyone help to create storeprocedure in SQL SERVER 2000? the case is like this: i have one table call [TIMETABLE] which contains these fields:ID,start_time,finish_time,subject_code,venu e and another table call [attendant_sheet] has these following fields:ID(which should be from TIMETABLE),student_id,subject_code,status,date. and the storeprocedure will works like this: if the time(system time) has pass the finish_time on TIMETABLE table then insert the related field into attendant_sheet. and then update the status field to 'absent' and the student_id should be get it from another table call [enrolment] who havent take their attendance and enrols related subect? could this possible? PLZ HELP ME!
View Replies !
Update Table With Multipe Condition
hi, I want to ask how I want update my table using a multiple condition.My table format is like this: IC_NO F05 F07 F08 F09 Ind 123 452 C 654 852 P 125 A 526 C What I want to do is: IF Ind = C, F05 <> NULL then IC_NO = F05 IF IND = C, F05 = Null and F09 <> Null then IC_NO = F09 IF IND = P, F05 <> Null and F07 <> Null then IC_NO = F07 IF Ind = A, F05 = Null and F08 <> Null then IC_NO = F08 your helpful highly appreciated..thanks
View Replies !
Update All The Records Of A Table On A Condition
Hi I have a two tables as follows Table Category { ID PK, LastUpdate DateTime } Table Master { ID PK Catrgory DateTime } I wanted to update Catrgory coulmn of all records in the Master table with the Value of LastUpdate of the CategoryTable the where the ID of the both the table are same Can any one please let me know the query ~Mohan
View Replies !
Please Help Create Trigger Condition On Update
please help create trigger condition on update IF EXISTS ( SELECT * FROM empList WHERE (unit = 9) ) begin update [dbo].[empList] set unit = CASE WHEN SELECT na,empID, unit FROM empList WHERE (empID IN (111, 222, 333, 555)) AND (unit = 9)) then '4' else t.fld1 end i have an emmployee table empid unit ------------------------------------------------------ 1111 3 2222 9 3333 9 4444 2 5555 2 6666 1 7777 9 8888 2 9999 9 ----------------------------- i need help create trigger condition on update like this ONLY ON EMPID=2222,3333,7777,9999 WHAN ON UPDATE they have unit =9 THAN I NEED THE trigger DO THIS empid unit ------------------------------------------------------ 1111 3 2222 4 3333 4 4444 2 5555 2 6666 1 7777 4 8888 2 9999 4 ONLY IF someone update EMPID=2222 OR 3333 OR 7777 OR 9999 and UNIT=9 THAN automatic change 9 TO 4 TNX for the help
View Replies !
Is There A Way To Know The Condition In An Update/Delete Command Inside A SP?
Hi to all: I'm trying to make a sp who generates an export string for everey sql command, for example: Insert, update and delete. In the case of Insert I just have the sp, but in "update" and "delete" commands... how can I know the conditions?.. UDTATE DemoTable SET DemoField=77 WHERE (Condition1 AND Condition2) ....may I use a comparative with 'updated' table?, please help me with your ideas Thanks a lot.
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 !
UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View Replies !
COndition Spli - Error Date Condition
Dear friends, I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well... I need to filter rows in a dataflow... I created a condition spli to that... maybe there is a better solution... And the condition is: Datex != NULL(DT_DATE) (Some DATE != NULL) [Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error. What is wrong?? Regards, Pedro
View Replies !
Stored Procedure - Update Statement Does Not Seem To Update Straight Away
Hello, I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working. I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
View Replies !
Use If Else In A Join Statement
Hi Everybody, I'm trying to join two tables on the condition that field "A" on Table 1 = field "B" on table 2 but the problem is field "A" on table 1 and Field "B" on table 2 can have null values in which case I want to use field "C" on table 1 = field "D" on table 2. To accomplish this, I need to use if then statement inside the join statement. I tried using it, but its giving error saying there is an error near "if" and near "inner join" statement. How can this be accomplished ? any help is appriciated. devmetz
View Replies !
How To Join Sql Statement
Hi all, I had some sql statement query and would require your help. I would like to query data from table A and table B and would like to join the data and return as a result. Take for example, Query A: select timestart, timeend from TableA where product = 'A' Query B : select timestart, timeend from TableB where product = 'B' How can I join QueryA and QueryB and return as a single result? Thanks
View Replies !
Help On Join Statement
I have table A:ID intName textAnd Table BID intName text Now, I want to select all records from A where there is no matching record in B based on the IDI want to do this with a JOIN statement and not a subquery as I understood that the execution plan for JOIN statements is more efficient...Any help?
View Replies !
Help On Join Statement
I have two tables: tblUserDataUserNameUserCode tblBlogsUserCodeBlogText I have an SP which takes the username as a variable. How can I select all blogtext from tblBlogs where the usercode belonging to the username in tblUserdata is equal to the usercode in tblBlogs? so select all blogs for a specfic username...
View Replies !
JOIN Statement 'Help'
I am very new to SQL and need to create a statement that will JOIN data from 3 tables into my datagrid. The following are the tables: Table A: Compliance - FinancialsID -NetWorth -DebtRatio -WorkCapital Table B: Financials - FinancialsID - cAssets - TransDate - CustomerID Table C: CompanyInfo - CustomerID - Company - Agent I need to be able to display Company.CompanyInfo, NetWorth.Compliance, DebtRatio.Compliance, WorkCapital.Compliance in a datagrid and make sure that it ONLY displays the most current entry for the Company. The Compliance table has a relationship to the Financials table through the FinancialsID field and the Financials table is related to the CompanyInfo table through the CustomerID field. The TransDate is a date field in the Financials table. This seems extremely confusing to me, but I am sure its easier than what I am trying to make it. Any help would be GREATLY appreciated. Thanks Garrett
View Replies !
Join Statement
Hi.........Its been awhile since I've touched SQL statements, so I need some helpwith writing a JOIN statement to query 3 tables.The dB has 3 tables with valuesApplications-Application_code(Primary key)-Application_nameApplications_Installed-Computer_name(Pri key)-Application_code(Foreign key/sec key)Workstation_info-Computer_name(Pri key)-Serial_numberWhat I want to do is query the tables for a particular Application codeand name from Applications, so that it returns the values of computernames with the matching values from Applications_installed andWorkstation_info.So I need to do aSelect * from applications where applications.application_code='XXX'join (this is the part I'm stuck how do I tell it to match theapplications.application_code =applications_installed.application_code) then match the computer namesfrom Applications_installed.computer_name with that ofworkstation_info.computer_nameNot sure if I'm explaining this properly....Can anyone help.......
View Replies !
SQL Statement - INNER JOIN
I have two tables: Employees[ID, FirstName, LastName] DLSUs[ID, Title, HeadID, DeputyHeadID, Link] I would like to be able to display (* DLSUs.Title (add its corresponding link)), (Employees.FirstName, Employees.LastName WHERE Emplooyees.ID = DLSUs.HeadID) (Employees.FirstName, Employees.LastName WHERE Emplooyees.ID = DLSUs.DeputyHeadID) The problem with this is that I'm trying to fill a table as it is built, using recordsets and "do loop". In other words I want to have all DLSUs to have there corresponding Title(with Link), Head and Deputy Head. This is what I have so far, it only prints out the first DLSU Title(with Link) with its Firstname, LastName. Should I be using INNER JOIN, I don't fully understand the principle of it. sqlStmt = "SELECT DLSUs.*, Employees.* FROM DLSUs, Employees WHERE DLSUs.DLSUType = 'LSU' AND Employees.ID = DLSUs.HeadID" < ... __do until objRS.EOF ____Response.Write "<tr><td width=""320"">" ____If NOT objRS("Link") = "" Then ______Response.Write "<a href=" & objRS("Link") & ">" & objRS("Title_EN") & "</a>" ____Else ______Response.Write objRS("Title_EN") ____End If ____Response.Write "</td>" ____Response.Write objRS("FirstName") ____Response.Write "</td></tr>" ____objRS.MoveNext __loop __objRS.close() ... > Thanks in advance for your help Gazzou
View Replies !
Help With JOIN Statement
Hi, I'm doing a short lab assignment for a college SQL Server course, and I'm a little stuck up on one of the SELECT statements. Here's the questions: Create a SELECT statement that will return a list of all your employees, their project and the hours they have logged. Here's the SQL Code: CREATE TABLE project_employee ( employee_id INT, employee_name VARCHAR(40), employee_hire_date DATETIME, employee_termination_date DATETIME, employee_billing_rate INT, CONSTRAINT project_employee_pk PRIMARY KEY (employee_id) ); CREATE TABLE project ( project_id INT, project_name VARCHAR(40), project_budget INT, CONSTRAINT project_pk PRIMARY KEY (project_id) ); CREATE TABLE project_hours ( project_id INT, employee_id INT, time_log_date DATETIME, hours_logged INT, dollars_charged INT, CONSTRAINT project_hours_pk PRIMARY KEY (project_id, employee_id, time_log_date), CONSTRAINT proj_hours_fkto_projectemployee FOREIGN KEY (employee_id) REFERENCES project_employee, CONSTRAINT proj_hours_fkto_project FOREIGN KEY (project_id) REFERENCES project ); INSERT INTO project (project_id, project_name, project_budget) VALUES (1001, 'Corporate Web Site',1912000); INSERT INTO project (project_id, project_name, project_budget) VALUES (1002, 'Year 2000 Fixes',999998000); INSERT INTO project (project_id, project_name, project_budget) VALUES (1003, 'Accounting System Implementation',897000); INSERT INTO project (project_id, project_name, project_budget) VALUES (1004, 'Data Warehouse Maintenance',294000); INSERT INTO project (project_id, project_name, project_budget) VALUES (1005, 'TCP/IP Implementation',415000); INSERT INTO project_employee (employee_id, employee_name, employee_hire_date, employee_termination_date, employee_billing_rate) VALUES (101, 'Jonathan Gennick','15-Nov-1961',null,169); INSERT INTO project_employee (employee_id, employee_name, employee_hire_date, employee_termination_date, employee_billing_rate) VALUES (102, 'Jenny Gennick','16-Sep-1964','5-May-1998',135); INSERT INTO project_employee (employee_id, employee_name, employee_hire_date, employee_termination_date, employee_billing_rate) VALUES (104, 'Jeff Gennick','29-Dec-1987','1-Apr-1998',99); INSERT INTO project_employee (employee_id, employee_name, employee_hire_date, employee_termination_date, employee_billing_rate) VALUES (105, 'Horace Walker','15-Jun-1998',null,121); INSERT INTO project_employee (employee_id, employee_name, employee_hire_date, employee_termination_date, employee_billing_rate) VALUES (107, 'Bohdan Khmelnytsky', '2-Jan-1998',null,45); INSERT INTO project_employee (employee_id, employee_name, employee_hire_date, employee_termination_date, employee_billing_rate) VALUES (108, 'Pavlo Chubynsky','1-Mar-1994','15-Nov-1998',220); INSERT INTO project_employee (employee_id, employee_name, employee_hire_date, employee_termination_date, employee_billing_rate) VALUES (110, 'Ivan Mazepa', '4-Apr-1998','30-Sep-1998',84); INSERT INTO project_employee (employee_id, employee_name, employee_hire_date, employee_termination_date, employee_billing_rate) VALUES (111, 'Taras Shevchenko', '23-Aug-1976',null,100); INSERT INTO project_employee (employee_id, employee_name, employee_hire_date, employee_termination_date, employee_billing_rate) VALUES (112, 'Hermon Goche', '15-Nov-1961','4-Apr-1998',70); INSERT INTO project_employee (employee_id, employee_name, employee_hire_date, employee_termination_date, employee_billing_rate) VALUES (113, 'Jacob Marley', '3-Mar-1998','31-Oct-1998',300); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1001,101,'01-JAN-1998',1.00,169.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1003,101,'01-JAN-1998',3.00,507.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1005,101,'01-JAN-1998',5.00,845.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1002,101,'01-FEB-1998',7.00,1183.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1004,101,'01-FEB-1998',1.00,169.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1001,101,'01-MAR-1998',3.00,507.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1003,101,'01-MAR-1998',5.00,845.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1005,101,'01-MAR-1998',7.00,1183.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1002,101,'01-APR-1998',1.00,169.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1004,101,'01-APR-1998',3.00,507.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1001,101,'01-MAY-1998',5.00,845.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1003,101,'01-MAY-1998',7.00,1183.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1005,101,'01-MAY-1998',1.00,169.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1002,101,'01-JUN-1998',3.00,507.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1004,101,'01-JUN-1998',5.00,845.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1001,101,'01-JUL-1998',7.00,1183.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1003,101,'01-JUL-1998',1.00,169.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1005,101,'01-JUL-1998',3.00,507.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1002,101,'01-AUG-1998',5.00,845.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1004,101,'01-AUG-1998',7.00,1183.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1001,101,'01-SEP-1998',1.00,169.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1003,101,'01-SEP-1998',3.00,507.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1005,101,'01-SEP-1998',5.00,845.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1002,101,'01-OCT-1998',7.00,1183.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1004,101,'01-OCT-1998',1.00,169.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1001,101,'01-NOV-1998',3.00,507.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1003,101,'01-NOV-1998',5.00,845.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1005,101,'01-NOV-1998',7.00,1183.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1002,101,'01-DEC-1998',1.00,169.00); INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged) VALUES (1004,101,'01-DEC-1998',3.00,507.00); And here's the SELECT statement that I've wrote so far, but I'm not sure how to get the JOIN working correctly. Tips? SELECT EMPLOYEE_NAME AS 'Employee''s Name', PROJECT_NAME AS 'Project Name', HOURS_LOGGED AS 'Hours Logged' FROM PROJECT_EMPLOYEE, PROJECT, PROJECT_HOURS WHERE PROJECT_HOURS.PROJECT_ID = PROJECT.PROJECT_ID AND PROJECT_EMPLOYEE.EMPLOYEE_ID = PROJECT_HOURS.EMPLOYEE_ID;
View Replies !
Help With SQL Join Statement
I have 3 tables, a Store_Tbl for stores, a Store_Mall_Tbl to associate stores in malls and a Store_ATM_tbl to associate ATM types in stores. Store_Tbl -------------- StoreID|StoreName ----------------- 1|GAP 2|Banana Republic 3|Broadway 4|May Company 5|Sears Store_Mall_Tbl -------------------- StoreID|MallID ----------------- 1|1 2|1 3|1 4|1 5|1 Store_ATM_Tbl ------------------ StoreID|ATMID ------------------ 1|1 2|1 3|1 How can I run a query w/ appropriate outer joins to fetch StoreName and ATM availability given an ATMID and a MallID Ex: Input: ATMID=1, MallID=1 Output: StoreName|ATM --------------- GAP|YES Banana Republic|YES Broadway|YES MayCompany|NO Sears|NO Please Help! Boybles
View Replies !
Join Statement Required
I have the following tablestblFavouritesFavouriteSince datetimeUserCodeOwner int (the usercode of the user whose favouritelist this is)UserCodeFavourite int (the usercode of the user who has been added to the favouritelist of usercodeowner)EXAMPLE DATA10/14/2006 7:32:30 PM 4 710/16/2006 11:24:01 PM 4 510/16/2006 10:55:08 PM 5 4tblUserDataUserID uniqueidentifierUserName nvarchar(50)UserCode intaspnet_UsersUserID uniqueidentifierLastActivityDate datetimeNow I need a join statement that selects the fields aspnet_Users.UserID,aspnet_Users.LastActivityDate,tblUserData.Username,tblUserData.UserCode,tblFavourites.FavouriteSincefor all tblFavourites.UserCodeFavourite where tblFavourites.UserCodeOwner=4Could someone provide me with the join statement because I dont get it anymore :)
View Replies !
SQL Join Statement Problem
Hi,I have the following SQL statement which is pulling a few details froma database. As you can see, there is only the one table from which iam creating a temporary copy.The reason I do this is because in the table i only have the 'standIn'listed by integer and i want to return the 'standIn' by name.I hope this is clear enough.The statement works but i am now noticing that it lists multiplereturns in SQL Analyser e.g it is listing three different rows for oneuser and these have all been past StandIns for the user in question.It is not a problem at the moment but it may be and i would like toknow why it is doing this. Can i change the statement to stop this, ihave been messing with the join part but no luck.Any help greatly appreciated.-----------------------------------------------------SELECT T2.FirstName AS StandIn_FirstName, T2.LastName ASStandIn_LastNameFROM tblStaff AS T1LEFT OUTER JOIN tblStaff AS T2ON T1.StaffNo = T2.StandInWHERE (T1.NTUserName = 'auser')
View Replies !
|
TRACKER
