This can be achieved with inner join, no problem. Pretty simple.
However, there's a catch =)
DocParty.Role can have four different values in the 'where' clause. Is there a
way to fetch all of these four values without returning four duplicates with
only one field differing?
There are multiple fields in the query that are to be fetched in similar ways. Therefore,
using a IN('value1','value2','value3','value4') would increase the number of selected rows
a lot.
In addition, there is another type of condition that needs to be fullfilled.
Basically, there two fields in the 'main' table that are joined to the same field in another table
with different conditions. Can this be fetched with the same row as all the other data without duplicates?
Should I use a view somehow? How can I construct a view with these complex conditions if I can't
construct an SQL query, that would return no duplicates (pseudo-du
First of all I€™m a VB beginner, real beginner, like today is my first time hahaha My problem is that I don€™t know how to put my question into VB code. Here goes. I€™m working in Visual Studio and Reporting Services with a chart. I have a chart that shows the amount of trips an opportunity has in my CRM system. The opportunity goes from Open to Won €¦ or lost but I don€™t want to see those.
What I want to do in this chart is to separate all the Open Opportunities from the Won ones. I want to use a Stacked Column chart so Open Opportunities is on the upper part and Won is on the lower part of the column of course.
I can get the trips data from the CRM database, but I don€™t know how to put it in VB code €œSum the trips where Opportunity=Won€? for one series and for the other €œSum the trips where Opportunity=Open€?. In the database I have fields like statecodename='Won'. So far I have in the Data Values, were I write my expression: Opportunity Open Trips €¦ =Sum(Fields!trips.Value) And the same for Opportunities Won Trips €¦ but that isn€™t right of course.
So how do I say €œ=Sum(Fields!trips.Value) €¦ Where statecodename='Won'€??
Hi, I have a unique problem that I am currently unable to figure out. I need to populate a where clause in a SQL statement that has multiple values, however those values always change because they are in another table. The end result that I want to end up with is a list of subs that belong to all of the UCI's that were selected for a particular bid number. I have the following tables tblBid with two columns. Bid_ID, and Uci_ID . This table contains multlple rows with the same Bid_ID but the Uci_ID is never the same for the current Bid_id. For example. If I had a Bid_ID of 123, I might have mutliple records listing bid_id Uci_id 123 1000 123 2000 123 1050 tblSubs_By_Uci that has two columns. Sub_ID, and Uci_ID . This talbe contains a list of Uci_id's that Subs belong to. So I will have only multiple Sub_id and mulitple UCI_ID's because a sub can belong to mulitple Uci_ID's. Uci_ID Sub_ID 1000 456 1000 2345 2000 456 1050 2345 2000 2345
This is the statement I am using to return the Uci's from the Bid table with bid_id of 123. For example. when I run the following sql statement, it will list all of the UCI's for bid_id 123. SELECT Uci_ID from tblBid where Bid_ID = 123 . That produces a list of UCI's. Now I want to find each sub that belongs to each of the UCI's using that list. SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID = (SELECT Uci_ID from tblBid WHERE Bid_ID = 123) . I of course get an error from sql saying that I can not pass multiple values to the Where clause. Can someone please help point me in the right direction. I have been searching on the net for days trying to figure this out. I am open to any suggestions.
I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible...
The problem is thus: I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter.
The Solution I'm attempting: My idea is best represented in psuedo-code: - Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash". - The function would look something like: CREATE FUNCTION CashAccountIDs()
RETURNS TABLE
AS
BEGIN DECLARE TABLE @t1 (account INT) INSERT INTO @t1 VALUES (100) INSERT INTO @t1 VALUES (101) INSERT INTO @t1 VALUES (102) INSERT INTO @t1 VALUES (407) RETURN @t1 END
Then I could call this function by doing something such as:
SELECT * FROM Accounts WHERE AccountId IN (dbo.CashAccountIds())
I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts.
Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type"
(The code I use to generate this error is: CREATE FUNCTION fn_cashaccountids ()
RETURNS @t1 TABLE (i INT)
AS
BEGIN INSERT INTO @t1 VALUES (100) RETURN END
Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before.
hi friends,i need to select some of the employees from the EmpMaster using in clause. I tried to pass a string with the comma delemeters. it didn't produce all the records except the first in that string.shall i try with string functions in TSQL or any other options? Thanks and Regads,Senthilselvan.D
Hi all My query has some inner joins to some tables. And problem is when any ON clause get null as value, the correspondent record is not displayed. SELECTTableA.A, TableB.AFROM TableAINNER JOIN TableB ON TableA.A = TableB.A What I did try: SELECTTableA.A, TableB.AFROM TableAINNER JOIN TableB ON TableA.A = TableB.A OR TableA.A IS NULL (but It generates redundant values from TableB) I need to show all values even that value from Tablea is null Thank a lot for any help
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox: Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly. <asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName"> <SelectParameters> <asp:Parameter Name="ListSelection" DefaultValue="1"/> </SelectParameters> </asp:SqlDataSource> The selListID column is type integer in the database. I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result:
Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim Item As ListItem For Each Item In ListBox1.Items If Item.Selected Then If Label1.Text <> "" Then Label1.Text = Label1.Text + Item.Value + "," Else Label1.Text = Item.Value + "," End If End If Next Label1.Text = Label1.Text.TrimEnd(",") SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID) End Sub What am I doing wrong here? Thanks!
I want to have a FromDateTextBox and a ToDateTextBox where the user can enter in dates (most likely in mm/dd/yy format, although intelligently handing other formats might be a plus). Then I want to use these dates as the basis for a WHERE clause like:<some sql...> WHERE start_date BETWEEN 'FromDateTextBox.Text' AND 'ToDateTextBox.Text' (Note this WHERE clause will be used as the basis for an SqlDataSource FilterExpression). 1. I believe the date strings need to be in the format 'yyyy-mm-dd' to search SQL server is this correct?2. What's a decent way to convert the strings from the textboxes to the required format?3. How can I avoid an SQL injection attack?
Hi! Has anyone experienced this problem? Certain queries that work fine in SQL 6.5 and Oracle return inconsistent / inaccurate results in SQL 7 (with SP1). These queries include an IN clause with a range of values. For example, the following query: SELECT columnA, columnB, columnC, columnD FROM table WHERE columnD = 'I' AND columnA IN (1,2,3,11,19) go
returns a different result than this query: SELECT columnA, columnB, columnC, columnD FROM table WHERE columnD = 'I' AND columnA IN (1,3,11,2,19) go
The only way we have stumbled upon to get accurate results consistently is to order the range values from largest to smallest: AND columnA IN (19,11,3,2,1)
Have not seen this documented anywhere. We are in the process of re-ordering these ranges in our code, but I welcome any ideas or comments... Thanks!
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?
Hi, I am using a SQL back end to dynamically populate an asp.net report/page. As the data I'm interrogating is created from a tree control, I'm having to use a recursive function to retrieve the data into a series of ID values. This all happens at the moment in a DataTable manipulated with c# code. So my ID values end up in this datatable. My problem is that I am then performing a crosstab query in SQL Server 2000 and these ID are required as part of that query. Should I create a temp table and join this into the query or should i feed in a series of ID values into a where clause? Any help gratefully appreciated. Thanks. John
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 .Â
Result (While I am selecting 'Yes' in dropdown) ---------------------------------------------- incident_id usr_id item_id 10059926 191 61006 10054444 222 3232
SELECT incident.incident_id,incident.usr_id,incident.item_id FROM incident where exists (How i can write query here to check the act_type_sc=ADD_ATTCHMNTS is exists)
I understand that Multi-Select Parameters are converted behind the scenes to an In Clause when a report is executed. The problem that I have is that my multi-select string parameter is turned into an in claused filled with nvarchar/unicode expressions like:
Where columnName in (N'Value1', N'Value2', N'Value3'...)
This nvarchar / unicode expression takes what is already a fairly slow-performing construct and just drives it into the ground. When I capture my query with Profiler (so I can see the In Clause that is being built), I can run it in Management Studio and see the execution plan. Using N'Values' instead of just 'Value1', 'Value2','Value3' causes the query performance to drop from 40 seconds to two minutes and 40 seconds. It's horrible. How can I make it stop!!!?
Is there any way to force the query-rewriting process in Reporting Services to just use plain-old, varchar text values instead of forcing each value in the list to be converted on the fly to an Nvarchar value like this? The column from which I am pulling values for the parameter and the column that I am filtering are both just plain varchar.
I am building a search application that has several fields in it - I want to be able to allow the user to put in specific search criteria or all them to say "All" and then pull back any records with anything in that row. Example Select *From mytableWHERE (SDate >= 1-1-2007) AND (EDate <= 1-25-2007) AND (Location = "ALL" or In ALL) this is where I am cloudy.. What/How would I write in the query to pull back everything, I know I could just leave the Location out of the query all together, but then that does not allow them to select just one the next time they run the application. Thoughts? Thanks,Ad.
Hi everyone, I am trying to run this query and it is not returning a single row. Although I can see one row with this 10/14/2005 date in the table.SELECT iSourceId, UserId,FROM tblEmployeeWHERE dtInsertDate >= '10/14/2005' and dtInsertDate <='10/14/2005'I also tried to rewrite the query this waySELECT iSourceId, UserId,FROM tblEmployeeWHERE dtInsertDate = '10/14/2005' but still it is not returning a single row.Please let me know what am I doing wrong.Thanks.
SELECT t1.fname, t1.lname, t2.district_name, t2.district_number FROM t1 AS table1, t2 AS table2 WHERE t2.district_name LIKE 'S%'
i have these values in the table
table1
fname lname mike jackson roy mires
table2
district_name district_number South 123 Daggerty 7845 duffel 7224 rubble 7545
Now the query is dynamic (the letter is that the like clause is run against)
When the letter D is searched for i get the 2 colums duffel, daggerty BUT when S is searched against I get nothing..
I am confused as to why, It doesn't seem to be case sensitive, as the 2 colums duffel and Daggerty 1 is d is in lowercase and the other is in uppercase. andive tries both lowercase s and uppercase S and still got nothing.
Is tehre a better way to use the LIKE clause? Ive looked and looked for documentation about the LIKE clause but I cannot find anything
How can I get all names that start with "sci" or "eng" without using the OR clause. In other words, how can I modify the following statement so I don't use the OR clause: select * from course where (name like 'sci%' or name like 'eng%')
Does anyone know how to count rows of data from 1 table that meet one or two different criteria and then get the probability of occurance for that criteria ... as an output column? Do I use 'Having' or temp tables or Views? Here is my output for now. I am trying to solve for 'Prob' - everything else works on it's own.Craig
SELECT Wins_Lng/Trades_Lng as Prob FROM Transactions SELECT Count(Ticker) as Trades_Lng FROM Transactions WHERE TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND TransKind='B' (SELECT COUNT(Ticker)as Wins_Lng FROM TransactionsWHERE Transkind='B' AND TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND Profit_Lng>=0)
I want to export an SQL Server table to an Excel Spreadsheet driven by a web interface. I am using Cold Fusion to call a SQL Server Stored procedure. The SP accepts a variable (IDlist) from the web page and sets this to a Global Variable.
The SP then executes a DTS package to export to Excel. The DTS package uses the Global variable in the SQL Query thus:
SELECT ... FROM ... WHERE tblPropertyRegister.IDProperty IN (?);
This works fine when I pass one single ID (@outIDlist = "20") into the stored procedure. But it returns no records when I pass multiple IDs (@outIDlist = "19, 20, 21") into the stored procedure. It works fine also if I "hard code" the IDlist into the DTS query (eg WHERE tblPropertyRegister.IDProperty IN (19, 20, 21);). The problem appears to be in the setting of the global variable in the stored procedure.
Has anyone had any experience with this? Any feed back would be greatly appreciated. TIA
sSQL = "SELECT VIN, Year, MakeID AS 'Make', ModelID AS 'Model', Name AS 'Dealer', PhoneOne AS 'Phone', StockDate AS 'Stock Date', SoldDate AS 'Sold Date', RepairCost AS 'Repair Cost' FROM " sSQL = sSQL & sView sSQL = sSQL & " WHERE VIN = '" & sVIN & "'"
The error I got is invalid column 'MakeID' and invalid column 'ModelID'. I'm not familiar with the term "AS" in SQL so can anyone explain what's the problem here?
I've put together the following query, but it has been unsuccessful running it so far.
Code:
select * from Contractors.dbo.Contacts where VendorNo in (select No_ from [BMIS Live Database].dbo.[BMIS Live Database$Vendor] where [Name] like '%of%')
I receive the following error when I run it in Query Analyzer:
Code:
Server: Msg 446, Level 16, State 9, Line 1 Cannot resolve collation conflict for equal to operation.
I'm trying to grab values from the column, "No_" in the Bmis.. database then only select data from the Contractors database if the VendorNo column holds one of the values grabbed from the Bmis.. database. I think my syntax is just completely wrong but hopefully someone might pick up on my mistakes. Any help would be greatly appreciated, thanks!
This is my queryselect ano,max(date),a_subject from MY_TAB where table_name='xyz' andano=877group by a_subject,ano order by a_subjectANOmax(Date)A_Subject8772005-01-20 00:00:00.000Subject_18771900-01-01 00:00:00.000Subject_28772004-12-20 00:00:00.000Subject_38772005-01-19 00:00:00.000Subject_4--------------------------------------------------------------------------When I put the status column in, it fetches all the rows.select ano,max(date),a_subject,status from MY_TAB wheretable_name='xyz' and ano=877 group by a_subject,ano,status order bya_subjectANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8771900-01-01 00:00:00.000Subject_3Not Started8771900-01-01 00:00:00.000Subject_4Not Started8772005-01-19 00:00:00.000Subject_4Not Started-----------------------------------------------------------------------now what i want isANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8772005-01-19 00:00:00.000Subject_4Not StartedThanks a lot for your help.AJ
OK ... I am using UPS Worldship that issues an ODBC query to my MS2Kserver ... Worldship can query either a table or a view and retreiveshipping info for a supplied orderid.I need to create a DB table that will track the orderids requestedfrom Worldship so that I can stop doubleships. That is to set up afunction to allow the info to be sent only once to worldship.I need to execute a stored procedure to write to a table and enforcebiz logic.So .. I've created a view that Worldship can execute an ODBC queryagainst (v_upsPull) ... in which I guess the query issued will belike: SELECT * FROM v_upsPull WHERE orderid = 123456The view is:CREATE VIEW dbo.v_upsPullASSELECT * FROM OPENROWSET ( 'SQLOLEDB', '[db]'; '[user]'; '[password]','exec sp_ups_pull')When the ODBC query calls the view the sp_ups_pull store procedurer isexecuted.However ... I do not have access to the original Where clause in theODBC query in the stored procedurer.Is there a way I can get access to the ODBC Where clause and pass itinto the stored procedurer?If not is there some other way I can create a DB table and run aselect against it ... based on the Worldship query?
HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks
