hello. I have a database that a client developed that I need to pull data from. it consists of articles that fall into a range of 3 main categories. each article will have up to 7 different subcategories they fall into. I need to be able to sort by main category as well as by subcategory. But when I create the SQL query it gets really messy. I tried using WHERE Cat1= comm OR leg OR and so on, but there are seven categories so this gets very cumbersome and doesn't quite work. Is there a way to create an array or a subquery for this? I am a total newbie, so any help is much appreciated!
I need help identifying the following SQL. Sounds like a simple question, but I am new to this type of design and ad-hoc sql with no store procedures. Basically we have no stored procs and all the selects are siting in a table for retrieval.
Select Column1 from db where SomeColum = '{1}'
Can someone explain the '{1}' and is it similar to ?
For my reports I have a Sort By parameter which has 2 values - Customer Name & Customer Number. for my dataset I have added @SortBy as parameter and assigned the value = Parameter!SortBy.value.
In the query I want to set the Order By clause based on the user selection. eg.:
select * from dbo.customers where name = @CustomerName order by @SortBy
However, I am unable to do this. I always get an exception for the order by clause no mater what. I have also tried the following queries in the query designer for the dataset customers but none of them work
="select * from dbo.customers where name " + @CustomerName + " order by " + @SortBy
select * from dbo.customers where name = @CustomerName order by + @SortBy
I know that I can set the interactive sort on the column headers and the interactive sort works, but the customer wants to have the ability to set the Sort By using the dropdown list.
I have my where clause as follows, but it's not working:
WHERE (WPID LIKE @WBS1 + '.' + CASE WHEN @WBS2 = '' THEN substring([Number], 4, 2) = __ ELSE substring([Number], 4, 2) = @WBS2 END CASE)
I give the user two paramters.
The first parameter is to populate the first two characters of a string. There is a "." then another two characters. What I'm trying to do is if the user types nothing in the second parameter, then I use the underscore characters so it can be any two characters. If they do enter two characters, then I want to use them for the 2nd part of the string, hence character 4 and 5.
if I have a SQL string that has an IN clause in its WHERE statement so that I only bring back rows for certains primary key values, how do I handle this with SQL Parameters without having to do any string concatenation?
Running the query in SSMS, Im using something like this to test
WHERE ( [SalesPerson].[SalesPerson].&[17] , [Department].[Department].[All] )
I just need to use the above in an expression with parameters, where BOTH SalesPerson and Department could use a specific member OR use the All member.
Hi, I am trying to build search engin with 11 parameters in 4 different tables in the database. For example: In search.aspx I have 11 textboxes namely nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc. And in the result.aspx page I have gridview which post data from the database if the search match. I wrote this stored procedure. P.S please ignore the syntax. @name var(30),
@nationality (30),
@phone int,
etc
as
Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
WHERE
name LIKE '%' @name '%'
OR nationality LIKE '%' @nationality '%'
OR phone LIKE '%' @phone '%'
etc
But I got an error when I am trying to execute this code because the nulls values so I wrote 1 @name var(30), 2 3 @nationality (30), 4 5 @phone int, 6 7 etc 8 9 as 10 11 12 13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c 14 15 WHERE 16 17 name LIKE '%' ISNULL(@name, '') '%' 18 19 OR nationality LIKE '%' ISNULL(@nationality,'') '%' 20 21 OR phone LIKE '%' ISNULL(@phone,'') '%' 22 23 etc 24 25
Also the error still exist. What is the best way to search for multiple parameters in multiple tables ?
I have created a ssrs report which connects to vertica database through odbc connection. When I try to pass parameter value through parameter (e.g.: column name IN (@parameter) ) then getting error message in query designer prompting "Error in list of values in IN clause. Unable to parse query text. ". Using sql server 2012 , visual studio 2010 version and HP Vertica 7.1 .
where exigo_data_sync.orderdetail.itemcode in (B1001, B1001B, B1007, B1007B, B1008, B1008B, B1000, B1000B, B1006, B1006B, B1009, B1009B)
I keep getting a ADO error stating invalid column names...these are not column names they are the data that i want to use in the where clause. What am I doing wrong?
How can you handle multiple criteria query in T-SQL ? i wrote selection query and in my where clause i have about 7 different criteria and for some reason when i run the query i do not get any error but i do not get any data return.
So is there any other way to handle multiple criteria in T-SQL ?
I have a table with a field that contains an integer which represents the state of a record. This field "intType" may contain values 0-4.
A parameter in my stored procedure "@intUserType" may contain values 0-3
If @intUserType = 0, I need to select the records where intType = 0 or 3 but if @intUserType = 3, I need to return all records where intType > 1, all other values of @intUserType should return no records
The query I am working with seems a bit forced and I feel like it could be simplified, but I can't seem to wrap my head around it.
This is what I am working with:
Code: SELECT * FROM tblEmployees WHERE (intType = (CASE WHEN @intUserType = 0 THEN 0 ELSE NULL END) OR intType = (CASE WHEN @intUserType = 0 THEN 3 ELSE NULL END) OR intType > (CASE WHEN @intUserType = 3 THEN 1 ELSE NULL END))
Maybe it is as good as it needs to be ... I don't know .. I've only been using SQL regulary for a couple of months and I have not had the time to really study it in depth.
I need to return all the distinct IDs where the combination of AttrID and AttrVal matches multiple criteria for that specific ID.
I have the following:
SELECT DISTINCT(ID) FROM ((SELECT a.ID FROM tblAttributes AS a WHERE a.AttrID = 90 AND a.AttrVal = 7) AS x INNER JOIN (SELECT a.ID FROM tblAttributes AS a WHERE a.AttrID = 91 AND a.AttrVal = 8) AS y ON x.ID = y.ID)
Hello People, Please help. I have a basic report with a parameter in the 'Where" clause called (@Stat) from the statement below: " WHERE contractinfo.termdate >= GETDATE() AND provider.status= 'Active' AND provider.credentialstatus = (@Stat)"
This variable has one of two values: 'A' or 'B' that the user selects, how do I set it up so that if user selects say 'A' then the Where clause would go to one set of constraints ie "WHERE contract.description NOT LIKE 'NON%' "
But if the user selects 'B' then the Where clause would go to a different set of constraint ie "WHERE contract.description LIKE 'NON%' " Thanks
Hi AllI am having a problem with an ORDER BY clause when selecting information from multiple tables. EgSELECT i.InvoiceId, pd.PayDescription, u.UserNameFROM Invoice i LEFT OUTER JOIN tblPay ON i.PayId = pd.PayId LEFT OUTER JOIN tblUsers ON i.UserId = u.UserIdORDER BY pd.PayDescriptionthis is just an example my query is a lot more complex. Is there any simply way you can do an order by in this way?I am writing this for MSSQL Server 2000ThanksBraiden
HiI'm a bit stuck with a SELECT query. This is a simplified version ofwhat I need. I've had a look in a few books and online but I'mdefinitely missing something. I'm trying to avoid looping and cursors.I'll be running this in a stored procedure on SQL 7.I have a separate query which returns a series of numbers, A, say 101103 107 109 113.I have a table (tableB) with a field myFieldB where I have anotherseries of numbers, B. I want return each row in tableB wherei - ALL values in A existii- ANY values in A existFor ii, I can use WHERE myFieldB IN AHow about for i?Is there a good guide on the web or a book on WHERE clauses and/ormore complex SQL?Thanks in advance!Sam
I have a parent and child package. i pass a parent package variable called @abc with a value of (1,2,3,4,5,6) to the child package. here in the oledb source i have a select statement like, select * from A where id in (@abc)
Right now I have to do something like this and it is time consuming every time I have to query a specific table...
SELECT lots_of_columns FROM table WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1') AND other_query_critiera_here
Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).
I have come across a problem executing a select with a multi-part where clause that only shows up if there are multiple indexes on the table. The situation using a simplified table is shown below
create table tblTest( utcTimestamp datetime NOT NULL, testType int NOT NULL)go
insert into tblTest (utcTimestamp, testType) VALUES('6/1/2003 0:0:0', 100)go
Now, without adding any indexes to the table, I can execute the following select and it works fine, returning the single row in 2003:
select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0' and testType = 100go
Furthermore, if I introduce a single descending index on just the utcTimestamp:
CREATE INDEX IX_tblTest_Timestamp ON tblTest (utcTimestamp DESC)go
the search still works.
HOWEVER, if I now introduce another index:
CREATE INDEX IX_tblTest_EntryType_Timestamp ON tblTest ( testType, utcTimestamp DESC)go
the search does **not** return the row. However, if I change the where clause to remove the test of testType:
select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0'go
it works.
Also, strangely, if I populate the table with a number of records with different dates and execute the following search:
select * from tblTest where utcTimestamp > '1/1/2004 0:0:0' and testType = 100go
I get records from **earlier** than 1/1/2004 (i.e. like the sense of the compare is wrong)
Finally, as I was writing this report, I discovered that all of these problems go away if the DESC is removed from the indexes - so that's my workaround, but it still looks like a bug.
I created a stored procedure like the following in the hope that I can pass mulitple company_id to the select statement:
CREATE PROC sp_test @in_company_code nvarchar(1024) AS
select company_code, name, description from member_company where company_code in (@in_company_code)
However, I tried the following :
exec sp_test 'abc', 'rrd', 'bbc'
Procedure or function sp_test has too many arguments specified.
and SQLServer doesn't like it.
Did I specify this stored procedure correct? If so, how can I can pass multiple values to the stored procedure then to the sql statement? If not, is it possible to specify a stored procedure like this?
Hello,I'm using a shape query, but instead of using a simple clause "RELATEfield1 to field2" (relates the parent to the child), i wan't to use 2relates. somthing like "RELATE field1 to field2 AND field3 to field4".I want to receive in the children RS only the records who apply bothconditions.How do i do that ?Thanks !
I would like to know how i can handle multiple columns returned by a subquery via IN clause in case of sql server 2005. I can do that in oracle by using the following statement:
DELETE FROM TEST1 WHERE (ID, ID1) NOT IN (SELECT ID,ID1 FROM TEST2);
Hi all. I have the problem on query. This is my query.
select D.fullname, P.religion, E.empno from pspersonaldata as P inner join hremployees as E on P.dcno = E.empdcno inner join psdatacenter as D on D.dcno = E.empdcno where P.religion in ('Born Again','Baptist', 'Catholic')
How could I make a query that returns a result for either "Born Again, Baptist, or Catholic". The parameter would depend on the input of the user depending on how many religion the user inputed. If the user inputed Born Again and Baptist, the result is the employees that have a religion of Born Again and Baptist. Thanks -Ron-
I need to pass a parameter to a stored procedure that allows the user to select all store numbers or a combination thereof. The portion that selects all works fine. I am have trouble with the later. My code looks like this:
IF @StartDate IS NULL BEGIN Set @StartDate = GetDate() Set @EndDate = (GetDate ()+90) END Declare @StoreList TABLE ( CenterID int )
-- Get our Centers IF ( @Center = 'ALL' ) or ( @Center IS NULL ) BEGIN INSERT @StoreList SELECT DISTINCT POS_Site_ID FROM LVIGP.dbo.POS40108 (nolock) END ELSE BEGIN
INSERT @StoreList SELECT DISTINCT POS_Site_ID FROM LVIGP.dbo.POS40108 (nolock) WHERE POS_Site_ID = @Center END
the first if statement returns all of the store number like this CenterID 001 002 010 024 057
the second if returns nothing I tried using coalesce but it returns the store number like this: CenterID 001, 002, 010, 024, 057 This does not work with the rest of my program.
Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.
