Hi.
I am working on a procedure which can filter data through three tables.
First Table is User
---------------------------
UserName UserID
---------------------------
Second Table is Customer
---------------------------
UserID Cust_Site_Int_ID
---------------------------
Third Table is l Details
---------------------------------------------------------------
Cust_Site_Int_ID Created_Date Status
----------------------------------------------------------------
I want to create a stored procedure which can filter through these tables. I will have four input fields in my application to accept parameters. The four parameters will be UserName, two date fields which specifies a range of dates and status. I want the filter to work like this, if only one parameter is provided it will use only that parameter if additional parameter is given it will use the AND logic.
I tried to do a Proc but it is not working any way here it is. So you will get a better idea of what I am trying to achieve.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
ALTER PROCEDURE [dbo].[FilterServiceTickets]
-- Add the parameters for the stored procedure here
@userName varchar(20)= NULL,
@startDate datetime =NULL ,
@endDate datetime = NULL,
@status int = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT SERVICE_TICKET.SERVICE_TICKET_CODE, SERVICE_TICKET.PROBLEM_REPORTED, SERVICE_TICKET.CONTACT_NUMBER,
SERVICE_TICKET.STATUS
FROM aspnet_Users INNER JOIN
BuildingAddress ON aspnet_Users.UserId = BuildingAddress.UserID INNER JOIN
SERVICE_TICKET ON BuildingAddress.Customer_Site_Int_ID = SERVICE_TICKET.CUST_SITE_INT_ID
WHERE ((aspnet_Users.UserName = @userName)OR (@userName=NULL))
AND ((SERVICE_TICKET.STATUS = @status) OR (status=NULL))
AND ((SERVICE_TICKET.CREATED_DATE >=@startDate AND SERVICE_TICKET.CREATED_DATE <=@endDate ) OR (@startDate=NULL)OR (@endDate=NULL)OR(@startDate=NULL AND @endDate=NULL))
END
Just curious. The exec plan is the same for both qry's, and they both show the same estimated row counts @ the point of question in the exec plan. The exec times are roughly the same, any variances I'm attributing to db load from other things going on, since any benefits of one over the other are not consistent from execution to execution. So is there any benefit to filtering in the join conditions vs. the where clause? My thinking was that by filtering earlier in the qry (when joining) as opposed to "waiting" to do it in the where clause, the rest of the qry after the join would inherently be dealing w/a smaller result set for the rest of it's execution, thus improving performance. After the exec plan checking I did, I guess I was wrong. Seems that Sql Server is intelligent about such filtering when analyzing the entire qry, and building its execution accordingly. The execution plan for both qry's showed the same where clause argument for the tables being joined.
Filtering in where clause....
Code:
select... FromtProject p with (noLock) jointProjectCall pc with (noLock) on P.ID = pc.project_id jointStore S with (noLock) on pc.store_id = s.id jointZip Z with (noLock) on Z.zip5 = s.zip5 jointManager M on M.ID = case ... end leftjoin ( selectprojectCall_RecNum as RecNum, sum(answer) as HoursUsed fromtCall C whereAnswer > 0 and question_id in (1, 2) group by projectCall_Recnum ) as C on pc.recnum = c.recnum wherepc.removed = 0 andp.cancelled = 0 andp.deleted = 0 ands.closed = 0 ands.deleted = 0 andyear(getDate()) between year(P.startDate) and year(P.expDate)
Filtering in joins...
Code:
select... FromtProject p with (noLock) jointProjectCall pc with (noLock) on P.ID = pc.project_id and pc.removed = 0 and p.cancelled = 0 and p.deleted = 0 and year(getDate()) between year(P.startDate) and year(P.expDate) jointStore S with (noLock) on pc.store_id = s.id jointZip Z with (noLock) on Z.zip5 = s.zip5 and s.closed = 0 and s.deleted = 0 jointManager M on M.ID = case ... end leftjoin ( selectprojectCall_RecNum as RecNum, sum(answer) as HoursUsed fromtCall C whereAnswer > 0 and question_id in (1, 2) group by projectCall_Recnum ) as C on pc.recnum = c.recnum
I have two tables, one with data for one type of ID (call it key1) and a table where this ID (call it key2) is transformed to another. It is not one-to-one match with these types of ID and I want to check those key2 cases that have two or more key1 linked to it.
It is simple enough and for the easiest check I don't even need table1 to run it as table2 has both key1 and key2 variables.
However, not all doubles are of identical worth. Table1 (that has only key1) has a year variable. I am interested in doubles that have same year variable, ie. in table1 there are two key1 cases with the same year variable that are linked to one key2 case in table2.
So in essence in table1 I have key1, year and in table2 I have key1, key2 and I am interested in those key2-cases that have more than one key1 linked to it where years are the same.
SELECT query.key2 FROM ( SELECT DISTINCT a.key1, b.key2 FROM table1 AS a JOIN table2 AS b ON a.key1=b.key1 JOIN table1 AS c ON a.key1=c.key1 WHERE a.year=c.year) AS query GROUP BY query.key2 HAVING COUNT(*)>1
I tried it joining table1 twice and fiddling around with various JOIN and WHERE clauses (the one on show being the simplest and most naive one) but the query still returns key2-doubles whose key1 cases are linked to different years. It is simple enough if you give a distinct year value in where clause (and drop second table1 join as unnecessary) but I don't want to go through all years manually one by one. I was thinking some kind of iterative loop that changes the value of the year in where clause could do the trick (and be heavy computationally) but I don't really know how to go around doing it, haven't done any loops in SQL ever.
I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:
SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30 After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.
I have the following code: Imports System Imports System.Data.SqlClient
Namespace TestWeb
Partial Class araging
Inherits System.Web.UI.Page
Private _dt As DataTable Private _connstr As String
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer. <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub
#End Region
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim _connstr As New SqlConnection(ConfigurationManager.ConnectionStrings("ESQLConn").ConnectionString)
Dim sqlCommand As SqlCommand = New SqlCommand("csp_rpt_ack_form", _connstr)
Dim ds As New DataSet objaDapter.Fill(ds) mainGrid.DataSource = ds mainGrid.DataBind()
Response.Write(_connstr)
End Sub
End Class
End Namespace I am trying to pass a value to the parameter that it contains. What Im I doing wrong? I keep on getting the following message."An SqlParameter with ParameterName '@v_order_doc_no' is not contained by this SqlParameterCollection." Thanks guys!
Hello,I have 3 optional text boxes. I don't know if the best way to set upthe table would be a field for each box, since this would leave gaps inthe table if the user only filled in one box. Is there a good methodto use?? This is kind of like storing check box values, in that therecould be multiple answers.
Hola chicos!!! Estoy empezando a ver sobre la opcion que maneja el RS de envio de emails...podrian apoyarme con un manual o indicandome como o que configurar para que esto fuciones pliz!... o es con el manejo del Sql , crear un procedure y programar una tarea que se ejecute cada cierto tiempo para que se ejecute el proc ????
Hi Friends, i have a question table and i want to filter this table using the following stored procedure but i cannot take a resultset ; (there is no error)
Stored Procedure is here:
CREATE PROCEDURE [dbo].[SP$AllQuestion] @Session nchar(10), @Class nchar(10), @Unit nchar(10) AS BEGIN SELECT L.QID, L.CLASS, L.SESSION, L.UNIT, L.CONTENT as QUESTION FROM tblQUESTION L WHERE @Session IS NULL OR L.SESSION = @Session) AND (@Unit IS NULL OR L.UNIT = @Unit AND (@Class IS NULL OR L.CLASS = @Class) AND ((@Session IS NULL AND @Unit IS NULL ) OR (L.SESSION = @Session AND L.UNIT = @Unit)) AND ((@Session IS NULL AND @Class IS NULL ) OR (L.SESSION = @Session AND L.CLASS = @Class)) AND ((@Unit IS NULL AND @Class IS NULL ) OR (L.UNIT= @Unit AND L.CLASS = @Class)) AND (L.UNIT = @Unit AND L.SESSION = @Session AND L.CLASS = @Class) END
i dont know what my fault is ; to your opinion what i have to do? thanks from now on...
Tell me please how can I make a query that updates one table using data from another but not all and not filtered by its field but filtered by a field in a linked table. I've tryed to use usual construction but it doesn't work: UPDATE arch_exm SET reg_num = t1.reg_num, dscp = t1.dscp, checktype = t1.contr_type, mark = t1.mark, contr_date = t1.d_kontr, semester = t1.semester, n_register = t1.n_regist FROM (ses_curr INNER JOIN students ON ses_curr.reg_num = students.reg_num INNER JOIN groups ON students.stgroup = groups.stgroup AND students.stgroup = groups.stgroup INNER JOIN specialties ON groups.speciality = specialties.speciality AND groups.speciality = specialties.speciality) t1 WHERE (t1.arch_store = 1) AND (t1.depart = @depart) So I need to use for update source a table is produced by joining several ones and use a filter in one of its fields but in syntax FROM <table> <name> is supported only one table but not a result of joining. Tell me please how can I describe in SQL using after FROM a result of joining with a filter.
I can't find any info on what the 'IN' operator does when filtering data. What's the syntax? I'm hoping this operator will behave similar to the IN operator in sql. I want to pass in 1 or more values that I'd like my data filtered by. If I say <field> IN <single value> it works, but if I say <field> IN <value1>,<value2> I get back nothing at all. Any claification on the use of this operator and the correct syntax for my value list would be greatly appreciated!!
<br><br>I obtain table names from a database and pass them to a dropdownlist. Based on user selection, I want to pass each table name to a query.Here is an extract from my code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="select * from @dDTable"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="dDTable" PropertyName="SelectedValue" DefaultValue="product" Direction="InputOutput" Size="15" Type="String" /> </SelectParameters> </asp:SqlDataSource>I keep getting this error: Must declare the table variable "@dDTable".Please does anyone knows the best way to go about this?
I'm designing a sales report. The user would like to be able to look at individual customers against total sales in a report.
I have a parameter that will allow the user to select from a list of customers. Then I need a column that will show that customers sales and the next column will show total sales. If I filter the data set by Customer I will only get that customer's sales. Can I filter on individual columns?
I tried something like
=Sum(IIF(Fields!Name.Value=Parameters!VendorName.Label,Fields!Coil_Weight.Value/code.Expon(Parameters!Units.Value),0))in the expression but that doesn't seem to work.
I was writing a query using both left outer join and inner join. And the query was ....
SELECT S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P LEFT OUTER JOIN Production.Categories AS C ON C.categoryid = P.categoryid) ON S.supplierid = P.supplierid WHERE S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table. The makeid and orderdate belong to the OrderDetails table. The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.
This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.
Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.
I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.
I have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible.I have included a simplified view definition to demonstrate the issue...
ALTER VIEW MyView AS WITH ContactCTE AS( SELECT Col1, Col2, TenantId
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores' storeid doc article storeid doc article ALL 0010 001 101 0010 001 ALL 0010 002 101 0010 002 ALL 0011 001 102 0011 002 ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:- GO ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details] @Class_Id int AS BEGIN
If I join Table1 to Table2 with a WHERE condition, isit the same if I would join Table2 to Table1 consideringthat the size of the tables are different.Let's assume Table2 is much bigger than Table1.I've never used MERGE, HASH JOINs etc, do any ofthese help in this scenario?Thank you
Is it possible to insert data into a table from a temporary table that is inner join? Can anyone share an example of a stored procedure that can do this? Thanks, xyz789
Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:
I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.
That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.
Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.
I have one main Table "MainTable" which I want to relate with "subTable1, subTable2, ..." in such a way that:
"ith subTable" have to be related/joind on "ith row" of the "MainTable", "jth subTable" have to be related/joined on "jth row" of the "MainTable" and so on...
What I want Actually?
I want that when ever I delete a Record in the "MainTable", The corresponding "subTable" have to be deleted Itself.
I thought a solution that, I can cerate a Trigger on Delete of the "MainTable" and it would delete the corresponding "subTable". But I dont know how to ceate that.
Secound solution what I thought is, that may be there is some majic power in the Table Joinings. That I might join "MainTable" row with "subTable" ( ofcourse that I dont know either :))
So my question is, that what is the actual solution for my problem?
What ever solution is please give me a sample also with that. Like in a Trigger how can I write some Expression which can delete the "subTable" for the Currunt delete Row.
Hi, how can i make optional relationship? for example: In table A, there is column 1, column 2, column3. In table B, there is column 4, column 5 and column 6. column 1 and column 2 are primary keys for table A and table B. The relationships between table A and table B are column 2 and column 5; column3 and column 6. but optional (ie. when data exists in column 2, then column3 is null) how can i set the relationship? because one of the columns data is null each time, error always occurs.
I have a select proc that will take a bunch or criteria parameters. Based on how many are not null I would like to decide how many inner joins to do. for example:
select H1.Priceid as HotelPriceId,H2.Priceid as AirPriceId, H1.VendorPackageId from ( select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join ( select VendorPackageId from ValidVendorPackages where Vendor = 'SBT' and Sitecode = 'system' and PackageType = 'AHCF' )HB on HA.VendorPackageId = HB.VendorPackageId and ( CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = 'LAS' and LengthOfStay = 5 and Ages = 'A2' and ComponentType = @ComponentType_1 and ValidItemType = @ValidItemType_1 and ItemValue = @ItemValue_1 ) )
)H1 INNER JOIN ( select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join ( select VendorPackageId from ValidVendorPackages where Vendor = 'SBT' and Sitecode = 'system' and PackageType = 'AHCF' )HB on HA.VendorPackageId = HB.VendorPackageId and ( CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = 'LAS' and LengthOfStay = 5 and Ages = 'A2' and ComponentType = @ComponentType_2 and ValidItemType = @ValidItemType_2 and ItemValue = @ItemValue_2 ) ) )H2 on H1.Priceid = H2.priceId Inner Join ( select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join ( select VendorPackageId from ValidVendorPackages where Vendor = 'SBT' and Sitecode = 'system' and PackageType = 'AHCF' )HB on HA.VendorPackageId = HB.VendorPackageId and ( CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = 'LAS' and LengthOfStay = 5 and Ages = 'A2' and ComponentType = @ComponentType_3 and ValidItemType = @ValidItemType_3 and ItemValue = @ItemValue_3 ) ) )H3 on H2.Priceid = H3.priceId
if values are only passed in from @ComponentType_1,@ValidItemType_1,@ItemValue_1 I dont want to do any inner joins.
If its passed in for @ComponentType_1,@ValidItemType_1,@ItemValue_1 & @ComponentType_2,@ValidItemType_2,@ItemValue_2 I want to do the first Inner Join.
and of course if I get all 3 sets of criteria I want to do both the inner joins. I know I can cut and past this thing 3 times with an if statement but that isn't going to be practical.
Is it possible to define an argument as optional for a UDF? I have a financial calculation that may or may not require a defined date range depending on the status of an individual item. Is there a way to avoid requiring the date range where it's not necessary?
AS SELECT * FROM myTables with joint WHERE DepartureDate = @DepartureDate AND ReturnDate = @ReturnDate AND MaxPrice = @MaxPrice AND Country = @Country
If a parameter is null, I dont want it to be in the WHERE clause. I dont want to use a string parameter and execute a "string" select. I dont want to use a combination of IF to check everything ...