I have a problem. My provider(ISP) is supporting SQL Native Client driver and my forum supplier is only supporting SQLOLEDB. I am trying to access our sql2005 DB located at our ISP.
I have changed this line:
strCon = "Provider=SQLOLEDB;Connection Timeout=90;" & strCon
To:
strCon = "Driver={SQL Native Client};Connection Timeout=90;" & strCon
Now I can access the database, but when I am trying to loging I get this error:
Server Error in Forum Application
An error has occured while writing to the database.
Please contact the forum administrator.
Support Error Code:- err_SQLServer_loginUser()_update_USR_Code
File Name:- functions_login.asp
Error details:-
Microsoft OLE DB Provider for ODBC Drivers
Query cannot be updated because the FROM clause is not a single simple table name.
What can I do?? I am stuck in between and need a solution.....
Hi everybody, I am a total noob conserning ASP, but I am willing to learn We have a sql2005 SRV(hosted by our ISP, so limited access) and a ASP based forum (WEB WIZ) When I try to login I get this error: Support Error Code:- err_SQLServer_loginUser()_update_USR_CodeFile Name:- functions_login.aspError details:-Microsoft OLE DB Provider for ODBC DriversQuery cannot be updated because the FROM clause is not a single simple table name.Can somebody tell me whats wrong? Thanx in advance. Gerry de Bruijn!
My understanding is that whenever any INSERT, DELETE, or UPDATE statements execute that impacts significant amount of rows in a table, its statistics must be automatically updated by SQL Server. But it was surprise to see in the profiler that after INSERT no update statistics event happen. But the moment a SELECT is executed on the table the event 'Auto Stats' is shown up. What is the reason behind this? Why the Auto Stats not happening immediately after the INSERT statement?
Following code can be used to illustrate this (in the profiler select the Auto Stats event under Performance):
IF(SELECT OBJECT_ID('t1')) IS NOT NULL DROP TABLE t1 GO CREATE TABLE t1(c1 INT, c2 INT IDENTITY) INSERT INTO t1 (c1) VALUES(1) INSERT INTO t1 (c1) VALUES(2) INSERT INTO t1 (c1) VALUES(3) CREATE NONCLUSTERED INDEX i1 ON t1(c1)
GO
--Now add 10,000 rows to update so that statistics are updated. Look into the profiler:
SET NOCOUNT ON GO
DECLARE @n INT SET @n = 1 WHILE @n <= 10000 BEGIN INSERT INTO t1 (c1) VALUES(2) SET @n = @n + 1 END
SET NOCOUNT OFF GO
--Next runt he following statement. The profiler will now show 'Auto Stats' event
i have Two tables... with both the table having LastUpdated Column. And, in my Select Query i m using both the table with inner join.And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
I am building a search application that has several fields in it - I want to be able to allow the user to put in specific search criteria or all them to say "All" and then pull back any records with anything in that row. Example Select *From mytableWHERE (SDate >= 1-1-2007) AND (EDate <= 1-25-2007) AND (Location = "ALL" or In ALL) this is where I am cloudy.. What/How would I write in the query to pull back everything, I know I could just leave the Location out of the query all together, but then that does not allow them to select just one the next time they run the application. Thoughts? Thanks,Ad.
Hi everyone, I am trying to run this query and it is not returning a single row. Although I can see one row with this 10/14/2005 date in the table.SELECT iSourceId, UserId,FROM tblEmployeeWHERE dtInsertDate >= '10/14/2005' and dtInsertDate <='10/14/2005'I also tried to rewrite the query this waySELECT iSourceId, UserId,FROM tblEmployeeWHERE dtInsertDate = '10/14/2005' but still it is not returning a single row.Please let me know what am I doing wrong.Thanks.
SELECT t1.fname, t1.lname, t2.district_name, t2.district_number FROM t1 AS table1, t2 AS table2 WHERE t2.district_name LIKE 'S%'
i have these values in the table
table1
fname lname mike jackson roy mires
table2
district_name district_number South 123 Daggerty 7845 duffel 7224 rubble 7545
Now the query is dynamic (the letter is that the like clause is run against)
When the letter D is searched for i get the 2 colums duffel, daggerty BUT when S is searched against I get nothing..
I am confused as to why, It doesn't seem to be case sensitive, as the 2 colums duffel and Daggerty 1 is d is in lowercase and the other is in uppercase. andive tries both lowercase s and uppercase S and still got nothing.
Is tehre a better way to use the LIKE clause? Ive looked and looked for documentation about the LIKE clause but I cannot find anything
How can I get all names that start with "sci" or "eng" without using the OR clause. In other words, how can I modify the following statement so I don't use the OR clause: select * from course where (name like 'sci%' or name like 'eng%')
Does anyone know how to count rows of data from 1 table that meet one or two different criteria and then get the probability of occurance for that criteria ... as an output column? Do I use 'Having' or temp tables or Views? Here is my output for now. I am trying to solve for 'Prob' - everything else works on it's own.Craig
SELECT Wins_Lng/Trades_Lng as Prob FROM Transactions SELECT Count(Ticker) as Trades_Lng FROM Transactions WHERE TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND TransKind='B' (SELECT COUNT(Ticker)as Wins_Lng FROM TransactionsWHERE Transkind='B' AND TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND Profit_Lng>=0)
I want to export an SQL Server table to an Excel Spreadsheet driven by a web interface. I am using Cold Fusion to call a SQL Server Stored procedure. The SP accepts a variable (IDlist) from the web page and sets this to a Global Variable.
The SP then executes a DTS package to export to Excel. The DTS package uses the Global variable in the SQL Query thus:
SELECT ... FROM ... WHERE tblPropertyRegister.IDProperty IN (?);
This works fine when I pass one single ID (@outIDlist = "20") into the stored procedure. But it returns no records when I pass multiple IDs (@outIDlist = "19, 20, 21") into the stored procedure. It works fine also if I "hard code" the IDlist into the DTS query (eg WHERE tblPropertyRegister.IDProperty IN (19, 20, 21);). The problem appears to be in the setting of the global variable in the stored procedure.
Has anyone had any experience with this? Any feed back would be greatly appreciated. TIA
sSQL = "SELECT VIN, Year, MakeID AS 'Make', ModelID AS 'Model', Name AS 'Dealer', PhoneOne AS 'Phone', StockDate AS 'Stock Date', SoldDate AS 'Sold Date', RepairCost AS 'Repair Cost' FROM " sSQL = sSQL & sView sSQL = sSQL & " WHERE VIN = '" & sVIN & "'"
The error I got is invalid column 'MakeID' and invalid column 'ModelID'. I'm not familiar with the term "AS" in SQL so can anyone explain what's the problem here?
I've put together the following query, but it has been unsuccessful running it so far.
Code:
select * from Contractors.dbo.Contacts where VendorNo in (select No_ from [BMIS Live Database].dbo.[BMIS Live Database$Vendor] where [Name] like '%of%')
I receive the following error when I run it in Query Analyzer:
Code:
Server: Msg 446, Level 16, State 9, Line 1 Cannot resolve collation conflict for equal to operation.
I'm trying to grab values from the column, "No_" in the Bmis.. database then only select data from the Contractors database if the VendorNo column holds one of the values grabbed from the Bmis.. database. I think my syntax is just completely wrong but hopefully someone might pick up on my mistakes. Any help would be greatly appreciated, thanks!
This is my queryselect ano,max(date),a_subject from MY_TAB where table_name='xyz' andano=877group by a_subject,ano order by a_subjectANOmax(Date)A_Subject8772005-01-20 00:00:00.000Subject_18771900-01-01 00:00:00.000Subject_28772004-12-20 00:00:00.000Subject_38772005-01-19 00:00:00.000Subject_4--------------------------------------------------------------------------When I put the status column in, it fetches all the rows.select ano,max(date),a_subject,status from MY_TAB wheretable_name='xyz' and ano=877 group by a_subject,ano,status order bya_subjectANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8771900-01-01 00:00:00.000Subject_3Not Started8771900-01-01 00:00:00.000Subject_4Not Started8772005-01-19 00:00:00.000Subject_4Not Started-----------------------------------------------------------------------now what i want isANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8772005-01-19 00:00:00.000Subject_4Not StartedThanks a lot for your help.AJ
OK ... I am using UPS Worldship that issues an ODBC query to my MS2Kserver ... Worldship can query either a table or a view and retreiveshipping info for a supplied orderid.I need to create a DB table that will track the orderids requestedfrom Worldship so that I can stop doubleships. That is to set up afunction to allow the info to be sent only once to worldship.I need to execute a stored procedure to write to a table and enforcebiz logic.So .. I've created a view that Worldship can execute an ODBC queryagainst (v_upsPull) ... in which I guess the query issued will belike: SELECT * FROM v_upsPull WHERE orderid = 123456The view is:CREATE VIEW dbo.v_upsPullASSELECT * FROM OPENROWSET ( 'SQLOLEDB', '[db]'; '[user]'; '[password]','exec sp_ups_pull')When the ODBC query calls the view the sp_ups_pull store procedurer isexecuted.However ... I do not have access to the original Where clause in theODBC query in the stored procedurer.Is there a way I can get access to the ODBC Where clause and pass itinto the stored procedurer?If not is there some other way I can create a DB table and run aselect against it ... based on the Worldship query?
HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks
Table:ColumnsUsersList:UserID, UserName, Country I need a query which select all the rows from the above mentioned table with all fieldsButThe order the rows is First all the users from "Pakistan"Second all the users from rest of the countries except "Pakistan" in ascending order So the query first return all the users from Pakistan and the the users from rest of the world in ascending order. Forexample, 1, ABC, USA2, XYZ, Saudi Arabia3, LMN, Pakistan4, TQR, India5, PTR, Afghanistan then the query returns. 3, LMN, Pakistan5, PTR, Afghanistan4, TQR, India2, XYZ, Saudi Arabia1, ABC, USA
Hello, I have to update a query that was given to me so that it displays only items that were created in a certain month and certain year (which I prompt the user for). I hard-coded a month and year to test (2008 year, 2 month). My results are including other months I'm not asking for. I have marked my problems areas with /* Problem 1 */ and /* Problem 2 */ (same where clause n 2 locations).|What am I doing wrong? Doesn't matter what year or month I enter, I am always getting extra data1st 3 columns of results (I removed the calculated fields) 2008 1 Incoming2008 1 Both2008 2 Outgoing2008 2 Incoming2008 2 BothI can't for the life of me figure out how to fix this up. query below... ================================ DECLARE @TheYear integerDECLARE @TheMonth integer SET @TheYear = 2008SET @TheMonth = 2 SELECT * FROM ( SELECT year(startime) as yearstart, month(startime) as monthstart, directioncodename , count(CASE WHEN new_issuecategoryname is null THEN activitycountvalue END ) as nullcall , count(CASE WHEN new_issuecategoryname='ACDelco Comment' THEN activitycountvalue END ) as acdelcocommentcall , count(CASE WHEN new_issuecategoryname='ACDelco General Inquiry' THEN activitycountvalue END ) as acdgeneralinquirycall , count(CASE WHEN new_issuecategoryname='ACDelco PPD'
THEN activitycountvalue END ) as acdppdcall , count(CASE WHEN new_issuecategoryname='ACDelco Promotion'
THEN activitycountvalue END ) as acdpromotioncall , count(CASE WHEN new_issuecategoryname='ACDelco Promotion - Calendar'
THEN activitycountvalue END ) as acdpromotioncalendarcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Program Enquiry'
THEN activitycountvalue END ) as tssprogramenquirycall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Promotion'
THEN activitycountvalue END ) as tsspromotioncall , count(CASE WHEN new_issuecategoryname='ACDelco Training'
THEN activitycountvalue END ) as acdtrainingcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Benefits'
THEN activitycountvalue END ) as tssbenefitscall , count(CASE WHEN new_issuecategoryname='ACDelco Other'
THEN activitycountvalue END ) as acdothercall , count(CASE WHEN new_issuecategoryname='ACDelco TAC Number'
THEN activitycountvalue END ) as acdtacnumbercall , count(CASE WHEN new_issuecategoryname='ACDelco PPD Promotion'
THEN activitycountvalue END ) as acdppdpromotioncall , count(CASE WHEN new_issuecategoryname='RealRewards - ISC'
THEN activitycountvalue END ) as realrewardsisccall , count(CASE WHEN new_issuecategoryname='RealRewards - CounterPerson'
THEN activitycountvalue END ) as realrewardscounterpersonscall , count(CASE WHEN new_issuecategoryname='ACDelco Event'
THEN activitycountvalue END ) as acdelcoeventcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Lead'
THEN activitycountvalue END ) as acdtssleadcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Other' THEN activitycountvalue END ) as acdtssothercall , count(CASE WHEN new_issuecategoryname is not null and new_issuecategoryname!='ACDelco Comment' and new_issuecategoryname!='ACDelco General Inquiry' and new_issuecategoryname!='ACDelco PPD' and new_issuecategoryname!='ACDelco Promotion' and new_issuecategoryname!='ACDelco Promotion - Calendar' and new_issuecategoryname!='ACDelco TSS Program Enquiry' and new_issuecategoryname!='ACDelco TSS Promotion' and new_issuecategoryname!='ACDelco Training' and new_issuecategoryname!='ACDelco TSS Benefits' and new_issuecategoryname!='ACDelco Other' and new_issuecategoryname!='ACDelco TAC Number' and new_issuecategoryname!='ACDelco PPD Promotion' and new_issuecategoryname!='RealRewards - ISC' and new_issuecategoryname!='RealRewards - CounterPerson' and new_issuecategoryname!='ACDelco Event' and new_issuecategoryname!='ACDelco TSS Lead' and new_issuecategoryname!='ACDelco TSS Other' THEN activitycountvalue END ) as othercall ,count(activitycountvalue) as totalcall
FROM ( select startime = CASE WHEN filteredphonecall.new_cmgstartdatetime is not null THEN filteredphonecall.new_cmgstartdatetime WHEN filteredphonecall.actualstart is not null and filteredphonecall.new_cmgstartdatetime is null THEN filteredphonecall.actualstart ELSE filteredphonecall.createdon END , 1 as activitycountvalue , new_issuecategoryname , new_issuecategory , 'phone call' as activitytypecodename , filteredphonecall.new_languagename , filteredphonecall.directioncodename from /* PROBLEM 1 */ filteredphonecall WHERE ( ( filteredphonecall.new_cmgstartdatetime is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth) ) OR ( filteredphonecall.actualstart is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth) ) OR ( filteredphonecall.actualstart is not null AND (Year(filteredphonecall.actualstart) = @TheYear) AND (Month(filteredphonecall.actualstart) = @TheMonth) ) OR ( filteredphonecall.createdon is not null AND (Year(filteredphonecall.createdon) = @TheYear) AND (Month(filteredphonecall.createdon) = @TheMonth) ) ) )as phoneactivities GROUP BY year(startime), month(startime), directioncodename
UNION ALL
/* KATHY1 */ SELECT year(startime) as yearstart , month(startime) as monthstart ,'Both' as directioncodename , count(CASE WHEN new_issuecategoryname is null THEN activitycountvalue END ) as nullcall , count(CASE WHEN new_issuecategoryname='ACDelco Comment' THEN activitycountvalue END ) as acdelcocommentcall , count(CASE WHEN new_issuecategoryname='ACDelco General Inquiry'
THEN activitycountvalue END ) as acdgeneralinquirycall , count(CASE WHEN new_issuecategoryname='ACDelco PPD'
THEN activitycountvalue END ) as acdppdcall , count(CASE WHEN new_issuecategoryname='ACDelco Promotion'
THEN activitycountvalue END ) as acdpromotioncall , count(CASE WHEN new_issuecategoryname='ACDelco Promotion - Calendar'
THEN activitycountvalue END ) as acdpromotioncalendarcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Program Enquiry'
THEN activitycountvalue END ) as tssprogramenquirycall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Promotion'
THEN activitycountvalue END ) as tsspromotioncall , count(CASE WHEN new_issuecategoryname='ACDelco Training'
THEN activitycountvalue END ) as acdtrainingcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Benefits'
THEN activitycountvalue END ) as tssbenefitscall , count(CASE WHEN new_issuecategoryname='ACDelco Other'
THEN activitycountvalue END ) as acdothercall , count(CASE WHEN new_issuecategoryname='ACDelco TAC Number'
THEN activitycountvalue END ) as acdtacnumbercall , count(CASE WHEN new_issuecategoryname='ACDelco PPD Promotion'
THEN activitycountvalue END ) as acdppdpromotioncall , count(CASE WHEN new_issuecategoryname='RealRewards - ISC'
THEN activitycountvalue END ) as realrewardsisccall , count(CASE WHEN new_issuecategoryname='RealRewards - CounterPerson'
THEN activitycountvalue END ) as realrewardscounterpersonscall , count(CASE WHEN new_issuecategoryname='ACDelco Event'
THEN activitycountvalue END ) as acdelcoeventcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Lead'
THEN activitycountvalue END ) as acdtssleadcall , count(CASE WHEN new_issuecategoryname='ACDelco TSS Other' THEN activitycountvalue END ) as acdtssothercall , count(CASE WHEN new_issuecategoryname is not null and new_issuecategoryname!='ACDelco Comment' and new_issuecategoryname!='ACDelco General Inquiry' and new_issuecategoryname!='ACDelco PPD' and new_issuecategoryname!='ACDelco Promotion' and new_issuecategoryname!='ACDelco Promotion - Calendar' and new_issuecategoryname!='ACDelco TSS Program Enquiry' and new_issuecategoryname!='ACDelco TSS Promotion' and new_issuecategoryname!='ACDelco Training' and new_issuecategoryname!='ACDelco TSS Benefits' and new_issuecategoryname!='ACDelco Other' and new_issuecategoryname!='ACDelco TAC Number' and new_issuecategoryname!='ACDelco PPD Promotion' and new_issuecategoryname!='RealRewards - ISC' and new_issuecategoryname!='RealRewards - CounterPerson' and new_issuecategoryname!='ACDelco Event' and new_issuecategoryname!='ACDelco TSS Lead' and new_issuecategoryname!='ACDelco TSS Other' THEN activitycountvalue END ) as othercall ,count(activitycountvalue) as totalcall
FROM ( select startime = CASE WHEN filteredphonecall.new_cmgstartdatetime is not null THEN filteredphonecall.new_cmgstartdatetime WHEN filteredphonecall.actualstart is not null and filteredphonecall.new_cmgstartdatetime is null THEN filteredphonecall.actualstart ELSE filteredphonecall.createdon END , 1 as activitycountvalue , new_issuecategoryname , new_issuecategory , 'phone call' as activitytypecodename , filteredphonecall.new_languagename , filteredphonecall.directioncodename from filteredphonecall /* PROBLEM 2 */ filteredphonecall WHERE ( ( filteredphonecall.new_cmgstartdatetime is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth) ) OR ( filteredphonecall.actualstart is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth) ) OR ( filteredphonecall.actualstart is not null AND (Year(filteredphonecall.actualstart) = @TheYear) AND (Month(filteredphonecall.actualstart) = @TheMonth) ) OR ( filteredphonecall.createdon is not null AND (Year(filteredphonecall.createdon) = @TheYear) AND (Month(filteredphonecall.createdon) = @TheMonth) ) ) )as phoneactivities GROUP BY year(startime), month(startime) ) as orderedresults order by yearstart, monthstart, directioncodename DESC;
I am developing Staff Allocation System, database is sql server 2000. I have problem in retrieve the staff informations,
employee working which Project and what project have assign to him, what is his assign project or contract no,
One employee working more then one project, retrieve information one employee how many projects are working,
What is his approved position, what is his assign position. It the main data have to retrieve, as well as retrieve all fields which related to those tables.
I use this query.
select name,apppos approved_position,appcont approved_contract,appdate employee_appr_date,munref Municipality_Ref,dcilref DCIL_REF,projtype Project_Type,strdate Project_str_date,comdate Projcet_comp_date,extdate Proejct_ext_date,dept,emptype Employee_Type from contract,emp,apprecords where contract.rec_id=emp.rec_id and emp.rec_id=apprecords.rec_id and apprecords.name='dewachi'
above query retrieve no data,
how can use group by clause in the above query ?
group by apprecords.appcontract
group by clause give error.
above query have to retrieve data from the three tables, I have four tables, what query I use so that all four tables data retrieve like this.
Name, approved_position, approved_contract,assign_position,assign_contract,startdate,completion_date,........ and so on… Group by apprecords.appposition ……….
Employee Table (basic data entry employee table) --------------------------------------------------------- rec_id EmpNo Name Position Department EmployeeType
Approved Records Table (in this table all information about the employee and his approved position and contract ) ------------------------------------------------------------------------ rec_id Name Approved Date MunicipalityRefNo DCILRefNo ApprovedPosition ApprovedContract
Assign Project Table (in this table all information about the employee his assign the project) -------------------------------------------------------------------- rec_id Name AssignPosition AssignContract EmpProjectStartDate EmpProjectEndDate ShiftNo ProjectStatus
I'm trying to list salesreps (if they have any sales for a particular date) with their total sales amounts for a queried date, but when running this sql string in QueryAnalyzer, it says there is an error with syntax on Line 1 near "s" :SELECT o .Rep_ID, o .ID, s.ID, SUM(b.orderamount) AS totalsales, b.order_ID FROM (SELECT b.Deal_ID FROM btransactions b WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN orders o ON o .Rep_ID = s.ID INNER JOIN b ON o.ID = b.Deal_ID GROUP BY d .Rep_ID, d .ID, s.ID, b.order_ID HAVING (SUM(b.orderamount) > 0)???.NetSports
Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty FROM T1 t1 WHERE MaxQty>=10
But it fails as computed or inline query columns can not be added in where clause.
However following works: Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty FROM T1 t1 WHERE (Select Max(Qty) From T2 where ID=t1.ID) >=10
BUT IS IT OPTIMIZED? Please suggest an optimized way to handle such scenarios.
This can be achieved with inner join, no problem. Pretty simple.
However, there's a catch =)
DocParty.Role can have four different values in the 'where' clause. Is there a way to fetch all of these four values without returning four duplicates with only one field differing?
There are multiple fields in the query that are to be fetched in similar ways. Therefore, using a IN('value1','value2','value3','value4') would increase the number of selected rows a lot.
In addition, there is another type of condition that needs to be fullfilled.
Basically, there two fields in the 'main' table that are joined to the same field in another table with different conditions. Can this be fetched with the same row as all the other data without duplicates?
Should I use a view somehow? How can I construct a view with these complex conditions if I can't construct an SQL query, that would return no duplicates (pseudo-du
USING A WHERE CLAUSE and Alias ------------------------- select a.emp_name from dbo.dba a, dbo.project b where a.empid =b.project_emp and b.project_name is not null
USING A JOIN ----------------- select emp_name from dbo.dba as a inner JOIN dbo.project ON empid = dbo.project.project_emp AND dbo.project.project_name is not NULL
******
I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all ) :shocked:
Any comments/ suggestions.
Thanks,
Have a great time -Ranjit.
------------------------------------- It pays to be honest to your DBA
I could write a query with a sub-query in order to perform an UPDATE on the most recent 60,000 records of a table based on a date field, but unfortunately I am receiving an error.
Code: SELECT * FROM DMTM SET transmit_date = '2012-05-07 00:00:00.000', transmit_status = '1223' WHERE temp_pk in
I have the following query where I select records from Active_Activities_temp which do not match on cde_actv in the table ACTIVITY_CORE_LISTING: SELECT Active_Activities_temp.* FROM Active_Activities_temp LEFT JOIN ACTIVITY_CORE_LISTING ON Active_Activities_temp.cde_actv=ACTIVITY_CORE_LISTING.cde_actv WHEREACTIVITY_CORE_LISTING.cde_actv is null ORDER BY prtcpnt_id So for example, if a participant has a cde_actv=38 (which doesn't exist in ACTIVITY_CORE_LISTING), that record would appear as the query is currently.
The issue is that participants can have multiple records in Active_Activities_temp and if a participant has a record that does exist in ACTIVITY_CORE_LISTING, no records for that participant should appear in this query result. For example, if a participant has two records in Active_Activities_temp, one with a cde_actv 38 (which does not appear in ACTIVITY_CORE_LISTING) and one with a cde_actv 33 (which does appear in ACTIVITY_CORE_LISTING), no records for that participant should appear in the result. Currently the record with cde_actv=38 does appear.
What code can I implement to do what I need to do? Thanks so much.
Hello! suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like DEPT ABSENT ----------------------------- Accounts 10 EDP Section 0 ** Admin 2 Stationary 0**
if no employee is absent in the department it has to display Zero
Hi all,Just wondering if anyone can tell me if an order by clause on a selectquery would have any impact on the time it takes to retrieve results?Essentially I'm selecting Top 1 out of a table via various criteriaand currently getting it back without an order by clause. The order bywould only include the column that has the clustered primary index onit.Can anyone tell me if in theory this will slow the query down?Many thanks in advance!Much warmth,Murrau
I'm collecting id based on user's selection and storing to ArrayList/String[in asp.net]. Now want to pass those ids stored to stroed procedure. I want SP to update all records in single statement using in like update table set field in (value).
I'm storing id with comma (,) separator. Below is my asp.net code:
string sId=string.Empty;
for(int a=0;a<Id.Count;a++)
{
Id += Id;
if(a!=Id.Count-1)
sId +=",";
}
In database id column is int type. I'm passing id collected with comma separator and passing it as a parameter to SP. but it is giving error related converting to int or similar.
Plz help me create Stored Procedure in SQL 2005 based on above scenario.
