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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Trying To Write A Select Statement.
I want to write a select statement that will show only distinct Student IDs where every record in the table with that student id has a grade above 80? Lets say for example the table looks like this: Student_ID Grade 11276 93 56879 81 11276 76 44327 92 11276 84 56879 97 56879 82 The select statement should only show students, 56879 & 44327 because student 11276 made a 76 in one class.Anyone know how to word this select statement?Thanks for any help!
View Replies !
How To Write The Select Statement?
I create a table that is named T1(C1,C2) and insert some data,as following: C1 C2 1 1 4 NULL NULL 5 7 NULL NULL 9 12 12 14 NULL NULL 16 I want to write a select statement to obtain the following result: C1 C2 1 1 4 5 7 9 12 12 14 16 how to write the select statement? 　
View Replies !
Is It Possible To Write If/Then Else Within SQL Select Statement For Vb.net?
I'm running a select statement from within my VB.net code. However, if the software package name that I chose from a drop down list in my form is not available in the search (in the TableColumn called Software.Package_Name), I would like to be able to write the message "Record Not Found" to the Package_Name "ListBox" column. Here's the select statement: "SELECT n.Workstation_Name , s.install_status , s.install_date , s.name , s.version , s.build , s.Package_Name , n.city , n.state FROM software s , network n where ( s.mac=n.mac AND n.Workstation_Name='B001617CA709E' ) AND ( s.name='Adobe Reader' AND s.Install_status='1') ORDER BY n.Workstation_Name ASC" My ListBox control is named lstQueryResults and uses a Structure that is one to one with each row of the lstQueryResults control. i.e. Public Class LIST_CTRL_DATA Public WksName As String Public InstallStatus As String Public InstallDate As String Public PackageName As String Public AppName As String Public Version As String Public Build As String Public City As String Public State As String Public Percent As UInteger Finally, here's the While..End routine that plugs the data into the ListBox called lstQueryResults. While sqlReader.Read() currec += 1 Dim msg As THREAD_MESSAGE msg.msgid = 101 Dim message As New LIST_CTRL_DATA message.WksName = sqlReader("Workstation_Name").ToString message.InstallStatus = sqlReader("Install_Status").ToString message.InstallDate = sqlReader("Install_Date").ToString message.AppName = sqlReader("Name").ToString message.Version = sqlReader("Version").ToString message.Build = sqlReader("Build").ToString message.PackageName = sqlReader("Package_Name").ToString message.City = sqlReader("City").ToString message.State = sqlReader("State").ToString message.Percent = (currec * 100) / maxrecs msg.msgdata = message m_ParentWindow.Invoke(m_NotifyMainWindow, msg) End While Any ideas on whether or not you can write an If..Then Else statement within the SQL Select Statement and if so how? I'm still somewhat new to SQL. Any assistance would be great. Regards, NBK
View Replies !
Using Select Statement Instead Of Cursor
Hi All, Can anyone please help? TableA has data as below: ssn sex dob rel_code 111111111 m 19500403 m 111111111 f 19570908 w 111111111 f 19770804 d 111111111 f 19801203 d 111111111 f 19869712 d 111111111 m 19870907 s 111111111 m 19901211 s I have to convert the rel_code into a specific manner so the data will look as below in TableB: ssn sex dob rel_code 111111111 m 19500403 01 111111111 f 19570908 02 111111111 f 19770804 20 111111111 f 19801203 21 111111111 f 19869712 22 111111111 m 19870907 30 111111111 m 19901211 31 Member's rel_code = 01 spouse's rel_code = 02 daughter's rel_code starts from 20 with the oldest and increments by 1. Son's rel_code starts from 30 and increments by 1 from oldest to the youngest. I know You can write a Sp with cursor and do this, but I would like to know if you can accomplish the same thing by a select or case or something else instead of a cursor. Thanks in advance. Jannat.
View Replies !
Select Statement In Cursor
Hi... I have a stored procedure that rertrieves data from an sql database and sends out a mail to each receipient who meets the criteria I am using SQL mail. I dynamically generate the where clause for my sql query based on criteria taken from other stored procedures and store it in a varchar variable called @sqlquery When i have the following code to run my cursor DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary. What should i do. i have tested the variable @sqlquery and it is definately not blank. There is no bracket error or anything. Please help!!! Thanks much indeed Ramesh
View Replies !
Select Statement In Cursor...Please Help
Sorry to disturb you guys but I have a problem on the select statement in sql cursor My select statement is stored in 2 variables one holds the select clause and the other holds the where clause I am doing a small test as my seelct statement is very complicated lots of joins and it is built up from lots of parameters from other queries and from another stored procedure as well Hope you can help when i type the following code: declare @query varchar(100) declare @query2 varchar(100) set @query = "SELECT FROM ml_testMaillist " set @query2 = " WHERE m_Email= 'ramesh@go-events.com' " DECLARE overdue2 CURSOR LOCAL FORWARD_ONLY exec(@query + @query2) open overdue2 I get the error Server: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'exec'. Please please help as this is very impt to me Thanks Thanks Regards
View Replies !
How To Specify Which Database To Use For A Select Statement Within A Cursor?
Hi everyone, I have been trying to perform the following task: Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns: Database name Table name Column name from that table with a clustered index I have already created the following script which displays all the databases for a given server: declare @DBname nvarchar(128) declare testCursorForDB cursor for select name from sys.databases with (nolock) where name not in ('master','tempdb','model','msdb') order by name open testCursorForDB fetch next from testCursorForDB into @DBname while @@fetch_status = 0 begin print @DBname fetch next from testCursorForDB into @DBname end close testCursorForDB deallocate testCursorForDB I also have created the following query which will display all the table and column names which have a clustered index for a given database: select object_name(i.id) as TableName, i.name as IndexName from sys.sysindexes as i with (nolock) where i.indid = '1' However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor. Any help is greatly appreciated Thanks!
View Replies !
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile... Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69 Incorrect syntax near the keyword 'ORDER'. However the select statement itself runs perfectly well as a query, no errors. The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs. What gives with this? Thanks in advance R. The code: Code Snippet -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF object_id('InsertImportedReportData ') IS NOT NULL DROP PROCEDURE InsertImportedReportData GO -- ============================================= -- Author: ----- -- Create date: -- Description: inserts imported records, marking as duplicates if possible -- ============================================= CREATE PROCEDURE InsertImportedReportData -- Add the parameters for the stored procedure here @importedReportID int, @authCode varchar(12) AS BEGIN DECLARE @errmsg VARCHAR(80); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --IF (@authCode <> 'TX-TEC') --BEGIN -- SET @errmsg = 'Unsupported reporting format:' + @authCode -- RAISERROR(@errmsg, 11, 1); --END DECLARE srcRecsCursor CURSOR LOCAL FOR (SELECT ImportedRecordID ,ImportedReportID ,AuthorityCode ,[ID] ,[Field1] AS RecordType ,[Field2] AS FormType ,[Field3] AS ItemID ,[Field4] AS EntityCode ,[Field5] AS LastName ,[Field6] AS FirstMiddleNames ,[Field7] AS Title ,[Field8] AS Suffix ,[Field9] AS AddressLine1 ,[Field10] AS AddressLine2 ,[Field11] AS City ,[Field12] AS [State] ,[Field13] AS ZipFull ,[Field14] AS OutOfStatePAC ,[Field15] AS FecID ,[Field16] AS Date ,[Field17] AS Amount ,[Field18] AS [Description] ,[Field19] AS Employer ,[Field20] AS Occupation ,[Field21] AS AttorneyJob ,[Field22] AS SpouseEmployer ,[Field23] As ChildParentEmployer1 ,[Field24] AS ChildParentEmployer2 ,[Field25] AS InKindTravel ,[Field26] AS TravellerLastName ,[Field27] AS TravellerFirstMiddleNames ,[Field28] AS TravellerTitle ,[Field29] AS TravellerSuffix ,[Field30] AS TravelMode ,[Field31] As DptCity ,[Field32] AS DptDate ,[Field33] AS ArvCity ,[Field34] AS ArvDate ,[Field35] AS TravelPurpose ,[Field36] AS TravelRecordBackReference FROM ImportedNativeRecords WHERE ImportedReportID IS NOT NULL AND ReportType IN ('RCPT','PLDG') ORDER BY ImportedRecordID -- this should work but gives syntax error! ); END
View Replies !
How To Write This Condition?
Hi, I am trying to set up a conditional split. As usual, my books do not explain what I am trying to do :-) flat file source --> conditional split (if Column0 contains a certain string send to error file) In the Conditional Split Transformation Editor I have the following for "condition": FINDSTRING( "my search string", [Column0] ,1 ) TITLE: Microsoft Visual Studio ------------------------------ Error at myPkg [Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" is not Boolean. The result type of the expression must be Boolean. Error at myPkg[Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" on "output "Case 1" (8351)" is not valid. Error at myPkg [Conditional Split [8259]]: Failed to set property "Expression" on "output "Case 1" (8351)". As you can see I want it to search Column0. Can someone please point out my error. Thanks
View Replies !
How To Write Trigger Based On Certain Condition
One of my table called as 'customertable' contains following fields customername, emailid, subscriptionendperiod Example records are: david, david@john.com, 12/20/2005(mm/dd/yyyy format). My question is that: Just one month before subscriptionendperiod that is on 11/20/2005(mm/dd/yyyy) an automatic email should go to david@john.com with the message 'Your subscription period ends on 12/20/2005' How to write trigger for this. Please Note : No ASP code is invloved in this. Only MSSQL coding to be done. Regards
View Replies !
How To Write Condition In The Conditional Split Control In SSIS
HI i need to write the Condition for Insert and Update Reccord depending upon the Prod_ID. How to write the Follwing condtion in the Condition Split? pls Anyone give me the Solution? " if Prod_ID Exist then UPDATE Records if Prod_ID Not EXIST then INSERT Records " how to write the above conditon in the Condional Split? Thanks & Regards, Jeyakumar.M chennai
View Replies !
How To Write SQL Query And Not Cursor ?
Please help me to get the required result: For each IDS in table1 - change the ids to numbers (eg. for '1,2,3' get the numbers (IntValue) 1, 2 & 3) - in table2, find the maxVal for each number - disply the table1..ids, number, table2..maxVal & table1..idsDesc, order by table1..ids, table2..maxVal & IntValue I have 2 tables, over milin records each. The Simplified versions of the tables looks like that: create table table1 (ids varchar(100), idsDesc varchar(100)) go insert table1 select '1,2,3', 'Description 1' union all select '2,3,4', 'Description 2' union all select '1,7', 'Description 3' union all select '16,3,8', 'Description 4' union all select '2,5,6,1', 'Description 5' go create table table2 (ids int, maxVal int) go insert table2 select 1, 10 union all select 2, 6 union all select 3, 12 union all select 4, 11 union all select 5, 66 union all select 6, 4 union all select 7, 3 -- union all select 8, 5 -- no value for 8 union all select 9, 6 union all select 16, 12 go I have also function that returns table variable of numbers delivered from given string: create function dbo.fn_StrToIntValues ( @str varchar(1000) ) returns @numsTbl table (IntValue int not null) The command select * from dbo.fn_StrToIntValues('1,2,33') Returns --> intValue 1 2 33 Can I use SQL query and not cursor to get the following result ? Required Output : ids IntValue maxVal idsDesc --------- -------- ------- --------------- '2,5,6,1' 5 66 'Description 5' '16,3,8' 16 12 'Description 4' '2,3,4,9' 3 12 'Description 2' '1,2,3' 3 12 'Description 1' '1,7' 1 10 'Description 3'
View Replies !
How To Write Set-based SQL Instead Of Cursor
Guys Here's the scenario create table data1 (dealid varchar(6) , datex smalldatetime , Tn INT) insert data1 (dealid , datex , Tn ) values ('12345' , '31-12-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('12345' , '30-11-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('12345' , '31-10-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('98765' , '31-12-2005' , 2) insert data1 (dealid , datex , Tn ) values ('98765' , '30-11-2005' , 1) insert data1 (dealid , datex , Tn ) values ('98765' , '30-11-2005' , 0) select * from data1 I need to update the Tn column from the default 9999 for the 3 rows in this table where the dealid is 12345 based on the value in the datex column so the row with the 'highest ie most recent date' gets a 0. I then need to assign the value 1 to the next highest and so on until all rows (in this case 3) get incrementing integer values. It's easy with a cursor but can't get my head round doing it in a set-based way Any ideas
View Replies !
Does Cursor Convert Table To Read/write?
Hello, Any help here much appreciated. I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write. i suspect this is when i run a cursor through the table. Is anyone able to confirm for me whether running a cursor changes a table's properties? Many thanks. Tim Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.
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 !
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 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 Replies !
SELECT UNION SELECT Condition
let say i got such condition INSERT INTO TABLE SELECT WHERE XX NOT EXISTS (SELECT 1 FROM TABLE) UNION SELECT WHERE XX NOT EXISTS (SELECT 1 FROM TABLE) do you think that mssql will produce error or problem? from what i heard it will.
View Replies !
Select Condition From Log Table
Hi guys Im not even sure how to word this right, but how do I compare one element(unsure word number 1) namely Filename, from a list of results, and use that value as a variable for another condition in the same select... I think?! So if I get a list of 50 filenames (from a log table), some might be double. If the file was successfully processed, it will log that filename and the status as OK and if not then BAD. The file will be modified and then reprocessed until OK. Each time the file was processed, it will log the filename and status. I then run a statement that shows all the files that were BAD (from the log table) and it will bring up then filename and BAD status. What I want to do is check the status' and if the file is BAD, check if there is another logged entry with that filename and status is OK, if found then not produce output as Im only looking for the files that status' are BAD with no OK status for any of the logged entries for that filename. Is that understandable? If not then Ill try reword it. The help is much appreciated! Justin
View Replies !
Condition In Select Query
create function stzipcustinfo ( @campaign varchar(50), @startdate datetime, @enddate datetime,@audience_type char(10) ) RETURNS TABLE AS RETURN (Select distinct t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname, t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone,sum(t.totalquantity) as Totalquantity, t.Audience_type from ( select distinct v.sopnumbe, v.campaign, v.custnmbr, v.custname, v.address1, v.address2,v.city,v.state,v.country,v.zipcode, v.Phone, v.totalquantity as totalquantity, case @audience_type when v.custclas then v.custclas end as audience_type from vwstzipcustaudienceinfo v Where (v.itemnmbr = '' or v.itemnmbr=v.itemnmbr) and v.Campaign = @Campaign and (v.docdate between @startdate and @enddate) ) as t where t.audience_type is not null group by t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname, t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone, t.Audience_type ) --select * from mystatezipcustaudienceinfo('cpd','1/1/2008','5/15/2008','INDPATIENT') --getting 500rows --select * from mystatezipcustaudienceinfo('Cpd'','1/1/2008','5/15/2008','INST-HOSPITAL') -- note getting single row problem have a “-“ in them..i m not getting result..so what condition should be in red color one.. thanks
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
T-SQL: Different SELECT Executed Depending On Condition.
I'm trying to execute a different SELECT statement depdning on a certain condition (my codes below). However, Query Analyzer complains that 'Table #Endresult already exists in the database', even though only one of those statements would be executed depending on the condition. Any ideas as to a work around? I need the result in an end temporary table. IF @ShiftPeriod = 'Day' SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkDays = 1 ELSE IF @ShiftPeriod = 'Night' SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkNights = 1 ELSE IF @ShiftPeriod = 'Evenings' SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkEvenings = 1 ELSE SELECT * INTO #EndResult FROM #NursesAvailable
View Replies !
If Condition Within Select Query Sql Server 2000
Hi all, I have to write a select query which need some logic to be implemented. Query is like select name,number,active, if active ="true" then select @days=days from tbl_shdsheet else @days='' end from tbl_emp In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet So how can i write queery for this.
View Replies !
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 !
How To Write SQL Statement
I wanted to select few columns from the result of calling this store procedure and sort by a column. {CALL FIQR.Portfolio.Loans_In_Portfolio('2007-09-07', 'FISYND', '', '%')} How should I write it?(without changing the store procedure)
View Replies !
How Do I Write This SQL Statement?
I have a table named "products", it has a column called "category", I have data such as "CategoryA", "CategoryB" and "CategoryC" etc.... now I want to do a select statement and choose "CategoryA" and "CategoryB" but I want to rename them as "A" and "B". how do I write such a statement... thanks!
View Replies !
Help.. How To Write This Sql Statement?
Hi all, DECLARE @STA as char(50) SET @STA = 'TT' SELECT * FROM ABC_TBL where ABC_Filed in (@STA) if i have multivalue in parameter @STA, like 'TT', 'DD', 'AA' so how can i write my statement? i only need to pass one parameter with multilist, so do anybody know how to write the MS sql statement? Example like DECLARE @STA as char(50) SET @STA = 'TT', 'DD' SELECT * FROM ABC_TBL where ABC_Filed in (@STA) Thank You. Regards, S3ng
View Replies !
HELP With SQL Query: Select Multiple Values From One Column Based On &&<= Condition.
Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation: I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner: (AISC_Shapes_Table) AISC_MANUAL_LABEL W W44x300 300 W42x200 200 (and so on) WT22x150 150 WT21x100 100 (and so on) MT12.5x12.4 12.4 MT12x10 10 (etc.) I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is: Code SnippetSELECT AISC_MANUAL_LABEL, W FROM AISC_Shape_Table WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%')) It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance. Regards, Steve G.
View Replies !
Is It Possible Write In 1 Query Statement?
Hi guys I'll appreciate any help with the following problem: Table 1: TBSALESHEADER PK: SALESNO Table 2: TBSALESENTRY FK: SALESNO This query is return 2 rows. SELECT PAYMTDID AS TYPE, CONVERT(CHAR(8),SALESDATE,112) AS DATE, SUM(SALESAMT) AS AMOUNT FROM TBSALESHEADER A, TBSALESENTRY B WHERE A.SALESNO = B.SALESNO AND CONVERT(CHAR(8),SALESDATE,112) = '20070701' GROUP BY PAYMTDID,CONVERT(CHAR(8),SALESDATE,112) TYPE DATE AMOUNT ---------------------------------------------------------- 0 20070701 3041.29 1 20070701 1442.20 Issue: Instead of retrieving 2 rows showing 2 type of records, I want to display the 2 type in 2 columns (need to return in 1 row). Can I get this desired result as following in 1 query statement: DATE AMOUNT (TYPE 1) AMOUNT (TYPE 2) --------------------------------------------------------------------------------- 20070701 3041.29 1442.20 Please help. Thanks.
View Replies !
Need Help With A SQL Statement - Trying Not To Use A Cursor
I'm just know basic SQL but not enough to write any complex queries.The problem I'm facing right now keeps me thinking to use a Cursor butI've seen a lot of posts on here saying Cursors are bad so I'm hopingthere is a complex query that can give me the data I need.I have about 6 pages in website where I need to display a datagrid ofinformation. There should be 5 columns, Filename, and then 4 CategoryTitles (These category titles are stored in a table calledPageCategory). I have another table, XREF_Doc_Page that stores thePageID, DocID (ID to know what file it is), and PageCategoryID. So Ican query this table with a pageID to see all the results that shouldbe on this page but I don't know how to format it the way I need mydatagrid?In order to have the records from PageCategory be columns, is this acrosstab query or something?My only thoughts right now are to user a cursor to query Pagecategoryand build a temp table somehow with these as the columns?? (Not surehow'd that would work yet).So the datagrid would have the 5 columns like I said and then justlist all files associated with this page and put a checkmark underwhichever category it was assigned to (example below...)Files PageCat1 PageCat2PageCat3 PageCat4abc.pdf Xxyz.pdf Xjkl.pdfx
View Replies !
How To Capture The Value For A CURSOR Statement
Hi everyone, The following snippet of code returns something like that: string;string1;string2 Up to here fine. I woner how to export such value to ssis variable??? That variable will contain the value needed for the FILEATTACHMENTS property (Send Mail Task) Thanks a lot, declare @anex as varchar(500) declare @anex2 as varchar(700) set @anex2 = '' DECLARE anexos CURSOR FOR SELECT [Ruta] + [Fichero] as ANEXO FROM SVC_FICHEROS INNER JOIN SVC_ENVIOS ON SVC_FICHEROS.IDENVIO = SVC_ENVIOS.IDENVIO WHERE ENVIADO = 0 OPEN anexos; FETCH NEXT FROM anexos INTO @anex WHILE @@FETCH_STATUS = 0 BEGIN IF @anex2 = '' begin set @anex2 = @anex end else begin set @anex2 = @anex2 + ';' + @anex end FETCH NEXT FROM anexos INTO @anex END CLOSE anexos DEALLOCATE anexos
View Replies !
Help With Cursor And Fetch Statement
Hello, I am hoping someone can help me with using the cursor and fetch functions. I have not used these features in the past and I am now stuck when trying to use IF statements with the fetch function. I have a temp table populated with the below headers and their associated data. The headers are as follows: ItemRcvdKey, TranID, TranDate, QtyReceived, UnitCost, ItemKey, WhseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost. The information contained in this temp table lists every single receipt of goods against all of our inventoried items. The QtyOnHand listed on each record is the total QtyOnHand for that item in that warehouse. What I need the fetch to do is grab the receipt of goods records, starting with the most recent TranDate, and pull them into the new temp table until the QtyOnHand is reached. The QtyonHand it should be comparing too is the one listed on the first fetched record. Once the Sum of the QtyRcvd is equal to or is greater than the QtyOnHand for that item I need the fetch to move on to the next item number and perform the same function. One thing I need to be clear on is that if there are 3 Receipt Records(TranID) for Item A in Warehouse A, the total QtyOnHand will be listed 3 times. I need to make sure that the Fetch is comparing all the records for Item A in Warehouse A to one instance of the QtyOnHand. The other aspect is that there will be receipt of goods for the same item in multiple warehouses. So I also need the Fetch to be sure that when it is grabbing records and putting them in the temp table, it makes sure it is matching the ItemID and the WhseID with the record it started with. The current script I have written is below. If you can offer any help I would greatly appreciate it. Code SnippetDeclare @ItemID VarChar(30), @QtyOnHand Decimal (16,8), @WhseID VarChar (6), @SumRcvd Int, @TranID VarChar(30), @TranDate DateTime, @QtyRcvd Decimal (16,8), @UnitCost Decimal (16,8), @ItemKey Int, @WhseKey Int, @ShortDesc VarChar (40), @StdCost Decimal (16,8) DECLARE Temp_cursor CURSOR FOR SELECT TranID, TranDate, QtyRcvd, UnitCost, ItemKey, WHseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost FROM #Temp1 tem OPEN Temp_cursor FETCH NEXT FROM Temp_cursor INTO @TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost WHILE @@FETCH_STATUS = 0 BEGIN -- 0 Insert Into #Temp3 (TranID, TranDate, QtyRcvd, UnitCost, ItemKey, WHseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost) Values (@TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost) FETCH NEXT FROM Temp_cursor INTO @TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost
View Replies !
How To Write A Delete Statement In Linked Server
Hi all, We are retriving tables from DB2 and loading into a SQL server. while retriving temporary tables are getting created in the linked server. we need to delete the data in those temporary tables in Linked server. I don't know how to write delete statement for the tables in linked server. any pointers would be appreciated. Thanks in advance. Shriram
View Replies !
|
TRACKER
