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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
SELECT Query - Different Columns/Number Of Columns In Condition
I am working on a Statistical Reporting system where: Data Repository: SQL Server 2005 Business Logic Tier: Views, User Defined Functions, Stored Procedures Data Access Tier: Stored Procedures Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by different organizational hierarchies different number of layers within a hierarchy select a organization or select All of the organizations with the organizational hierarchy combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies: Hierarchy 1 Country -> Work Group -> Project Team (Project Team within Work Group within Country) Hierarchy 2 Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases: Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams Country = "USA", Work Group = all work groups Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance" Client = "Client A", Contract = "2007-2008 Maint", Project = all Client = "Client A", Contract = allI am totally stuck on: How to implement the data interface (Stored Procs) to the Reports Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all") (WorkGroup = @argWorkGroup OR @argWorkGrop is NULL) Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services. Thanks for all your help!
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 !
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 !
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 !
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 !
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 !
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 !
SQL Query Condition
Hi, I am creating a stored procedure which takes a few paramaters. One of which is a string of comma separated codes. At the end of my select query the codes will be used in the where clause. What I'm doing is parsing the comma separated string and populating a temp table called #codes. So my query will look something like this: select * from tableA where tableA.col1 = 'something' and tableA.code in (select * from #codes) and.... However, the code paramater can be null and if this is the case I want the query to be called for all codes - i.e. I effectively want to remove the and tableA.code in (select * from #codes) part of the where clause. Is there any clever way of doin this other than having a if... else... and writing 2 queries - one with the and and one without it? Hope this is clear. Thanks, Wallace
View Replies !
How To Put Where Condition In Xml Query
hi, I am to SSRS.I using following xml file . <Sales> <Region value = €œWest€?> <Name>Vinod Krishnan</Name> <Detail> <Country name = €œUSA€? amount = €œ12345678.90€? /> <Country name = €œCanada€? amount = €œ898545.03€? /> </Detail> </Region> <Region value = €œEast€?> <Name>Kylan Mylappillii</Name> <Detail> <Country name = €œIndia€? amount = €œ333348.00€? /> <Country name = €œChina€? amount = €œ678912.34€? /> <Country name = €œIndonesia€? amount = €œ100000.00€? /> </Detail> </Region> </Sales> I wrote query in quer tab as <Query> <ElementPath> Sales /Region /Detail/Country </ElementPath> it returns all data. how to put "where " condition in above query. </Query>
View Replies !
Condition On Sum Value (t-sql Query)
Hello all, I've got some problem with the following query: select subscriber_id, sum(convert(int,n_inboundcalls)) as totalinbound from calldiversityratedaily where totalInbound < 10 group by subscriber_id The error message provided is: Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'totalInbound'. I'm using MSSQL server 2000, version 8.00.194 and it appears that I can't use the name I give to the sum in the where clause. Could anyone help me please?
View Replies !
SQL Query Using String Condition
My table is as follows and I have a string ‘U2’,’U4’ CustomerID UserID OtherUsers C1U1#U3~#U5~ C2U2 C3U3 C4U4#U1~#U2~ C5U5 C6U1#U2~#U5~ C7U2 C8U3#U1~#U4~ Now I should issue a sql which should extract the CustomerID where UserID in (‘U2’,’U4’) or (U2 or U4) in OtherUsers. i.e I should get the CustomerIDs C2,C4,C6,C8 I have trouble in coming out with the sql. Does anyone have idea how to achieve this?
View Replies !
Complicated Sql Condition Query
Hi, hope someone can help or suggest something to help me with the issue. I have a list of projects. this list contains all master (parent) and all subprojects(child). when I click on a project I want to be able to retreive information about that project and it's subprojects. here is my delema. I want my sql query to check if project is master or sub. if master then get all data for this project and its subprojects but if it's sub then get data only for that sub. below is a sample data that I hope it clear things up parent_ID child_id Type projname ----------------------------------------------- 100 100 P parent_X_proj 100 25 C child_X_proj 100 29 C child_X_proj2 200 200 P parent_Z 300 300 P parent_Y etc................ this is how my table is constructed. my application passes child_id and what I want is if someone clicks on parent_X_proj I want to be able to retreive the three projects (100,25,29) but if someone clicks on child project (29) then I want only that project. so I want my query to look for the type and if my type is P (parent) then get all project where parent_id = 100(for example) but if type= C then get child_id = 29. I know it could be done in stored procedure buy my application cannon executre SP but only sql statements. Thank you for any help
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 !
Condition On Query Created Field
Hi all, i have a query as below that creates a count in the field Total. I wanted to then be able to say only show me where there are more than 2 incidents. But i can't get it to recognise my created field. Any advice? Thanks. select a.vchcompanyname, count(*) as total from company a inner join incident b on b.iownerid = a.icompanyid where b.iincidentcategory = 1 and a.icompanytypecode = 102165 --and total > 2 (Wont recognise Total) group by b.iownerid, a.icompanyid, a.vchcompanyname order by 2 desc
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 !
Using A Variable That Stores The Query String For WHERE Condition
Hi, I was wondering if someone could help out. I need to create a stored procedure, but before i do so, I need to know if I can store a conditional expression in a string and just use that variable as my condition for the WHERE clause. The reason I ask this is because I am trying to create a stored procedure that queries different things depending on what the inputs are.
View Replies !
Query Performance With DateTime Versus Int Condition
Good day, The following query performs acceptably (2 seconds against 126,000,000 rows in the main table): SELECT Count(*) FROM Message1_2_3 INNER JOIN VDMVDO ON Message1_2_3.VDMVDO_ID = VDMVDO.VDMVDO_ID INNER JOIN NMEA ON VDMVDO.NMEA_ID = NMEA.NMEA_ID WHERE NMEA.NMEA_ID BETWEEN 14000000 AND 14086000 AND VDMVDO.RepeatIndicator = 0 AND NMEA.SentenceFormatterID = 'VDM' When we change the first condition from an Int column to a DateTime as in: NMEA.TimeDate BETWEEN CONVERT(DATETIME, '2007-07-09 8:30:00', 102) AND CONVERT(DATETIME, '2007-07-09 9:30:00', 102) the query performance falls to 14 seconds, even though both columns are indexed and a similar number of rows are found. When the select clause changes from a simple Count to a complex Max expression, response time falls to over a minute! Any thoughs on optimizing the DateTime search would be greatly appreciated...
View Replies !
Return The Results Of A Select Query In A Column Of Another Select Query.
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
View Replies !
Select Query Based Upon Results Of Another Select Query??
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return. This is implemented as a stored procedure declare @OwnerIdent varchar(7) set @OwnerIdent='A12345B' SELECT table1.val1 FROM table1 INNER JOIN table2 ON table1. Ident = table2.Ident WHERE table2.Ident = @OwnerIdent 'Now for each result of the above I need to run the below query SELECT Clients.Name , Clients.Address1 , Clients.BPhone, Clients.email FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey WHERE Growers.PIN = @newpin) '@newpin being the result from first query Any help appreciated
View Replies !
ADO + MS Access Cannot Use &&"date&&" As Query Condition
First I have to apologize about posting the problem here , since it not actually relate to SQL Server I am really new to database programming. I use ADO (the ActiveX one , Not ADO.NET) In an MFC Application to access MS ACCESS Database I can insert date in to table using statement like this insert into tableXXX (date_field) values ('1/2/2007') but when i tried to query it using this statement select * from tableXXX where date_field = #1/2/2007# Nothing return I also tried to use #1-2-2007# , #01-02-2007# but it all the same Can someone tell me how to successfuly use date as a query condition ? Thank in Advance
View Replies !
Result Sets Using Select In Query Anlyzer Vs BCP Vs Select Into
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into. Thanks
View Replies !
Date Select Query - Select Between Two Dates
have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003. i have tried with the following query, i need help putting my queries together select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004') when i run this query i get this message Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15 please help mustfa
View Replies !
An Easy Way To Reference The Nth Row From A Table Meeting A Condition X, And The Mth Row From The Same Table Meeting Condition Y
Hi I am developing a scientific application (demographic forecasting) and have a situation where I need to update a variety of rows, say the ith, jth and kth row that meets a particular condition, say, x. I also need to adjust rows, say mth and nth that meet condition , say y. My current solution is laborious and has to be coded for each condition and has been set up below (If you select this entire piece of code it will create 2 databases, each with a table initialised to change the 2nd,4th,8th and 16th rows, with the first database ignoring the condition and with the second applying the change only to rows with 'type1=1' as the condition.) This is an adequate solution, but if I want to change the second row meeting a second condition, say 'type1=2', I would need to have another WITH...SELECT...INNER JOIN...UPDATE and I'm sure this would be inefficient. Would there possibly be a way to introduce a rank by type into the table, something like this added column which increments for each type: ID Int1 Type1 Ideal Rank by Type 1 1 1 1 2 1 1 2 3 2 1 3 4 3 1 4 5 5 1 5 6 8 2 1 7 13 1 6 8 21 1 7 9 34 1 8 10 55 2 2 11 89 1 9 12 144 1 10 13 233 1 11 14 377 1 12 15 610 1 13 16 987 2 3 17 1597 1 14 18 2584 1 15 19 4181 1 16 20 6765 1 17 The solution would then be a simple update based on an innerjoin reflecting the condition and rank by type... I hope this posting is clear, albeit long. Thanks in advance Greg PS The code: USE master GO CREATE DATABASE CertainRowsToChange GO USE CertainRowsToChange GO CREATE TABLE InitialisedValues ( InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY, Int1 int NOT NULL ) GO CREATE PROCEDURE Initialise AS BEGIN INSERT INTO InitialisedValues (Int1 ) SELECT 2 INSERT INTO InitialisedValues (Int1 ) SELECT 4 INSERT INTO InitialisedValues (Int1 ) SELECT 8 INSERT INTO InitialisedValues (Int1 ) SELECT 16 END GO EXEC Initialise /*=======================================================*/ CREATE TABLE AllRows ( AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY, Int1 int NOT NULL ) GO CREATE TABLE RowsToChange ( RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY, Int1 int NOT NULL ) GO CREATE PROCEDURE InitialiseRowsToChange AS BEGIN INSERT INTO RowsToChange (Int1 ) SELECT 2 INSERT INTO RowsToChange (Int1 ) SELECT 4 INSERT INTO RowsToChange (Int1 ) SELECT 8 INSERT INTO RowsToChange (Int1 ) SELECT 16 END GO EXEC InitialiseRowsToChange GO CREATE PROCEDURE PopulateAllRows AS BEGIN INSERT INTO AllRows (Int1 ) SELECT 1 INSERT INTO AllRows (Int1 ) SELECT 1 INSERT INTO AllRows (Int1 ) SELECT 2 INSERT INTO AllRows (Int1 ) SELECT 3 INSERT INTO AllRows (Int1 ) SELECT 5 INSERT INTO AllRows (Int1 ) SELECT 8 INSERT INTO AllRows (Int1 ) SELECT 13 INSERT INTO AllRows (Int1 ) SELECT 21 INSERT INTO AllRows (Int1 ) SELECT 34 INSERT INTO AllRows (Int1 ) SELECT 55 INSERT INTO AllRows (Int1 ) SELECT 89 INSERT INTO AllRows (Int1 ) SELECT 144 INSERT INTO AllRows (Int1 ) SELECT 233 INSERT INTO AllRows (Int1 ) SELECT 377 INSERT INTO AllRows (Int1 ) SELECT 610 INSERT INTO AllRows (Int1 ) SELECT 987 INSERT INTO AllRows (Int1 ) SELECT 1597 INSERT INTO AllRows (Int1 ) SELECT 2584 INSERT INTO AllRows (Int1 ) SELECT 4181 INSERT INTO AllRows (Int1 ) SELECT 6765 END GO EXEC PopulateAllRows GO SELECT * FROM AllRows GO WITH Temp(OrigID) AS ( SELECT OrigID FROM (SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows) AS FromTable INNER JOIN RowsToChange AS ToTable ON FromTable.RowScore = ToTable.Int1 ) UPDATE AllRows SET Int1=1000 FROM Temp as InTable JOIN Allrows as OutTable ON Intable.OrigID = OutTable.AllRowsID GO SELECT * FROM AllRows GO USE master GO CREATE DATABASE ComplexCertainRowsToChange GO USE ComplexCertainRowsToChange GO CREATE TABLE InitialisedValues ( InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY, Int1 int NOT NULL ) GO CREATE PROCEDURE Initialise AS BEGIN INSERT INTO InitialisedValues (Int1 ) SELECT 2 INSERT INTO InitialisedValues (Int1 ) SELECT 4 INSERT INTO InitialisedValues (Int1 ) SELECT 8 INSERT INTO InitialisedValues (Int1 ) SELECT 16 END GO EXEC Initialise /*=======================================================*/ CREATE TABLE AllRows ( AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY, Int1 int NOT NULL, Type1 int NOT NULL ) GO CREATE TABLE RowsToChange ( RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY, Int1 int NOT NULL, Type1 int NOT NULL ) GO CREATE PROCEDURE InitialiseRowsToChange AS BEGIN INSERT INTO RowsToChange (Int1,Type1 ) SELECT 2, 1 INSERT INTO RowsToChange (Int1,Type1 ) SELECT 4, 1 INSERT INTO RowsToChange (Int1,Type1 ) SELECT 8, 1 INSERT INTO RowsToChange (Int1,Type1 ) SELECT 16, 1 END GO EXEC InitialiseRowsToChange GO CREATE PROCEDURE PopulateAllRows AS BEGIN INSERT INTO AllRows (Int1, Type1 ) SELECT 1, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 1, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 2, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 3, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 5, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 8, 2 INSERT INTO AllRows (Int1, Type1 ) SELECT 13, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 21, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 34, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 55, 2 INSERT INTO AllRows (Int1, Type1 ) SELECT 89, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 144, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 233, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 377, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 610, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 987, 2 INSERT INTO AllRows (Int1, Type1 ) SELECT 1597, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 2584, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 4181, 1 INSERT INTO AllRows (Int1, Type1 ) SELECT 6765, 1 END GO EXEC PopulateAllRows GO SELECT * FROM AllRows GO WITH Temp(OrigID) AS ( SELECT OrigID FROM (SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows WHERE Type1=1) AS FromTable INNER JOIN RowsToChange AS ToTable ON FromTable.RowScore = ToTable.Int1 ) UPDATE AllRows SET Int1=1000 FROM Temp as InTable JOIN Allrows as OutTable ON Intable.OrigID = OutTable.AllRowsID GO SELECT * FROM AllRows GO
View Replies !
Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query
Hello, I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post. I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table. This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off. So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining? Thanks in advance, Andy Select * from ( declare @years table (years int); insert into @years select CASE WHEN month(getdate()) in (1) THEN year(getdate())-1 WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate()) END select u.fullname , sum(tx.Dm_Time) LastMonthBillhours , sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage from Dm_TimeEntry tx join systemuserbase u on (tx.owninguser = u.systemuserid) where Month(tx.Dm_Date) = Month(getdate())-1 and year(dm_date) = (select years from @years) and tx.dm_billable = 1 group by u.fullname ) as A left outer join (select u.FullName , sum(tx.Dm_Time) Billhours , ((sum(tx.Dm_Time)) / ((day(getdate()) * ((5.0)/(7.0))) * 8)) perc from Dm_TimeEntry tx join systemuserbase u on (tx.owninguser = u.systemuserid) where tx.Dm_Billable = '1' and month(tx.Dm_Date) = month(GetDate()) and year(tx.Dm_Date) = year(GetDate()) group by u.fullname) as B on A.Fullname = B.Fullname Left Outer Join ( select u.fullname , sum(tx.Dm_Time) TwomosagoBillhours , sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage from Dm_TimeEntry tx join systemuserbase u on (tx.owninguser = u.systemuserid) where Month(tx.Dm_Date) = Month(getdate())-2 group by u.fullname ) as C on A.Fullname = C.Fullname
View Replies !
Select Query Vs Store Procedure Query
hi, does it make a difference to write the following select statement in either query window or create a sp and then calling the store procedure to be executed.. select * from authors OR create procedure authors as select * from authors lets assume that we have million records in the author table. is it faster to run the query from within a store procedure or not ? thanks for your input Ali
View Replies !
Select Query Vs Store Procedure Query
hi, does it make a difference to write the following select statement in either query window or create a sp and then calling the store procedure to be executed.. select * from authors OR create procedure authors as select * from authors lets assume that we have million records in the author table. is it faster to run the query from within a store procedure or not ? thanks for your input Ali
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 !
How To Remove Partially Duplicate Rows From Select Query's Result Set (DB Schema Provided And Query Provided).
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate ORDER BY t.topicid ASC,th.lastpostdate DESC Whose result set is as below:- forumid topicid name author lastpostdate NoOfThreads 1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2 1 1 Java Overall a@b.com 2008-01-27 14:44:29.000 2 1 2 JSP NULL NULL 0 1 3 EJB NULL NULL 0 1 4 Swings p@q.com 2008-01-27 15:12:51.000 1 1 5 AWT NULL NULL 0 1 6 Web Services NULL NULL 0 1 7 JMS NULL NULL 0 1 8 XML,HTML NULL NULL 0 1 9 Javascript NULL NULL 0 2 10 Oracle NULL NULL 0 2 11 Sql Server NULL NULL 0 2 12 MySQL NULL NULL 0 3 13 CSS NULL NULL 0 3 14 FLASH/DHTLML NULL NULL 0 4 15 Best Practices NULL NULL 0 4 16 Longue NULL NULL 0 5 17 General NULL NULL 0 On modifying the query to:- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid) ORDER BY t.topicid ASC,th.lastpostdate DESC I get the result set as below:- forumid topicid name author lastpostdate NoOfThreads 1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2 1 4 Swings p@q.com 2008-01-27 15:12:51.000 1 I want the result set as follows:- forumid topicid name author lastpostdate NoOfThreads 1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2 1 2 JSP NULL NULL 0 1 3 EJB NULL NULL 0 1 4 Swings p@q.com 2008-01-27 15:12:51.000 1 1 5 AWT NULL NULL 0 1 6 Web Services NULL NULL 0 1 7 JMS NULL NULL 0 1 8 XML,HTML NULL NULL 0 1 9 Javascript NULL NULL 0 2 10 Oracle NULL NULL 0 2 11 Sql Server NULL NULL 0 2 12 MySQL NULL NULL 0 3 13 CSS NULL NULL 0 3 14 FLASH/DHTLML NULL NULL 0 4 15 Best Practices NULL NULL 0 4 16 Longue NULL NULL 0 5 17 General NULL NULL 0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. The scripts for creating the tables and inserting test data is as follows in an already created database:- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Forums] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Threads] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Topics] GO CREATE TABLE [dbo].[Forums] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Threads] ( [threadid] [int] IDENTITY (1, 1) NOT NULL , [topicid] [int] NOT NULL , [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [replies] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastpostdate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Topics] ( [topicid] [int] IDENTITY (1, 1) NOT NULL , [forumid] [int] NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Forums] ADD PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD PRIMARY KEY CLUSTERED ( [threadid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD PRIMARY KEY CLUSTERED ( [topicid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD FOREIGN KEY ( [topicid] ) REFERENCES [dbo].[Topics] ( [topicid] ) GO ALTER TABLE [dbo].[Topics] ADD FOREIGN KEY ( [forumid] ) REFERENCES [dbo].[Forums] ( [forumid] ) GO ------------------------------------------------------ insert into forums(name,description) values('Developers','Developers Forum'); insert into forums(name,description) values('Database','Database Forum'); insert into forums(name,description) values('Desginers','Designers Forum'); insert into forums(name,description) values('Architects','Architects Forum'); insert into forums(name,description) values('General','General Forum'); insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall'); insert into topics(forumid,name,description) values(1,'JSP','Topic JSP'); insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans'); insert into topics(forumid,name,description) values(1,'Swings','Topic Swings'); insert into topics(forumid,name,description) values(1,'AWT','Topic AWT'); insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services'); insert into topics(forumid,name,description) values(1,'JMS','Topic JMS'); insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML'); insert into topics(forumid,name,description) values(1,'Javascript','Javascript'); insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle'); insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server'); insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL'); insert into topics(forumid,name,description) values(3,'CSS','Topic CSS'); insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML'); insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices'); insert into topics(forumid,name,description) values(4,'Longue','Longue'); insert into topics(forumid,name,description) values(5,'General','General Discussion'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
View Replies !
Select Query
Hello All I havetable with 2 columns and Datas like this LRNo LRStatus L001 10 L002 10 L003 10 L001 15 L002 15 L001 20 i need a query to select the LRNo where lrstatus =10 and not equal to 15 and 20 here is my query select lrno from tbl where lrstatus in (10) and not in (15.20) but i didnt get it Please help me Thanks in Advance
View Replies !
Help With Select Top X Query
im trying to get a page to display this query like this in a data grid SELECT TOP @rows * FROM students where student_id = @studentID Basically I want code that returns the top X number of rows, where X is a user defined variable (like in a textbox)... I've been trying to find a way to get this to work and have not and any luck... any ideas? thanks!
View Replies !
Use Value From Select Query
I am writing to learn if it is possible to obtain a value from a select query, and utilize this value in a succeeding query. Please see below for my current query. I would like to take the filenum result, and use the value in a second query. Thanks in advance! select shhd.CUST_NO, shhd.file_no as filenum from tpsdba.shipment_header SHHD WHERE SHHD.CUST_NO = '052584 ' AND (SHHD.DATE_ENTRY >= '20080401' AND SHHD.DATE_ENTRY <= '20080404')
View Replies !
Select Query ?
hi friends...... i have table t1 task startdate enddate 1 03/04/2008 10/04/2008 2 04/04/2008 10/04/20083 06/04/2008 25/04/2008 i need a query to get task id and column2column2 = if(enddate > getdate()) then 1 else 2 give me the answer without the use of cursors......answer should be in a query...thanks in advance
View Replies !
Select Query
I have a table as below after executing select query select userid as userid,count as totalcount from table1 UserId totalcount 101 15 102 7 105 6 106 4 108 3 i want a extra column so that it is an autoincrement and the query result should be like UserId totalcount Sno 101 15 1 102 7 2 105 6 3 106 4 4
View Replies !
Select Query
i have a table which has a colum name's Name, Album. the way data is storing is for Name Album '123' 'xyz' '4555' 'xyz' ''212' 'xyz' '33sa' 'abc' 'nyz1' 'abc' and so on. what would be the query for selecting all the Album's one row only. like when i write the query i want it to return like this. 'xyz' 'abc'
View Replies !
Select Query
Hello it's me again running against the time. Well the scenario of my query is to do a threads view like this forum has, but a bit different of course, it's 3 tables one is the postsTable with fields post_id, thread_id, user_id, title, text and another is threads table which contain details of the threads with fields thread_id, rate and the last one that is called standBy with fields thread_id, user_id, which is for the users who want to be in touch with the thread. So I have to get the first most recent post created for each thread and last one, and see if the current user are “in touch� with the thread. The query I think is almost working and it’s the following: SELECT t.thread_id, p.post_id, lp.post_id, t.rate, t.type, t.views, t.posts, t.numberVotes, p.title, p.answer, s.thread_id, u.user_id As 'firstUser', u.userType_id as 'firstUserType', u.nickname as 'firstNickname', lu.user_id AS 'lastUser', lu.userType_id AS 'lastUserType', lu.nickname AS 'lastNickname', min(p.creation) as 'firstCreation', max(lp.creation) AS 'lastCreation' FROM solarPosts AS p INNER JOIN solarThreads AS t ON p.thread_id = t.thread_id INNER JOIN solarUsers AS u ON p.user_id = u.user_id INNER JOIN solarPosts AS lp ON lp.thread_id = t.thread_id INNER JOIN solarUsers AS lu ON lp.user_id = lu.user_id LEFT OUTER JOIN solarStandBy AS s ON t.thread_id = s.thread_id and s.user_id = @user_id GROUP BY t.thread_id, p.post_id, lp.post_id, t.rate, t.type, t.views, t.posts, t.numberVotes, p.title, p.answer, s.thread_id, u.user_id, u.userType_id, u.nickname, lu.user_id, lu.userType_id, lu.nickname having p.post_id = min(p.post_id) and lp.post_id = max(lp.post_id) -- the "group by" does a agrupment of fields with the same value, as the almost of the fieds in the query above -- from solarThreads will have the same value in all rows so it must go with "group by" clause The having clause is doing nothing, and the left outer join is working except when you put the Where clause because the nulls fields don’t appear in case that there is no “in touch� or “listener� for this user. Thank you very much for your patient.
View Replies !
Select Query Help
I need to query the DB and only see if the 1st 5 characters of a Guid match. How would i go about only calling a certain amount of characters from a Guid?
View Replies !
SELECT Query
Hello,I have the following tables:[Blogs] > BlogId (PK), ...[Posts] > PostId (PK), BlogId (FK), Title, Content, ...[Labels] > LabelId (PK), LabelName[LabelsInPosts] > LabelId (PK), PostId(PK)I am selecting all posts from a Blog given the BlogId:SELECT * FROM dbo.Posts WHERE BlogId = @BlogIdThis will return the columns Title, Content, ... for those posts.I would like to add 2 columns, PostLabels and LabelsCount:1. PostLabels would be created by:For each post select all the labels associated with it inLabelsInPosts. Then for each label get the label name. Then createthe value of PostLabels which would be each label separated by acomma.I tried the following but this is not working. I am having problems getting the LabelId from LabelsInPosts and then getting the LabelName from Labels and use them to create the CSV string:SELECT DISTINCT l1.PostID, STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + l2.LabelName FROM Labels AS l2 WHERE l2.PostID = l1.PostID ORDER BY ',' + l2.LabelName FOR XML PATH('')), 1, 1, '') AS PostLabelsFROM Labels AS l1ORDER BY l1.PostID2. LabelsCount would be the number of labels associated with each postI created a procedure which, given a PostId, returns the number oflabels associated with it.So I suppose it would be only a question of integrating a call tothis procedure with this SELECT I am trying to create.Thank You,Miguel
View Replies !
Select Query
Hi, I'm trying to do the following select statement. I have the following databases with the following tables and columns which will be involved with the query. DataBase Name: usersTable: UsersColumn: UserNameColumn: UserId DataBase Name: documentareasTable: document_areaColumn: doc_area_idColumn: doc_area_nameColumn: doc_area_typeColumn: doc_area_defaultTable: document_area_user_accessColumn: doc_area (contains a doc_area_id)Column: user_id (contains a username) Now I want to select the doc_area_name where the logged in username exists in the user_id of the document_area_user_access table. So to clarify, I am looking to look through the document_area_user_access for each time the current users username exists and for each existing username the doc_area_name is displayed depending on what doc_area(s) is associated with the user_id in document_area_user_access. Don't know if this has made any sense?? I am hoping to do all this in the aspx sqlsource if possible. As for each userarea that the user has access a link will exist. Cheers, Mark
View Replies !
SQL SELECT Query
Hi, I have been having problems trying to sort this query out and would appreciate some pointers. I have a SQL Server table 'Match' which includes the columns: match_date datetime age_group int The match_date column includes both date and time values. The age_group column is an id linked to an 'Age_Group' table. I would like the select query to retrieve the top 3 dates (not including times) and the agegroups for these dates. For example: The table data might include match_date, agegroup -------------------- 10/11/2006 10:00, 1 10/11/2006 11:00, 1 10/11/2006 12:00, 1 10/11/2006 13:00, 2 03/11/2006 09:00, 3 03/11/2006 10:00, 4 03/11/2006 11:00, 5 25/10/2006 10:00, 2 20/09/2006 10:00, 5 20/09/2006 10:00, 6 I would like the SELECT to retrieve like so: match_date, agegroup -------------------- 10/11/2006, 1 10/11/2006, 2 03/11/2006, 3 03/11/2006, 4 03/11/2006, 5 25/10/2006, 2 So the query only returns the one record for each age_group on each date for the top 3 dates. Does anyone have any ideas? I hope I have explained it well enough! Thanks, Pete
View Replies !
Going Over 2 Select's In 1 Query
i have a code L;conPubs = New SqlConnection(conStr) conPubs.Open() '**>> building select box for מצב המסלול cmdSelect = New SqlCommand("Select * From course_type;SELECT UploadTrail_id, UploadTrail_select_types_id FROM UploadTrail_select_values WHERE (UploadTrail_id = 54) AND (UploadTrail_select_types_id = 6);", conPubs) Dim Reader As SqlDataReader Reader = cmdSelect.ExecuteReader() course_type.DataSource = Reader course_type.DataValueField = "course_id" course_type.DataTextField = "course_name" course_type.DataBind() if u look u will see 2 select's that are delimited with ; (semicolon)how can i access the second recordset after i bind the first one on the last line (i want to get values from the rows in the second "select")thnaks in advancepeleg
View Replies !
SQL Select Query Help
How do I render a select statement in my sever script. So far the only way that I know how is to drag a sqldatasource control and a gridview or detailsview.... I would like the select statement output to be displayed in a gridview but prefers to run the statement at the server side. All the help would be appreciated. Thanks
View Replies !
SQL Query: Select * Where X=everything??
I've got a DataGrid that displays results depending on a selection in a dropdown list. So far, so good.But I'd also like to have an option in the dropdown list that permits to display ALL the results. The query used to fill the datagrid is the following: "SELECT * FROM [tSpecies] WHERE ([Status] = @Status)"The @Status variable refers to the selected value in the dropdown list. Which value would be needed so that this WHERE clause would display ALL the records?Thanks a lot!
View Replies !
|
TRACKER
