Multi-Select On List View - Return InstructorID Where ClassID Matches All Of ClassIDs In String
Nov 19, 2012
I have a list of ClassID that is stored based on users multi select on a listview
For example ClassID might contain
301
302
303
304
Now I need to find InstructorID where classID matches all the value in the above list.
I am using this query
Code:
Dim assSQL = "Select InstructorID from ClassInstructors where ClassID = @P0"
For i = 1 To classIDs.Count - 1
assSQL &= " UNION Select InstructorID from ClassInstructors where ClassID = @P" & i.ToString
Next
[Code] ....
But the problem is the query is returning InstructorID where ClassID matches any of the ClassIDs. I want it to return Instructor ID where ClassID matches all of the ClassIDs in the string.
Let's say I want to get the ROOMTYPE which contains AMENITY 1,2,4 only.
If I do this:
SELECT ROOMTYPE FROM TABLE WHERE AMENITY IN (1,2,4)
I get all the 3 RoomTypes because the IN acts like an OR but I want to get the column which contains ALL of the items in the list, i.e I want the ROOMTYPE that has ALL of the 1,2,4 but not just 1 or 1,2, etc...In this case, I want only R002 to be returned because the other RoomTypes do not contain all of the 1,2,4
NOTE: The data and list above is an example only as the table contents as well as the list will change over due course. e.g. the list can be (2,6,8,10,20,..100) etc.. So, I would need a query which can cater for any list contents...(x,y,z,...) and the query should return me the RoomTypes which have ALL elements in that particular list. If one of the RoomTypes do not have an element, it should NOT be returned.
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 am trying to get a null value to pass to a multi-select parameter in SSRS 2005. The multi-select box is getting its possible values from the following query:
SELECT NULL CMP_ID, '<all companies>' COMPANY UNION SELECT DISTINCT T1.CMP_ID, T1.COMPANY FROM VIEWCOMPANYLIST T1 WHERE T1.DIV_ID = 2 ORDER BY 1
This query is pre-pending a row with NULL as the first ID. The resulting data set is exactly what you would expect - Row 1 has a null in column CMP_ID, and <all companies> in column COMPANY. All the rest of the rows have proper data. My multi-select parameter box is of type Multi-value Int, with possible values populated by this query and a default of NULL. However, when I preview the report, the <all companies> row disappears.
It's not even in the list, and it's not getting selected by the default selector. So, of course, when I try to View Report without selecting anything, I get prompted to select a company. Why the prepended row in my dataset is getting dropped from the multi-select box at display time? And yes, I have to be able to pass a null value because the stored procedure on which the report is based expects this parameter to be optional.
Hi... Need help with some SQL-code: Im just interesting in how many rows in my table 'Location' that has 'New York' in the column called 'City'.... So I just want to return the number of rows that is macthing... How do I write the sql-part for this??
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
But data is needed from 3 tables... - Created a VIEW that returns all (82) rows (negating distinct of the function on qcParent_ID) - Failed Miserably Integrating Function call into a multi-table select (inexperienced with complex joins)
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
AND THEN THERES... Failing miserably on Integrating the Function call into This SELECT ON MULTI-TABLES:
How to integrate the Function call: JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
into the multi-table Select relationships (while maintaining Where & Order By):
Problem is that if the [Receiving] table doesn't have a match then no records are return. I want all matches from the [Orders Subtable] and any matches from the [Receiving] Table. If no [Receiving] table matches then I still want all matches from the [Orders Subtable]. Attached is the query.
Note: The query has to run in Access 2000 and I will be coding it in VB.
FROM (Orders LEFT JOIN Receiving ON Orders.[Orders ID] = Receiving.[Orders ID]) INNER JOIN [Orders Subtable] ON Orders.[Orders ID] = [Orders Subtable].[Orders ID]
GROUP BY Orders.[Orders ID], [Orders Subtable].ID, [Orders Subtable].Quantity, Receiving.Quantity, Orders.[Project #], [Orders Subtable].On_Order, [Orders Subtable].[Component #], Receiving.[Component #]
HAVING (((Orders.[Project #])="Speed1aaaaa") AND (([Orders Subtable].On_Order)=True) AND (([Orders Subtable].[Component #])="R02101A") AND ((Receiving.[Component #])="R02101A"));
I am using SQL 2014 RTM (may be it's time to upgrade).
I have the following view:
create view [dbo].[SiriusV_Max4SaleList] as select m.id as Max4SaleId, mt.[Description] as [TypeDescription], CAST(m.[type] as tinyint) as [Type], m.start_time as [StartTime], m.end_time as [EndTime],
[Code] ....
I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.
I executed the following select statement once
select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'
And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.
I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.
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.
Has anyone find solution for this problem. i also checked http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=298056&SiteID=1 and http://blogs.conchango.com/kristianwedberg/archive/2006/02/22/2955.aspx
Suppose have a Dimension table
DimColor ---------------------------- ColorKeyPK(smallint) ColorAlternateKey(nvarchar(30)) -1 UnknownMember 1 2 Blue 3 Red 4 Black
Color with the ID 1 is empty string
FactOrders --------------------------- OrderID Date Color Quantity
OrderID = 1 Color = 'Black' Quantity = 10 OrderID = 2 Color = 'Red' Quantity = 20 OrderID = 3 Color = '' Quantity = 10 OrderID = 4 Color = 'Blue' Quantity = 5 OrderID = 5 Color = Black Quantity = 10
When i use the Lookup transform it cannot find the ColorKeyPK
The result of the Lookup transform is. ------------------------------ OrderID = 1 Color='Black' ColorKey=4 OrderID = 2 Color='Black' ColorKey=3 OrderID = 3 Color='Black' ColorKey=NULL ----> This is the problem Lookup cannot find empty string. It should be 1. OrderID = 4 Color='Black' ColorKey=2 OrderID = 5 Color='Black' ColorKey=4
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns." How can I do this?
Insert Query: insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE) SELECT DISTINCT tri_IDENT.IDA AS PPID, tri_Ldg_Tran.CLM_ID AS PTicketNum, tri_ClaimChg.Line_No AS PLineNum, tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT' THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate, tri_Ldg_Tran.Tran_Date AS PEntryDate, tri_ClaimChg.Hsp_Code AS PHCPCCode, tri_Ldg_Tran.Adj_Type, tri_Ldg_Tran.PRS_ID, tri_Ldg_Tran.Create_Time, tri_Ldg_Tran.Adj_Group, tri_Ldg_Tran.Payer_ID, tri_Ldg_Tran.TRN_ID, tri_ClaimChg.Primary_Claim, tri_IDENT.Version FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc = qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN tri_Ldg_Tran RIGHT OUTER JOIN tri_IDENT LEFT OUTER JOIN tri_ClaimChg ON tri_IDENT.Pat_Id1 = tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID = tri_ClaimChg.PRS_ID AND tri_Ldg_Tran.Chg_TRN_ID = tri_ClaimChg.Chg_TRN_ID AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN tri_Payer ON tri_Ldg_Tran.Payer_ID = tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type = tri_Ldg_Tran.Pay_Type AND qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg') AND (tln_PaymentTypeMappings.PTMClientCode = 'SR') AND (tri_ClaimChg.Primary_Claim = 1) AND (tri_IDENT.Version = 0)
What is the best way to design this stored procedure for speed?
I want to pass in a list of IDs and have a return value of a table that contains the ID and its associated value. Is there a way to pass in as table and come out as table?
Hello all, I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.
select distinct ProductType from Product order by ProductType
I have a large table with a datetime column and rows in 30 second intervals everyday for about 3 months. What I want to do is create a select statement that will return only the datetime between 07:00:00.000 and 15:00:00.000 for each day. I imagine this is a common issue. How is this done with tSQL? (I cannot find any similiar posts.)
There are several parameters on a report. One of the parameter is a multi-select enabled parameter and I suppressed the value "All" showing as one of the item in the drop down list, simply by filter out the [bha].[bha].CURRENTMEMBER.LEVEL.ORDINAL to 1, as "(Select All)" is pre-assigned to the drop list when multi-select is enabled and it is confusing to show "(Select All)" and "All" in the drop list. However I have another report which is linked to this report and the value which is required to pass to this report for this parameter is "All". Can I pass the "Select All" as a parameter from the other report? If so, how? Thanks.
Here I need to create a view by using following criteria, there is 3 tables which are Tbl.adminCategory, tbl_adminuser, tbl_outbox respectively. I am working on 2000SQL server
I am treing to create view as following but getting some error.
SELECT tbl_adminuser.adminUserName, tbl_AdminCategory.Name, COUNT(tbl_outbox.msgUserID), FROM tbl_adminuser INNER JOIN tbl_AdminCategory ON tbl_adminuser.adminUserID = tbl_AdminCategory.CatID INNER JOIN tbl_outbox ON tbl_AdminCategory.CatID = tbl_outbox.msgUserID AND tbl_outbox.msgUserID <> 0 GROUP BY tbl_outbox.msgUserID But I am getting error pls correct the view,
I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.
Query:
Select [name], city, state, zipcode From Golf inner join charlist_to_table(@State,Default)f on State = f.str
Function:
CREATE FUNCTION charlist_to_table (@list ntext, @delimiter nchar(1) = N',') RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL, str varchar(4000), nstr nvarchar(2000)) AS BEGIN DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr nvarchar(4000), @leftover nvarchar(4000), @tmpval nvarchar(4000) SET @textpos = 1 SET @leftover = '' WHILE @textpos <= datalength(@list) / 2 BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr) WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SET @pos = charindex(@delimiter, @tmpstr) END SET @leftover = @tmpstr END INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover))) RETURN END GO
I am in need of help to develop a query I have two tables Exams and Exams_lab, that are joined by a field id_exame. I want to return The Exams that has all the dependent rows in Exames_lab with the same value in the status_int field of Exames_lab. Can anyone Help Me? Lets see an example tb_exame
id_exame date_exame
1 07/01/2006
2 08/01/2006
3 09/01/2006 tb_exame_lab
id_exame_lab id_exame desc_exame status_exame
1 1 NORMAL 1
2 1
0
3 2 NORMAL 1
4 2 PROBLEMS 1
5 2 OK 1
6 3 OK 0 in this exemple my query must return only id_exame 2 and 3 because id_exame 1 has two different values on id_status on tb_exame_lab can anyone help me?
I want to return a list of user tables from a database where the rowcount is 0. This will be a 3 step process: (1) truncate all 'New%' tables, (2) load data via ODBC/DTS into 'New%' tables, (3) list all 'New%' tables with zero rows (i.e. those that didn't get loaded, as all tables in the ODBC data source contain data).
I've tried: select left(s2.name,32) as TableName, max(s1.rows) as Records from sysindexes s1 inner join sysobjects s2 on s1.id=s2.id where type = 'U' and s2.name like 'New%' group by s2.name HAVING max(rows) = 0 ORDER BY TableName but of course there are multiple rows in sysindexes and the routine does not reliably return the correct list; for example the data in sysobjects & sysindexes, without the max and group by, might look like: TableNameRows NewARTxn0 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 I was hoping to come up with a single T-SQL statement that I could use in an xp_sendmail situation to email me the results.
Our clients working with the Firefox browser on a Mac are unable to use the Multi-Value parameter drop down lists that the Report Viewer control generates. Of course I realize that the multi-select dropdown lists are not really dropdown option lists using the standard HTML select tag, but are rather tables within div tags with cells that contain spans, inputs, and labels.
Originally the report viewer displayed these lists in the wrong position within Firefox on any platform (Mac or PC). Furthermore, there were other visibility problems with those lists that made it virtually impossible to select a checkbox within the list. Fortunately, Microsoft fixed this problem with the latest version of Report Viewer, which we downloaded from the following link: http://207.46.19.190/downloads/details.aspx?FamilyID=cc96c246-61e5-4d9e-bb5f-416d75a1b9ef&displaylang=en
So currently we have SQL Reporting Services Report Viewer 9.0.21022.8 installed on our web server. And the dropdown lists do appear as expected, and they work properly in Firefox on a PC.
But, when the control is rendered in Firefox on a Mac, the list is not scrollable. The scroll bar that should appear on the right-hand side of the dropdown list, which would enable users to select values toward the bottom of the list, does not appear. That scrollbar is missing in Firefox.
This is likely related to a Firefox rendering issue with the overflow:auto style. There are numerous entries on the web that indicate Firefox for Mac has a problem with overflow:auto. For example: http://www.webdeveloper.com/forum/archive/index.php/t-96958.html http://www.daniweb.com/forums/thread44144.html http://iamthewalr.us/blog/2007/04/20/firefox-on-the-mac/#comment-2321 http://www.errorforum.com/mozilla-firefox-error/3503-will-float-mac-firefox-scrollbars-floating-pop-up-windows.html https://bugzilla.mozilla.org/show_bug.cgi?query_format=specific&order=relevance+desc&bug_status=__open__&id=187435
That being the case, it seems that there should be some workaround to address this, either via a style or through some alternate control. Or perhaps there is a property that we can apply to the ReportViewer control that I'm unaware of which addresses this.
If you know of a workaround, or can suggest an alternate approach that we could implement quickly, please respond. Thanks.
-- populate declare @i int set @i = 1000 while @i > 0 begin insert into dbo.test1 select @i, '1.' + cast(@i as varchar(5)) set @i = @i - 1 end
insert into dbo.test2 select 1, '2.1' union all select 2, '2.2' go
-- create view create view dbo.vw_Test as select1 as QueryID, TestName fromdbo.Test1 union all select2 as QueryID, TestName fromdbo.Test2; go
-- this works as i want, only scans table dbo.Test2 select * fromdbo.vw_Test whereQueryId = 2
-- joining to a table triggers scan of both tables in view: declare @table table (QueryID int) insert into @table select 2;
selectvt.TestName fromdbo.vw_Test vt join@table t on vt.QueryID = t.QueryID
Using the showplan I can see why the optimizer ends up scanning all tables, but maybe there is a way to force it to use the QueryID param evaluation earlier in the filtering.
I have a report that I am building that consist of 4 multi-value selections from four different queries. When I choose "Select All" from all the drop downs and click on "View Reports", it blanks out all the selections from one of the drop downs.
Can someone please point me in the right direction on this problem. The report will run sometimes, but most of the time it will not.
I have do a sql function for return a list of element from a query send in variable.
When I test the function on self I have no problem.
But when I use the function in a sql query I have problem.
example :
Code SnippetSELECT APPLI_SUPPLIER.N_SUPPLIER_ID, APPLI_SUPPLIER.V_SUPPLIER_LABEL, dbo.APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))') AS V_PROCESS_ITEMS FROM APPLI_SUPPLIER INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6))
This is the error :
Server: Msg 557, Level 16, State 2, Procedure APPLI_RETURN_LIST_ITEM, Line 24 Only functions and extended stored procedures can be executed from within a function.
When I do an exec of the function I have this problem :
Code Snippet exec APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))'
This is the function
Code Snippet CREATE FUNCTION [dbo].[APPLI_RETURN_LIST_ITEM] (@QUERY AS VARCHAR(3900)=null) RETURNS varchar(8000) AS BEGIN -- Insert statements for procedure here declare @v_List_ITEM as varchar(8000) set @v_List_ITEM='' if @QUERY is not null Begin declare @cur_Lect_ITEM CURSOR; declare @FUNCTION AS NVARCHAR(4000); Declare @ITEM as VARCHAR(255); SET @FUNCTION = 'set @mainCursor=cursor for ' + @QUERY + ' for read only open @mainCursor'
EXEC sp_executesql @FUNCTION,N'@mainCursor cursor output', @cur_Lect_ITEM output fetch next from @cur_Lect_ITEM into @ITEM while @@fetch_status=0 begin set @v_List_ITEM=@ITEM + ' ; ' + @v_List_ITEM fetch next from @cur_Lect_ITEM into @ITEM end deallocate @cur_Lect_ITEM SET @v_List_ITEM=REPLACE(REPLACE(@v_List_ITEM, CHAR(13), ''), CHAR(10), '') set @v_List_ITEM=left(@v_List_ITEM,len(@v_List_ITEM)-3) End RETURN @v_List_ITEM END
Hi thereGot a interesting problem (depends on a point of view.....).Background :-Agent (within Domino) is run daily basis which extracts data fromDomino Notes application to SQL Server 2000 database. Agent firstremoves ALL contents and then appends ALL data.Reporting Tool is SQL Reporting Services (very cool !!).Problem :-Within Domino Notes, it can have a field which is mult-value fieldi.e. contain multi valuese.g.(from Helpdesk application)!HowTo!!Access Email;!HowTo!!Access the Web;etc..Need to create a view, then use sql to create stored proc, to be usedas the dataset for report within SQL Reporting services.Format. (using above as data as example)!HowTo!!Access Email; (1 row) WWL/SDR/04023/010 (DocID)!HowTo!!Access the Web; (2 row) WWL/SDR/04023/010 (DocID)I have a unique KEY within table called DocID. The report will have 8separate datasets (i.e. using subreports), all linked back to UniqueKey. That's easy.The dataset causing me hassle is the 1 above. How do u split outvalues as separate rows ?Name of field called --> "ImpFunctionsImpacted".Also, notes expert, who's working on the agent, tell's me the data canbe split either as a comma or semi-colon.Any suggestions most welcome.
