MS SQL Server :: Building Dynamic Query Based On Dropdownlist Contents

Building Dynamic SQL Query Strings
Let me start by asking that no one try to convince me to use Stored Procs. The examples below are a lot more simplistic then my real world code and it just gets too complicated to try to manage the quantity of SPs that I would need.
I have an application that displays a lot of data and I've created a system for users to filter the data using checkboxlist controls, dropdown controls, etc. From this, I have a "core" query that selects the fields that display in my GridView. It has a base Select clause, From clause and Where clause. From this I then add more to the Where clause to apply these filter values.
Here's an example "core" query:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCode
From this if a user want's to only display profiles from NC, they could select that from the CBL and the query would be modified to:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.State IN ('NC')
My code would add the last line above since the user specified that they only wanted NC profiles.
This is very simple and I have this already going on with my application. Here's the problem. In order to accommodate all of the various filters, I have to inner join and left join a bunch of various tables. Many times I include tables that have no data to display or filter on and therefore impacts performance. Here's an example:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentID
From the query above, I have included the Agent table that holds the agent's contact information. One of my filters allows the user to type in an agents name to find all profiles assigned to it. Here's what that would look like:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentIDAND Agent.Name = 'Smith, John'
You can see now that it was necessary to have the Agent table already joined into the query so that when I used the agent name filter, it wouldn't crash out on me.
The obvious thing would be to only include the Agent table when searching for an agent name. This is ultimately what I'm looking to do, but I need a solid method to go about doing this. Keep in mind that I currently have 16 tables in my "core" query and many of those are not needed unless the filters call for it.
If anyone has any ideas on how to simplify this process I'm selcome to suggestions. We're using SQL 2000, but are looking to upgrade to SQL 2005, if that makes any difference. I know that the way I do table joins is compliant with SQL 2005 and I'm certainly open to suggestions that will make it forward compatible.
This app is using .NET 2.0 and written in VB.NET. Thanks for any help!

View Replies !

Problem In Saving Page Contents I.e; Textbox And Dropdownlist Values Please Help
Hi all,please have a look of code i am unable to perform save operation onthe asp.net web page.I ahve written a stored procedure. the same code works if all aretextboxes, but some of textbox replaced with dropdownlist box thenthis save operation doesn't occurs. please let me know where is themistake in coding .vb.net code :- Protected Sub btnSave_Click(ByVal sender As Object, ByVal e AsEventArgs) Dim employmentID As Integer =Request.QueryString("employmentID") Dim resourceID As Integer = Request.QueryString("resourceID") Dim projectID As Integer = Request.QueryString("ProjectID") Dim dbconsave As SqlConnection dbconsave = New SqlConnection(HRISDBConnectionString) Dim dbcomsave As New SqlCommand("sp_save_NewHireEmailnotify",dbconsave) dbcomsave.CommandType = CommandType.StoredProcedure dbcomsave.Parameters.Add(New SqlParameter("employmentID",SqlDbType.Int)) dbcomsave.Parameters("employmentID").Value = employmentID dbcomsave.Parameters.Add(New SqlParameter("resourceID",SqlDbType.Int)) dbcomsave.Parameters("resourceID").Value = resourceID dbcomsave.Parameters.Add(New SqlParameter("ProjectID",SqlDbType.Int)) dbcomsave.Parameters("ProjectID").Value = projectID dbconsave.Open() dbcomsave.Parameters.Add("@PreferredFirstName",SqlDbType.VarChar) dbcomsave.Parameters.Item("@PreferredFirstName").Value =txtPreferredFirstName.Text.ToString() dbcomsave.Parameters.Add("@PreferredLastName",SqlDbType.VarChar) dbcomsave.Parameters.Item("@PreferredLastName").Value =txtPreferredLastName.Text.ToString() dbcomsave.Parameters.Add("@CellPhone", SqlDbType.VarChar) dbcomsave.Parameters.Item("@CellPhone").Value =txtCellPhone.Text.ToString() dbcomsave.Parameters.Add("@HomePhone", SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomePhone").Value =txtHomePhone.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressLine1",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressLine1").Value =txtHomeAddressLine1.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressLine2",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressLine2").Value =txtHomeAddressLine2.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressState",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressState").Value =txtHomeAddressState.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressCity",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressCity").Value =txtHomeAddressCity.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressZIP", SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressZIP").Value =txtHomeAddressZIP.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressCountry",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressCountry").Value =txtHomeAddressCountry.Text.ToString() dbcomsave.Parameters.Add("@ArrangementType",SqlDbType.VarChar) dbcomsave.Parameters.Item("@ArrangementType").Value =ddlArrangementType.SelectedItem.ToString() dbcomsave.Parameters.Add("@PracticeGroup", SqlDbType.VarChar) dbcomsave.Parameters.Item("@PracticeGroup").Value =ddlPracticeGroup.SelectedItem.ToString() dbcomsave.Parameters.AddWithValue("@EquipmentNeeds",txtEquipmentNeeds.Text.ToString()) Try dbcomsave.ExecuteNonQuery() lblMessage.Text = "Record saved successfully" Catch ex As Exception End Try dbconsave.Close() End Sub==============================================================================.aspx code :-<table> <tr> <td > <asp:LabelID="lblPreferredFirstName" runat="server" Text="Name(Preferred FirstLast) :" ForeColor="Blue"></asp:Label> </td> <td <asp:TextBoxID="txtPreferredFirstName" runat="server" Text="" BorderStyle="None"></asp:TextBox>   <asp:TextBoxID="txtPreferredLastName" runat="server" Text="" BorderStyle="none" ></asp:TextBox> </td> </tr> <tr> <td > <asp:Label ID="lblCellPhone"runat="server" Text="CellPhone :" ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBox ID="txtCellPhone"runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:Label ID="lblHomePhone"runat="server" Text="HomePhone :" ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBox ID="txtHomePhone"runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressLine1" runat="server" Text="HomeAddressLine1 :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressLine1" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressLine2" runat="server" Text="HomeAddressLine2 :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressLine2" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressState" runat="server" Text="HomeAddressState :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressState" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressCity" runat="server" Text="HomeAddressCity :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressCity" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressZIP" runat="server" Text="HomeAddressZIP :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressZIP" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressCountry" runat="server" Text="HomeAddressCountry :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressCountry" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:Label ID="lblArrangement"runat="server" Text="Arrangement :" ForeColor="Blue"></asp:Label> </td> <td > <asp:DropDownListID="ddlArrangementType" runat="server" Width="160px"DataSourceID="ObjectDataSourceArrangementType"DataTextField="ArrangementType" DataValueField="ArrangementType"OnDataBound="ddlArrangementType_DataBound"> </asp:DropDownList> <%--<asp:TextBoxID="txtArrangement" runat="server" Text="" ></asp:TextBox>--%> </td> </tr> <tr> <td > <asp:LabelID="lblPracticeGroup" runat="server" Text="Practice Group :"ForeColor="Blue"></asp:Label> </td> <td > <asp:DropDownListID="ddlPracticeGroup" runat="server" Width="160px"DataSourceID="ObjectDataSourcePracticeGroup"DataTextField="PracticeGroup" DataValueField="PracticeGroup"OnDataBound="ddlPracticeGroup_DataBound"> </asp:DropDownList> <%--<asp:TextBoxID="txtPracticeGroup" runat="server" Text="" ></asp:TextBox> --%> </td> </tr></table>please help me to solve the issue.Thanks in advance

View Replies !

Building A Dynamic Query Into A Stored Procedure
Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, I tried this but it didnt work;

Code:

ALTER PROCEDURE [dbo].[stream_UserFind]

(

@userName varchar(100),

@subCategoryID INT,

@regionID INT

)
AS

declare @StaticStr nvarchar(5000)
set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,
Users.userName ,UserSubCategories.userID
FROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOIN
SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'

if(@subCategoryID <> 0)
set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID '
if(@regionID <> 0)
set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID '

exec sp_executesql @StaticStr

)

View Replies !

Declare Cursor Based On Dynamic Query
Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

Declare @tempSQL varchar(1000)

--- This query will be generated based on my other conditon and will be stored in a variable

set @tempsql = 'select * from orders'

declare cursor test for @tempsql

open test

This code is not working.

please suggest

Nitin

View Replies !

Setting Dynamic Default Parameter In Dropdown Based On MDX Query
I have a parameter list that is built using an MDX query. I would like to set the default value to the current month. Here is the MDX used.

Code Snippet

WITH

MEMBER [Measures].[ParameterCaption] AS '[Time].[Month].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Time].[Month].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Time].[Month].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Time].[Month].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@TimeYear, CONSTRAINED) ) ON COLUMNS FROM [Pink Flag])

Any help would be appreciated. I have tried to set an expression to build a string that matches the result.

Thanks

Jason

View Replies !

Create A Table Of Contents Based On Report Items From A List Control
What are the options to create a table of contents based on the report items in a List Control? Document Mapping works for online viewing. A table of content would make the report easier to read when it's printed.

Any help is much appreciated. Thanks.

View Replies !

Building A Dynamic Stored Procedure
Hi

I am in the very final stages of
building a dating app for a client, I am totally stuck with the
advanced search page. been googling for days with limited success

For the most basic of purposes I have added a few form fields to my search.aspx page;
county (Dropdown list)
min age (Dropdown list)
max age (dropdown list)
Smoker (check box)
keyword (textbox)

My codebehind passes the vars to a stored procedure
@county = me.county.selectedvalue
etc
My problem is the stored procedure I sort of have the following but I can't get it to run
<code>
ALTER PROCEDURE dbo.TEST_ADVANCED_SEARCH
(@countyID int ,
@MaxAge
varchar(100),
@MinAge
varchar(100),

@smoker tinyint),
@keyword
varchar(250))

AS

DECLARE @SQL Varchar
(4000)

SELECT @SQL =   'dbo.user_accounts.profileComplete,
dbo.user_accounts.countyID, dbo.user_profiles.smoker,
dbo.user_profiles.Age
FROM         dbo.user_accounts INNER
JOIN

dbo.user_profiles ON dbo.user_accounts.userID =
dbo.user_profiles.userID
WHERE     (dbo.user_accounts.profileComplete =
1)'

IF @countyID > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_accounts.countyID = @countyID)'

IF @MaxAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age <= @MaxAge)
'

IF @MinAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age >= @MinAge)
'

IF @smoker > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_profiles.smoker = 1)'

IF @keyword IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Description LIKE @MinAge)
'

EXEC(@SQL)
</code>
If I can get this to work I can add the remaining fields that I need

Am I Missing something glaringly obvious?
Any help or advice gratefully received

Thanks

View Replies !

Building A Dynamic Mailing List
Okay, this has kept me busy for the past couple of days and just about had enough of it. What I'm trying to do is build Mailing Lists using logic instead of just assigning a contact to a mailing list. I've tried and tried but got no where as I think the solution is outside my knowledge scope of SQL.

The reason why I need this is because my client would sometimes like to target a subset of all their contacts and it needs to be dynamic so new contacts can be automatically included/excluded.

So here are the tables ...

tblCountry
CountryId CountryName
-------------------------------------
1 United Kingdom
2 United States of America
3 Canada
4 Austria

tblReligion
ReligionId ReligionName
--------------------------------------
1 Christian
2 Sikhism
3 Athiest

tblContact
ContactId ContactName CountryId ReligionId
------------------------------------------------------
1 John Smith 1 NULL
2 Jane Roberts 1 1
3 Tim Williams 1 1
4 John Doe 2 2
5 Janice McBride 2 3
6 Eddie Lewis 3 NULL
7 Hans Murdoch 4 1

tblRecipientGroup
RecipientGroupId RecipientGroupName
--------------------------------------
1 European Christians
2 All Athiests
3 No Christians

tblRecipientItem
RecipientItemId RecipientGroupId CountryID ReligionID IsExclude
---------------------------------------------------------------------------
1 1 1 NULL 0
2 1 4 NULL 0
3 1 NULL 1 0
4 2 NULL 3 0
5 2 NULL 1 1

Note: IsExclude detemines if the country is excluded instead of the default (included)

... and in brief, If I wanted to obtain all European Christians then 3 items are added to tblRecipientItem to represent the countries and religion to filter on. So the results would look like so ...

tblContact
ContactId ContactName CountryId ReligionId
------------------------------------------------------
2 Jane Roberts 1 1
3 Tim Williams 1 1
7 Hans Murdoch 4 1

... but bear in mind that that we may want to exclude filters like the No Chistians mailing list in tblRecipientGroup.

How would I go about building a query for this?

View Replies !

Building Dynamic SQL In Stored Procs
We are migrating from a file-server Access Database to a SQL server backend and Access front end system. I'm using ADO to access the data off the server but am implementing most of the business logic in stored procedures. All logic was coded in VBA earlier but i'm having to move that to T-SQL for performance issues. In many cases I have dynamically constructed SQL statements in code but I'm having some trouble in T-SQL. How can I do this in T-SQL?

' This is some VB code that shows how the query differs based on a parameter.
If Me![Sorting] = 1 Then
Me![ControlNumber].RowSource = "SELECT [DVD_Projects_Table].[DVD_Number], [Title] & "" : "" & [ID_Number] AS Display,__
[DVD_Projects_Table].Date FROM [DVD_Projects_Table] __
WHERE ((([DVD_Projects_Table].System) = IIf([Forms]![DVD_Projects_Form]![SystemFilter] = 1, ""525"", ""625""))) And __
(([DVD_Projects_Table].Active) = IIf([Forms]![DVD_Projects_Form].[ActiveOnly] = True, Yes, __
[DVD_Projects_Table].[Active]))) ORDER BY [DVD_Projects_Table].Title;"
Else
Me![ControlNumber].RowSource = "SELECT [DVD_Projects_Table].[DVD_Number], [ID_Number] & "" : "" & [Title] AS Display,__
[DVD_Projects_Table].Date FROM [DVD_Projects_Table]__
WHERE ((([DVD_Projects_Table].System) = IIf([Forms]![DVD_Projects_Form]![SystemFilter] = 1, ""525"", ""625""))) And __
(([DVD_Projects_Table].Active) = IIf([Forms]![DVD_Projects_Form].[ActiveOnly] = True, Yes, __
[DVD_Projects_Table].[Active]))) ORDER BY Int(Right([ID_Number],Len([ID_Number])-4));"
End If

This is the ideal sp that would do what I want but it is obviously incorrect. How can I get this logic implemented. I need to construct an SQL query based on the input parameters in a stored procedure.

CREATE PROCEDURE [procDVDProjectsList]
@SortBy as bit,
@ActiveOnly as bit,
@SysFilter as integer
AS
SELECT
CASE @SortBy
/* Display Title first */
WHEN 0 THEN [DVD_Number], [Title] + " : " + [ID_Number] AS Display, [DVD_Projects_Table].[Date]
/* Display Number first */
WHEN 1 THEN [DVD_Number], [ID_Number] + " : " + [Title] AS Display, [DVD_Projects_Table].[Date]
END
FROM
[DVD_Projects_Table]
WHERE
CASE @SysFilter
/* List all */
WHEN 0 THEN
/* List only NTSC */
WHEN 1 THEN [DVD_Projects_Table].[System] = "525"
/* List only PAL */
WHEN 2 THEN [DVD_Projects_Table].[System] = "625"
END
AND
CASE @ActiveOnly
/* List All */
WHEN 0 THEN
/* List only active */
WHEN 1 THEN [DVD_Projects_Table].Active = True
END
ORDER BY
CASE @Sortby
/* Sort Alpha */
WHEN 0 THEN [DVD_Projects_Table].Title
/* Sort Numeric */
WHEN 1 THEN Right([ID_Number],Len([ID_Number])-4)
END

Thanks for your help,

-Sumit Malik

View Replies !

Building A Dynamic Sql Statement Into Stored Procedure
Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, this is what another user suggested but i am having trouble;
ALTER PROCEDURE [dbo].[stream_UserFind]
(
@userName varchar(100),
@subCategoryID INT,
@regionID INT
)AS
declare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'
if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID 'if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID '
exec sp_executesql @StaticStr
)

View Replies !

Building Dynamic Tsql Statements In A Loop
Hi;I would like to read a list of tables from a temp table and then do asql statement on each table name retrieved in a loop, ie:-- snip cursor loop where cursor contains a list of tablesdeclare @rec_count intset @rec_count = 0exec('select @rec_count = count(myfield) from ' + @retrievedTableName)This does not work. SQLSERVER tells me @rec_count is not declared.How can I get the @rec_count populated....or can I?Thanks in advanceSteve

View Replies !

Building Dynamic Sql In Stored Proc Issue
Hi all,

I'm gonna need some help with this one.

I have this stored procedure written up that basically builds a dataset by querying a bunch of tables using outer joins. Our problem now is that it seems it takes a while for the dataset to pull back across the network. We would hence like to filter that dataset by adding on to the query in the procedure dynamically. Heres the query from the proc below:

SELECTN_Client.Prefix,
IsNull(dbo.N_CLIENT.SURNAME, '') + ', ' + IsNull(dbo.N_CLIENT.FIRST_NAME, '') AS Client_FullName,
dbo.N_CLIENT.TITLE,
dbo.N_COMPANY.COMPANY_NAME,
dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_NAME,
dbo.N_DIVISION.DIVISION_NAME,
dbo.N_REF_INDUSTRY.INDUSTRY_NAME,
dbo.N_CLIENT.DIRECT_PHONE,
dbo.N_CLIENT.EMAIL,
dbo.N_CLIENT.TIER_ID,
(SELECT COUNT(Client_ID)
FROM N_Alumni
WHERE N_Alumni.Client_ID = N_Client.Client_ID) AS Alumni,
(SELECT COUNT(Client_ID)
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandB,
(SELECT BAH_EMP_NID
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandBMailer,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'MM' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS MMEMPNID,
dbo.N_CLIENT.SURNAME AS Client_Surname,
dbo.N_CLIENT.FIRST_NAME,
dbo.N_CLIENT.FIRST_NAME AS Client_FirstName,
dbo.N_CLIENT.COMPANY_ID,
dbo.N_CLIENT.DIVISION_ID,
dbo.N_CLIENT.BUSINESS_UNIT_ID,
dbo.N_COMPANY.GROUP_ID,
dbo.N_CLIENT.COUNTRY,
dbo.N_GROUP.GROUP_NAME,
dbo.N_CLIENT.CLIENT_ID,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCEMPNID,
(SELECT NMT_Practice_Code
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Code,
(SELECT NMT_Practice_Name
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Name,
#returnTable.AddlFullName,
#returnTable.AddlEMPNID,
#returnTable.FunctionID as Function_ID,
#returnTable.FunctionName as Function_Name,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOEMPNID,
ISNULL(dbo.N_CLIENT.ARCHIVE_FLAG, 'N') AS Archive_Flag,
dbo.N_COMPANY.TARGET_COMPANY_FLAG,
dbo.N_COMPANY.INDUSTRY_ID,
N_Client.Address1,
N_Client.Address2,
N_Client.Address3,
N_Client.Address4,
N_Client.Address5,
N_Client.City,
N_Client.State,
N_Client.Postal_Code,
N_Client.Country,
N_Client.Region,
N_Client.Office_Code,
N_Client.Broderick_Target_Flag
FROMdbo.N_CLIENT
INNER JOIN
dbo.N_COMPANY ON dbo.N_CLIENT.COMPANY_ID = dbo.N_COMPANY.COMPANY_ID
LEFT OUTER JOIN
dbo.N_GROUP ON dbo.N_COMPANY.GROUP_ID = dbo.N_GROUP.GROUP_ID
LEFT OUTER JOIN
dbo.N_REF_INDUSTRY ON dbo.N_COMPANY.INDUSTRY_ID = dbo.N_REF_INDUSTRY.INDUSTRY_ID
LEFT OUTER JOIN
dbo.N_DIVISION ON dbo.N_DIVISION.DIVISION_ID = dbo.N_CLIENT.DIVISION_ID
LEFT OUTER JOIN
#returnTable ON #returnTable.CLIENT_ID = dbo.N_CLIENT.CLIENT_ID
LEFT OUTER JOIN
dbo.N_BUSINESS_UNIT ON dbo.N_CLIENT.BUSINESS_UNIT_ID = dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_ID
ORDER BY N_Client.client_id
Where upper(title) like '%parameter_value%'
and company_id = 'parameter_value'
and Nmt_practice_code = 'parameter_value' ...............and so on

What we would like to do is to add 15 (where some may be null) input parameters to the definition of the query and then somehow (where the parameter is not null), dynamically add that parameter to the WHERE clause of the query illustrated in italics above. The bold print are examples of 3 of the 15 parameters to be passed into the query by the proc, so basically
title, company_id,Nmt_practice_code would be the 3 parameters being passed into this proc.

So in other words if 9 parameters out of the 15 are passed into the proc, we would like those 9 parameters to be added/built dynamically onto the SQL Query as 9 predicates. I hope I have been clear. Does anyone have any experience with this??? Help!!

Thanks

View Replies !

Checking Contents Of Column And Replacing Contents If First Character Is A Letter
Hi All,I have come up against a wall which i cannot get over.I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.This is how far i have got: casting the varchar column to datetime and comparing. SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"Many thanks in advance!

View Replies !

Select Query Issue (dropdownlist Too)
Hi All,
In my scenario, I am using one sqldatasource with two columns
1. Code_ID
2. Code + ' - ' + CompanyName As Display

Now I bind ddlCompany.datatextfield = Display
ddlCompany.datavaluefield = Code_ID

But the problem is when my CompanyName is NULL value, it does not show my Code too....for example
Code = 1098 CompanyName = NULL

Sql query returns me NULL for this combination....and so my dropdownlist shows blank......can anyone show me the alternative.

Thanking in advance,

View Replies !

Report Based On Filtered Query OR Based On Custom Query
adp on SLQ7

What would be wise to do.

I'm creating a report based on two inner joined tables and i've got a total sum field for each line in the report. (Price * Ordered)

This results in the following sql statement:

Code:

sqlStr = "SELECT *, [Table1].Ordered * [Table1].Price AS LineTotal FROM [Table1] INNER JOIN [Table2] ON [Table1].RecieptNumber = [Table2].Number WHERE ([Table1].RecieptNumber = " & varNumber & ")"

I think I have a few options now.

1. Leave the varNumber out of the query, and save it as a regular query. And now filter the report on varNumber.

2. Write the constructed query with createquerydef to eg. "TmpQueryForReport" And set the reports recordsource fixed to TmpQueryForReport.

3. Set the recordsource of the report to constructed sqlStr on Report_open()

---------------------

Option 1, I got this working but when the database grows (and it will) this might get awfully slow.

Option 2
I had this working before i switched to using MSSQL server 7.00
After the switch i thought, this might be a problem with giving db access rights cause the users might need write rights to the db.

Option 3. Fast and easy???

Option 4 Stored procedures???

View Replies !

Help With Building Query
Hello,

This will probably be trivial and basic for most, but I'm having a hard time trying to figure out the best way to do a SELECT statement. First, let me explain what I have:

Two tables:

Table 1:
Orders
Some of the fields:
ID
PropID
WorkOrderNum
OrderDesc
DateCompleted

Table 2:
OrderDetail
ID
OrderID
TenantName

As you probably have realized, the OrderID in my 'OrderDetail' table corresponds to the ID field in my 'Orders' table. The 'Orders' table contains the order header information, while the OrderDetail contains line items for that order - 1 line item per record.

Here is my SQL statement to retrieve an order when searching by the 'Order Description' (Orders.OrderDesc):

SELECT PropertyLocations.PropertyLocation, Orders.ID, Orders.PropID, Orders.WorkOrderNum, Orders.OrderDesc, Orders.DateCompleted FROM PropertyLocations, ORDERS WHERE PropertyLocations.ID = Orders.PropID AND OrderDesc LIKE '%lds%'

Ok, so now for the 'big' question/problem: I also need to be able to search the 'Tenant Name' field from the 'OrderDetail' table. So what is the best/most efficient way of doing that? The other stipulation about that is that there can be (and usually is) several records/line items (in the OrderDetail table, of course) that contains the same (or similar) data, but I don't want duplicates. And when I say duplicates, all I care about is retrieving a few fields (as you can see from my SQL statement) from the 'Orders' table. Another way to describe what I want is that I want all unique orders that have a 'TenantName' in the 'OrderDetail' table that matches the search criteria. My brain just isn't wanting to figure this out right now, so I was hoping someone could help me out.

thanks.

View Replies !

How To Create INSERT Query For Contents Of Table
Using Sql Server 2005 Express and Management Studio I need to create
a SQL insert statement for the contents of a table (FullDocuments) so
that I can run the query on another server with that same table schema
(FullDocuments) and the contents will automatically be inserted into
the new instance of the FullDocuments table.In Management Studio
I have used "Script Table as" for the create table query. The
second instance of FullDocuments has been created on the remote
server. Now how do I generate an insert query for the contents of
FullDocuments so that the contents can be moved/inserted to the new
instance of the table?Thanks for any help provided.

View Replies !

How Do I List Contents Of File Directory In The SQL Query Analyzer
how do I list contents of file directory in the SQL Query Analyzer

View Replies !

Building A Table From SQL Query
I am hoping someone can point me in the right direction with this.I have query that returns all the colums in a row (SELECT * FROM table WHERE value = 'value') and I need to build a table with this data. Some of the columns may not have values in them, and so I dont want to build a table row for it. I also need to use the column name as the table header. As an example:==============================Column Name || Column Value-----------------||-----------------Column Name || Column Value
-----------------||-----------------
I hope I have explained myself properly. Any help would be greatly appreciated.

View Replies !

A Query Building Question
Hi there,
i have a query building question and was hoping that one of you would know the answer.
Here is what i need to do :(i am using asp.net and ado.net)
I have 1 table where I store thedata, where 5 criteria determine a unique row in this table. Now, this has recently changed as the start date was added. So there potentially can be more than one entry in the table with same 5 criteria, but different start date.
I need to retrieve the row with the latest start date (currently active). The problem arises when the users enter less than 5 criteria. In this case the results may not possess same 5 criteria. Say the user searches based on 2 criteria. Then all the rows possessing these 2 ctieria will be returned, but other 3 criteria might differ with the results set.
But, i only need the latest start date row for each row. So for example, if i searched on 2 criteria, i got back 4 rows, 2 of which possess the same 5 criteria. But between these 2 i only need to display ONE row to the user - the one with the latest date.
How do i build a query? say the table name is tbl, and criteria 1 to 5 fields are called c1 ... c5, and start date field is called start_date.
thanks in advance

View Replies !

Assistance Building A Query...
I am trying to generate some datasets with some queries...With a given series information, it should return PART_NOs that has STD= 1 and a unique price at that particular 'START', and keeping the'TYPE' in consideration...DB examples below:Main DBIDPART_NOSERIESSTD1A-1A12A-2A13A-3A14D-1D15D-2D0Price DBIDPART_IDTYPESTARTPRICE501X100050511X1000040521Y100060531Y1000050542X100050552X1000040562Y100060572Y1000050582X100090etc.main.ID and Price.PART_ID are paired together.So in an example case, lets say I am querying for SERIES A, with TYPEX. A table should be outputted something likePART_NOA-1100050A-11000040A-3100090Note how it skipped printing A2 because the price is the same as A1.I'm really looking for the SQL code here... I can't get it to filter ondistinct price.SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICEFROM MAIN, PRICINGWHERE (MAIN.SERIES LIKE 'A')AND (MAIN.STD = '1')AND (PRICING.PRICE != '')AND (PRICING.TYPE = 'X')AND (MAIN.ID = PRICING.PART_ID)I've been trying to use GROUP BY and HAVING to get what I need but itdoesn't seem to fit the bill. I guess I'm not terribly clear on how Ican use the SQL DISTINCT command...? If I try and use it in my WHEREstatement it gives me syntax errors, from what I understand you canonly have distinct in the select statement? I'm not sure how tointegrate that into the query to suit my needs.Thanks for any help.

View Replies !

Building An Alias From Query

Hi,
Does anybody know if it is possible to obtain an alias from a Query without using Dynamic SQL.

My Problem Looks like this:

CREATE TABLE Source (SourceName varchar(50), SourceAge int)

INSERT INTO Source VALUES ('Mary',41);

INSERT INTO Source VALUES ('John',22);

INSERT INTO Source VALUES ('Tom',15);

INSERT INTO Source VALUES ('Bill',55);

The Statement should look like This:

SELECT A.SourceAge AS A.SourceName FROM Source A;

The Result should look like this:

Mary John Tom Bill
----------------------------------------
41 22 15 55

Thanks in advance
Raimund

View Replies !

ASP - Building A Query - Question
Good Afternoon!

I have a (hopefully) simple question.
I have recently been bumped into an applications developer position. I took a week of ASP training a few months ago, but outside of that my exposure to ASP and SQL has been EXTREMELY limited.

I just undertook my first project using these skills. We have an application where I track "Letters". I fill out a form with information regarding the Letter, and save it so there is a record of the letters existence and what its status is.

Afterwards, we want to update the status, so I find the saved form, make my edits, and save it.

This is where my problem starts.

When I first save the letter, I have no problems whatsoever.
When I save it a second time after making my edits, I get an error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near 'yearID'.
/AuditFlowChart/UpdateAudit.asp, line 206

What I am doing with the ASP is basically created a SQL statement to update the table with the new letter information. I added a line to print out the store proceedure that is being created by the following line

Set rsRecordsetObject = objCOMComponentClassObject.ExecuteSQLStatement(spStoredProcedure,vntSQLServerName,dbDatabaseCatalog)

I print out spStoredProcedure and I get the following

UpdateCorrespondence @p_idCorr = 416,
@p_dtOfLetter = '8/7/2008',
@p_dtRcvdByRespAtty = '2/19/2007',
@p_dtRcvdByAdmin = '2/23/2007',
@p_descOfEntity = 'This is a test 2009 letter. Dated 2/8/2007',
@p_ClientCode = '01074',
@p_MatterCode = '0055',
@p_dtDue = '3/15/2007',
@p_dtEffective = '4/18/2007',
@p_dtYE = '4/8/2007',
@p_respAtty = '0330',
@p_respAttyFull = 'Abelson, H. Edward',
@p_prepParalegal = 'Aldous, Anne Marie',
@p_prepAtty = 'Abromowitz, David',
@p_prepLitParalegal = 'Allegrini, J. Samuel',
@p_dtGivenToParalegal = '4/20/2007',
@p_dtGivenToAtty = '2/28/2007',
@p_dtMemoCirculated = '2/18/2007',
@p_dtRespSentToLitParalegal = '2/10/2007',
@p_dtRespSentToPrepAtty = '2/26/2007',
@p_dtSentToAccountant = '2/15/2007',
@p_memoDSid = 1111111,
@p_respLetterDSid = 2222222,
@p_sideLetterDSid = 3333333,
@p_comments = NULL,
@p_yearID = rsLetterDetail1('yearID'),
@p_opinion = 1,
@p_complete = 0,
@p_userID = 'GSSUSO1517'

When I run the above thru Query Analyzer, I get the following:

Server: Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near 'yearID'.

So now I know where my error lies, but I am not sure where to look next. Does anyone have any ideas? Is there a problem I am not seeing with the query?

Any help would be wonderful and much appreciated.

View Replies !

Building Query Graphically
Hi,

I'm using 2005 management studio express.

Can I use the graphical query builder to specify tables from more than one database?

I know I can do this in sql in the format database..table.column but in the query builder it only shows tables from the current database. Can you get around this?

Thanks

View Replies !

Dynamic Join Based On Column Value??
Hi,

I have three tables.

The "Master table has a recordid, a masterID, a "IsSubField" and other stuff.

I need to do a join to a second table based on the MasterID...

However for each record, if the "IsSubField" has a True value then it has to use table A to JOIN to, where as if it's False, then it uses table B.

Make sense?

Anyone got any pointers?

Thanks in advance

James

View Replies !

Help With Parameterized Query Building Dataset
I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value.
This is the code for the Method to return a datareader

public SqlDataReader GetOrgID()
{
Singleton s1 = Singleton.Instance();
Guid uuid;
uuid = new Guid(s1.User_id);
SqlConnection con = new SqlConnection(conString);
string selectString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID";
SqlCommand cmd = new SqlCommand(selectString, con);
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;

con.Open();
SqlDataReader dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

return dtr;

This is the code trying to accomplish the same thing with a Dataset instead.

public DataSet organID(DataSet dataset)
{
Singleton s1 = Singleton.Instance();
Guid uuid;
uuid = new Guid(s1.User_id);
string queryString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID";
SqlConnection con = new SqlConnection(conString);

SqlCommand cmd = new SqlCommand(queryString, con);
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;

SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;

adapter.Fill(dataset);
return dataset;

}

Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well.
So assume that the Guid is a valid entry..I should return a valid dataset but its null.

View Replies !

Help: Need If Statement In Sql-query For Building A View
I have 3 tables with info, which I want to combine in a View. The database is from an original helpdesk application, which we use. I would like to crteate a view, where I see an overview of all open incidents, where I would like to see a coupled configuration, if available.

Table Inc:
IncIdt (unique incident number)
IncDisc (Incident discription)
bla
bla
bla

Table Cfg:
CfgIdt (unique configuration item number)
CfgDisc (config discription name)
bla
bla
bla

Table IncCfg (crosstable between Inc and Cfg):
IncIdt (unique incident number)
CfgIdt (unique configuration item number)

In IncCfg are only these entries, where a coupling between an Inc and a Cfg is being made. All other IncIdt are not available. How can I create a view, with the columns IncIdt, IncDisc, bla, bla, CfgDisc. When I try an ordinary query, it results in only those incidents, which have a coupled Cfg and not all incidents. I need all incidents, where the column CfgDisc is taken from the table Cfg, or if no coupling is being made, the value of CfgDisc is null or empty. I prefer a life view, instead of creating a temporary table with a job, followed by a view on this table.

I am using MS SQL Server 2000.

View Replies !

Dynamic WHERE Operator Based On User Input
Let's say I have a table with 3 fields: an ID field (primary key, set as an id field, etc.), a Name field (nvarchar50), and an Age field (int). I have a form that has three elements:
DropDownList1: This drop down list contains 3 choices- "=", ">", and "<".
Age: This text box is where someone would enter a number.
Button1: This is the form's submit button.
I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1.
The code-behind is shown below. The part I'm confused about is that if I load this page, the query works the -first- time. Then, if I try to change the parameters in the form and submit it, I get the following error:
"The variable name '@Age' has already been declared. Variable names must be unique within a query batch or stored procedure."
Any help would be appreciated.
Here is what I'm using in my code behind:
protected void Button1_Click(object sender, EventArgs e) { System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.Append("SELECT * FROM People WHERE Age "); switch (DropDownList1.SelectedValue) { case "=": sb.Append("= "); break; case ">": sb.Append("> "); break; case "<": sb.Append("< "); break; } sb.Append("@Age"); SqlDataSource1.SelectCommand = sb.ToString(); SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, Age.Text); }

View Replies !

XML With Dynamic Attributes Based On Column Values
I have been banging my head against the wall for TWO days. I have
gone back and forth with a very patient guy on thescripts.com. You
can see the ridiculous thread here

http://www.thescripts.com/forum/threadnav628777-1-10.html

If you have time, at least peruse that so we don't go in circles.
Anyway, if you guys can help me solve this, I will be forever
grateful!!

Here is the "basic" problem:

Here is an example for TWO different entities in the database.

EntityID XmlFieldName Value
1 City Austin
1 State TX
1 Country US
2 CityName Los Angeles
2 StateCode CA
2 CountryCode US
2 Zip 111111

Here is how the two different results should be

where EntityID = 1
<Address City="Austin" State="TX" Country="US"/>

where EntityID = 2
<Address CityName="Los Angeles" StateCode="TX" CountryCode="US"
Zip="111111"/>

Notice how the attribute names (City or CityName, State or StateCode,
etc) are based off the XmlFieldName and I don't know in advance what
the possible values will be? I also don't know how many attributes
there will be, but they can be different per entity, depending on how
they have set up an address in our application.

Another thing to note, is that I kind of have this working in an sproc
using PIVOT and generating a table with the values that have the
correct dynamic column names (you can see this on my other thread I
posted above) but I REALLY need this to not use dynamic SQL (so can
use it in a function) if possible and be able to be used in a select
statement, whether it be a temp table as I would like to get a result
set back that I can do a FOR XML RAW on. If this is confusing, it is
because I am delerious. OR is there a way to return a table from an
SPROC that has dynamic columns built?

Please help!! Thanks so much!!!

Brian

View Replies !

Is Possible To Create Dynamic Reports Based On Cubes?
Hi,

I would like to know if is possible to create dynamic reports based on cubes. What i mean is,after creating a cube with a couple of dimensions and measure if is there any way to give the normal users on the report manager or report builder the freedom to choose their own dimensions/measure so they can output the report with the choosen criteria.

Thanks.

View Replies !

Is Possible To Create Dynamic Reports Based On Cubes?

Hi,

I would like to know if is possible to create dynamic reports based on cubes. What i mean is,after creating a cube with a couple of dimensions and measure if is there any way to give the normal users on the report manager or report builder the freedom to choose their own dimensions/measure so they can output the report with the choosen criteria.

Thanks.

View Replies !

Dynamic Data Set Based On Calculated Fields

Hi,

I have three data sets. Data Set A is bound to my table in my report. There are also Data Sets B and C. All three data sets have the identical columns. I have setup data set A so that all of its fields are "Calculated". The calculation for each field will either pick the field from data set B or data set C based upon a parameter called BorC. So the calcualted fields in data set A look like the following:

=iif(Parameters!BorC.Value, First(Fields!MyField.Value, "dataset_B"), First(Fields!MyField.Value, "dataset_C"))

Problem is when I run the report I get internal error. Is it possible to populate one data set from the fields of another data set? If so is there some trick to doing this? Stripping this example down to one column in each of the data sets, and then just directly setting data set A from either B or C still gives me the same error. SO I set the one field in data set A to the following and it still doesn't work:

=First(Fields!MyField.Value, "dataset_B")

OR

=First(Fields!MyField.Value, "dataset_C")

This leads me to believe there is some fundamental problem with using another data set in a Calculated field. Since I think I have done this before I am convinced I am doing sommething wrong.

Any advice you have for me would be greatly appreciated!

Thanks.

View Replies !

Building/Issueing A Query With A Field With A Quote In It?
Using a language that using A4GL to connect to the database. From within the language(COBOL) we have the ability to add a simple query. All is fine with one exception. Some of the fields might have a quote mark in it.
we have a field in particular that a lot of our clients like to put an apostrophe in. such as 0001A'06, 2'11" and so on. Now from inside our programs and using the simple query we add something like 'where ap_id = '0001A'06' which i know is an error but is there a way around this or a way to make it work?

View Replies !

Building A Query In A Stored Proc With String
This should be an easy enough answer to find if I just knew what to search on!

I am building a query within my stored procedure based on what parameters are passed in. For example, suppose I have a table with first name and last name. I can call the sp with either first name or last name or both, so I build a query accordingly that says:
select * from tblNames where FirstName = 'Hannah'
or
select * from tblNames where LastName = 'Montana'
or
select * from tblNames where FirstName = 'Hannah' and LastName='Montana'

My problem is putting the single quotes around the variable value.
SELECT @whereClause = @whereClause + ' AND tblNames.LastName=' @LastName-with-singlequotes-around-it

Thanks

View Replies !

Assistance Requested Building View Query
The View SQL below takes 1:50 minutes to execute. I am looking for a way to improve processing time and keep the view select statements as simple as possible.

One part of the view that could be restructured is the lookup for the current term (TERM_TBL - bolded). When I hard code the current term my execution time drops to about 13 seconds

There are three possible values for the current term; one for each of our three acedemic careers (UGRD, CONT, & EXED). Could this information be looked up once and then each enrollment selected based upon the appropriate academic career current term value? If so, how would it be restructured? Currently, the lookup is done for every enrollment record.

There are no common fields between the NAMES table and the TERMS_TBL.

Code Snippet

SELECT A.EMPLID, A.LAST_NAME_SRCH, A.FIRST_NAME, A.MIDDLE_NAME, A.LAST_NAME

FROM NAMES A

WHERE A.EMPLID IN (

SELECT DISTINCT B.EMPLID

FROM STDNT_ENRL B

WHERE B.INSTITUTION = 'AAAAA'

AND B.STRM >= (

SELECT DISTINCT T.STRM
FROM TERM_TBL T
WHERE T.INSTITUTION = B.INSTITUTION
AND T.ACAD_CAREER = B.ACAD_CAREER
AND T.TERM_BEGIN_DT <= GETDATE()
AND T.TERM_END_DT >= GETDATE())

AND A.EMPLID NOT IN (

SELECT DISTINCT C.EMPLID
FROM SRVC_IND_DATA C
WHERE C.INSTITUTION = 'AAAAA'
AND C.SRVC_IND_CD = 'DPL' )

AND A.NAME_TYPE = 'PRI'

AND A.EFFDT = (

SELECT MAX(D.EFFDT)

FROM NAMES D

WHERE D.EMPLID = A.EMPLID

AND D.NAME_TYPE = 'PRI'

AND D.EFFDT <= GETDATE() )

Any suggestions will be gladly accepted. Keep in mind that the new and much improved must be legal within the context of a view select statement.

Thanks,
Steve

View Replies !

Dynamic CASE Statement Based On List Of Dates
I have the following table of data. I need to take a date from a large table and do the following case:CASEWhen date < date(0) Then '0'When date between date(0) and date(1) Then '1'When date between date(1) and date(2) Then '2'When date >= date(3) Then '3'What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)I hope I am making sense. Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date. Any ideas how to manipulate these dates into a case statement? Don't worry about the second table below. I just wanted you to see why I need to return an int from the Case function.thanksMilton

Dates Table

Date

4/1/2003

1/1/2006

4/2/2007

Fee Table

Date
Period
Class
Fee

1
Daily
True
329

1
Half Day
True
178

1
OT
True
49

1
Hourly
True
41

1
Daily
False
156

1
Half Day
False
86

1
OT
False
27

1
Hourly
False
19

2
Daily
True
355

2
Half Day
True
192

2
OT
True
50

2
Hourly
True
44

2
Daily
False
171

2
Half Day
False
92

2
OT
False
28

2
Hourly
False
21

3
Daily
True
364

3
Half Day
True
197

3
OT
True
51

3
Hourly
True
45

3
Daily
False
175

3
Half Day
False
94

3
OT
False
29

3
Hourly
False
21

View Replies !

Dynamic Build SQL In Store Procedure Based On Select
I have a department table like this:DeptID Department ParentID, Lineage1 HR NULL (2 Temp1 1 (1,3 Temp2 2 (1,24 PC NULL (I have a deptmember table like this:DeptID MemberID IsManager1 1 Y4 1 YI need to query table to get all department belong to MemberID 1 withall children departments.My thought is:1. Do Select * from deptmember where MemberID=1 and IsManager=Y2. Loop thru this table to build SQLWhere Lineage like '%1' OR Lineage like '%4'3. Select * from department using where statement from step 2.How do you loop thru results from step1, Do I need to use a cursor?Thanks,HL

View Replies !

How To Design Dynamic Reports Based On User's Choice
Hi all,

I'm a beginner to Report Services, and have tons of questions.

Here's the first one:

if the reports are created based on the condition that the user selects, how can I create the reports with Report Services?

For example,

the user can select the fields that will be shown on the reports, as well as the group fields, the sort fields and restrict fields. So I would not be able to pre-create all possible reports and deploy them to the report server, and I think I should create the reports dynamicly based on what the user select.

Could someone tell me how to do it (create and deploy the reports)?

Thanks a million!

Jonee

View Replies !

Dynamic Coloring Of Cells Based On The Multi-value Selction.
Hello All,

I am working on a report where in I need coloring of cells based on a Multivalue drop down selection.

Scenario:
1.) I have a exception flag as a column/field in the database which list the postion of the Exception and also the Value of the exception.
   Example: ExcpFlag: 012024504213
             Each digit in this ExcpFlag represents two values 0 being the first column in the table and 0 is Good data. 1 being the 2nd column and 1 is null data. 2 is 3rd column and Missing SSN...etc.
     We have separate list table which list all the exceptions with key and value pairs.

ExcpTable has 0 | GoodData
                       1 | NullData

   2 | Missing SSN's and etc.

2.) The Stored Procedure brings me the records based on the exception flag and file ID.
    Example: Two Parameters to SP.

Exec sprcFileException fileID:'1025' and ExcpFlag: '0,1,3,7,9' gives me all the records for that FileID and also the ExcpFlag.

3.) Now what I need is: Dynamic Coloring of the cells which has only '0,1,3,7,9' in the report for the fileID: 1025.

   I have about 24 fields/columns in the report and data displays for only '0,1,3,7,9' exception types from the selction.
Problem: I want to color all the cells which have the '0,1,3,7,9' exceptions on the report.

Please help me figuring out this issue.

Thanks in Advance.

Deepak

View Replies !

Building Query String For OPENQUERY To Linked Server
HELP! Does anyone know if I can build an OPENQUERY query string on the fly?
=====
I have a working linked server. If I pass ...

SELECT * FROM OPENQUERY(CD0IDEAS, "SELECT * FROM NULLID.PRODDTALIB.WEEKRVL6 WHERE FISYR = 1999 AND FISMO = 10 AND FISWK = 2")

...it works great. If I try to dynamically build the values, and pass ...

DECLARE @QueryString varchar(90)

SELECT @QueryString = 'SELECT * FROM NULLID.PRODDTALIB.WEEKRVL6 WHERE FISYR = ' + CONVERT(VARCHAR(4), @Year)+ ' AND FISMO = ' + CONVERT(VARCHAR(2), @Month) + ' AND FISWK = ' + CONVERT(VARCHAR(1), @Week)

SELECT * FROM OPENQUERY(CD0IDEAS, @QueryString)

... it fails ...
Line 27: Incorrect syntax near '@QueryString'.

View Replies !

Building Query Strings Within Stored Procs - Good Or Evil?
I ran across this technique being used in an application the other day. It seems not a good idea to me. What do you think?

1. The proc builds a basic query, nothing real fancy, into a string variable called @SQL defined as varchar 2000. Depending on the result desired, the group by clause can be one of three different sort orders.

2. The string is executed via EXEC @SQL.

It seems to me that the whole process can eliminate the EXEC and just use some other construct. All the parameters are passed in via the initial call to the stored proc. It also seems that every time this is executed it will result in a new query compile and cache useage, no matter what. Wasteful? Should I take the developers aside and knock heads? I think the app was coded by some folks who were rookies then but may be willing to crack open their code. Or, am I the one that is a rookie?

Thanks for your inputs.

View Replies !

View And Print Contents Of Query Analyzer (was &"Big Time Newbe Needs Help&")
Hello, I need some help, I am in school right now and I am in a SQL server class. We have been working in the query analizer making a database. Well I have to print out everything that I have typed. But I want to view it first. How do I do that?

Sorry I did a search and couldnt find anything.. Probably cause I dont really know what to search for or look under. Thanks for your guys time.

~Matt

View Replies !

SP To Perform Query Based On Multiple Rows From Another Query's Result Set
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users            SELECT u.userName,u.userID                FROM tableU u               LEFT JOIN tableW w ON w.workgroupID = u.workgroupID               WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
               FROM tableU u
               LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
               WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!

View Replies !