SELECT - Dynamically Determine Fields To Return

Dec 6, 2004

Hello,





I m writing a stored procedure to query a table Population that has the following fields - CityId, CityName, Plus0, Plus10, Plus20, Plus30, Plus40, Plus50, Plus60, Plus70, Plus80. The field Plus0 contains the number of people of age > 0 living in the city, Plus10 contains the number of people of age > 10 living in the city and so on. Given the city id and age groups like 20To40, 50To60, 40Plus, etc., I should be able to query the number of people in the city corresponding to the requested age group. Note that if the requested age group was 20To60, I need to make use of only 2 fields Plus20 and Plus60 in the table to compute this value. And if the requested age group was 40Plus, then I need only the value in the field Plus40. The problem is that a wide variety of age groups can be requested like 0Plus, 10Plus, ... , 80Plus, 0To10, 0To20, 0To30, .... 70To80.





Which is the most effecient way to handle this ?





1. Have a stored procedure that returns all the fields even though only 1 or 2 of them would be actually used ?





In this case, if I returned data for a large number of cities then there would be a lot of unnecessary information that was returned by the query. Lots of data would be passed through the network though most of it would not be used.





2. Have a stored procedure that takes in parameters @Plus0, @Plus10, @Plus20, .. @Plus80 that are bits indicating whether the field was required or not and then using a CASE statement to return values for a field only if the corresponding bit parameter was set, and returning NULL if the corresponding bit paramter was not set ?





In this case, I would be returning NULL for all those fields that were not required for a particular age group. This would save some network bandwidth, wouldn't it ?





3. Pass in the age group itself (ex: 0To20) as a parameter to the stored procedure and have lots of IF statements, one for each age group, that return only the fields that are needed for that age group.





This leads to a lot of code repitition.





4. Use a similar approach as above but use dynamic SQL queries to avoid code repitition.





But using dynamic SQL queries can affect the performance of the stored procedure as they need to be compiled each time.





5. Any other possible approaches ??





Looking forward to your responses,





Thanks much,





bmgun.

View 3 Replies


ADVERTISEMENT

Select Distinct One Some Fields, But Return All Feilds

Jul 23, 2005

I was curious...Is there a way to select distinct on a combination of some fields andthe for each record returned also get the other fields of anarbitrarily chosen record matching the fields in the distinct record.For example, if I have a select distinct on say three fields:SELECT DISTINCT Code1, Code2, Code3but the table also has other fields, maybe Foo1 and Foo2, and I wantFoo1 and Foo2 to also be displayed. Since there may be multiplerecords that match a particular Code1, Code2, Code3, then I just wantone of those to be arbitrarily chosen.

View 3 Replies View Related

Dynamically Determine Linked Servers

Oct 26, 2007

In our system, we need to execute queries using linked servers. However, the linked server name is not well known, it is a value stored in the system. Is there a way this can be done?




CREATE FUNCTION dbo.stfGetSupervisorOrgUnitAssignments(@SupervisorID INT)

RETURNS @ReturnTable TABLE(Org_Unit INT PRIMARY KEY)

AS

BEGIN

DECLARE @Server VARCHAR(100)

SELECT @Server = dbo.stfGetLinkedServerName()


INSERT INTO @ReturnTable(Org_Unit)

SELECT ReplicationID FROM OPENQUERY(@Server, '....')



END

Any help would be greatly appreciated.

View 6 Replies View Related

Stored Procedure Dynamically Determine Columnname

Dec 3, 2006

I want to dynamically determine which column I select data from. The table I want to select from looks like this:tblSexualitySexualityID int PKEN nvarchar(20)NL nvarchar(20)DE nvarchar(20)Based on the value of variable @LanguageColumnName I want to select the value from either column "EN", "NL" or "DE"In the sample below the value of @LanguageColumnName is "NL".Unfortunately the value of sexualityname is ALWAYS: "tblSexuality.NL" and not the value from that specific column...how can I alter my procedure so that it does the select the value from that column?ALTER PROCEDURE [dbo].[spFindUsers]@LanguageColumnName nvarchar(3),@startRowIndex int,@maximumRows intASBEGINSET NOCOUNT ON;
declare @tblSexualityName nvarchar(40)set @tblSexualityName='tblSexuality.' + @LanguageColumnName SELECT UserName,UserCode,ThumbNailPicture,BirthDate,ShowAgeOnly,IsMale,NearestBigCity,DistanceToNearestBigCity,Description,IsDonator,IsVIP,SexualityName FROM(SELECT ROW_NUMBER() OVER (ORDER BY UserCode) as RowNum,tblUserData.UserName,UserCode,IsDonator,IsVIP,Description,BirthDate,IsMale,ShowAgeOnly,tblCountries.CountryPicture,@tblSexualityName as sexualityname,tblCountries.CountryName,ThumbNailPicture,LastActivityDate,UserRanking,NearestBigCity,DistanceToNearestBigCity FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID INNER JOIN tblCountries ON tblUserData.Country=tblCountries.CountryID INNER JOIN tblSexuality ON tblUserData.Sexuality=tblSexuality.SexualityID) as MemberInfoWHERE RowNum > @startRowIndex AND RowNum <= (@startRowIndex + @maximumRows)END
 

View 1 Replies View Related

ODBC TCP/IP Dynamically Determine Port Check Box

Aug 16, 2006

Hopefully a simple answer:

*editted for clarity*

Where does the "Dynamically determine port" check box come from when creating a ODBC connection using TCP/IP in the client configuration? Only shows up in the control panel/adminstrative tools/Data Sources (ODBC) when it is installed. I have 3 computers here that have it (they are for development) but I am finding my users desktops do not have the box and so I am getting errors trying to connect from their desktops.

I am using Excel and Microsoft Access 2000 to connect to the server using the ODBC connection.

I need my users to connect to SQL Server 2005 so I need the "Dynamically determine port" box checked to find the port. Tried typing in the port but that isn't solving the problem or answering my question. So far I have tried MDAC and .NET 2.0 platform installs with no luck.

Thanks for the help,

View 8 Replies View Related

Dynamically Determine Subtotal Visibility In Matrix?

Feb 22, 2007

I'm using SSAS2005 SP2 as a data source, and RS2005 SP2 for reporting.

With the upgrade to sp2, users assigned to an area in a sales location tree no longer get a value returned for AGGREGATE(MyValue.Value) when looking at a region higher than they are assigned.

My MATRIX reports now look like:

_______________________________|___|___|
Region____Sub Region____Area1__|_5___3_|
Sub Region Total_______________|_______|
Region Total___________________|_______|
Grand Total____________________|_______|

Before sp2, they had the 5 and 3 values repeated in all total rows.

My thought was to hopefully dynamically hide the sub region and region groupings and their related subtotals - but when trying to do this, only the grand total shows, and its values are blank.

When a person can only see Area1, I'd like the report to look like this:

_______________________________|___|___|
Region____Sub Region____Area1__|_5___3_|

but if they could see everything from the cube, I would like those sections to show, because they have values in them:

_______________________________|___|___|
Region____Sub Region____Area1__|_5___3_|
Sub Region Total_______________|_10__5_|
Region Total___________________|_20_11_|
Grand Total____________________|_42_13_|

View 3 Replies View Related

Insert Diffrent Columns,Determine Non Unique Fields

Sep 19, 2006

andre writes "I have two questions:

1.)How do i determine if fields are not unique??
2) How to update one table with values from another table when the row names are diffrent??

Thank you!!"

View 1 Replies View Related

How To Refresh DataSet Fields Dynamically

Jan 30, 2008

Hi All,

I have a Dataset. I use this dataset in my matrix for grouping based on the underlying table columns names. Users can add columns to the underlying table that the DataSet refers.

Now my problem is:
The data is grouped based on the column name selected from the parameter.
If the user adds a column to this table how will the dataset refresh so that it contains the newly added field for grouping data.

View 5 Replies View Related

Stored Procedures - Using Return Value From Dynamically Created SQL Statement

Jul 29, 2007

Hello,

This is my foray into Stored procedures, so I'm hoping this is a fairly basic question.

I'm writing a stored procedure, in which I dynamically create an SQL statement.
At the end of this, the SQL statement reads like:



Code SnippetSELECT COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0



Now this statement works a charm, and returns a single value. I want to assign this count to a variable, and use it further on in the stored procedure. This is where the problems start - I cant seem to do it.

If I hard code a statement, like



Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger
that works fine (although it brings back a count of all the lines).

But if I modify the dynamically created SQL Statement from earlier on to:



Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0
it doesnt work - it complains: Must declare the scalar variable "@LineCount".

Just to clarify, when I say "dynamically created an SQL statement, I mean that by a bunch of conditional statements I populate a varchar variable with the statement, and then eventually run it exec(@SQLStatementString)

So, my question would be, how do I do this? How do I make a dynamically generated SQL statement return a value to a variable?


View 3 Replies View Related

SP Parameter To Determine SELECT Value

Jun 22, 2014

I have a problem with SP when passing in Parameters. Basically something like this:

-- Pass 1, 2 or 3 as parameter
EXEC SP_mySP 1

-- The SP will do the following SQL Statement
SELECT * FROM myTable
WHERE
(
(If @Parameter1 = 1 then myColumn = 'A' or myColumn = 'B')
(If @Parameter1 = 2 then myColumn = 'C')
(If @Parameter1 = 3 then myColumn is not null)
)

How to make the above condition?

View 2 Replies View Related

Return All Fields Of Database

Feb 16, 2007

Hello,

Is it possible to return all the field names of a database. I do not want the data rows. Just a list of fields in the databse.

Thanks

View 6 Replies View Related

SELECT Clause To Determine If A Field Is Japanese

Jun 8, 2007

I am currently trying to find a way in which I can determine if a column in a Select clause is Japanese. The column currently supports English and Japanese Kanjis and other kanas. Is there a way to determine if this column is not English or if it is Japanese without physically looking at it.?

Thanks .... Chris

View 1 Replies View Related

Expression Divide By 0 - How To Return N/A For Fields

Jun 3, 2015

I am new to SQL. I added a calculated field to my dataset with and expression to calculate efficiency.

My expression is as follows: =Fields!EstTotLbrHrs.Value/Fields!ActTotLbrHrs.Value

In some cases my value for ActTotLbrHrs is 0, so it returns #Error

How do I return "N/A" for fields with ActTotLbrHrs = 0

View 1 Replies View Related

Merging Queries To Return Different Fields In Same

Oct 25, 2005

hi all,

can anybody help in combining all the mentioned queries into a single query so as to display all fields in a row.

1.number of imported imported animal type:

select count(*) as import_lic_no from appln_clip ac,consign_animal c,pet p

where ac.ac_id=c.ac_id and p.pet_no=ac.clip_id and ac.appln_id like 'A8%'

2. number of imported animal type that are licensed:

select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id


3.percentage: 2/1*100

select percentage=

((select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id)
(select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id))*100

4.local animal type:total pet records - 1

select number=

(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id)


5.local animal type that are licenced:total pet records-2

select number1=
(select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)


6.percentage: 5/4*100

select percentage=((select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)/
(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id))*100

thx,
vani

View 1 Replies View Related

Return Non-matching Records On Two Fields

Jan 18, 2008



Here is a very basic question that I have.

I have two tables, A and B. Both have a customernumber and a batchid. This combination is unique in both tables.

How can I pull back the records from table A that do not have a corresponding combination in B?

I know I could find the ones that do match and then exclude them using an inner join and subquery, but is there a simpler way?


THANKS!

View 1 Replies View Related

Trying To Return Whole Records With Distinct Fields

Oct 17, 2006

Hi

I am trying to write a query that will return a full record with a particular distinct field (the rest of the record being the first such record that includes the distinct field).

For example, for the following:

Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow
Grape Y Green
Grapefruit N Yellow

I would want to return (assuming Colour was the distinct field):

Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow

How do I do this? I've tried using a join (of all different kinds) with a subquery that uses SELECT DISTINCT but this doesn't seem to work. I've tried GROUP BY but none of the aggregate functions seem to just take the first found field.

Thanks for any help you can offer.

View 11 Replies View Related

T-SQL (SS2K8) :: Select Past 3 Dates And Determine If 1st And 3rd Are Within Timeframe?

Sep 7, 2014

I have a table with addresses and activity dates. I need to be able to retrieve the past 3 activity dates and see if the first and last occurred within 15 days. If so, I need to flag them.

Using max date gets me the last date but not the previous two. I was trying to use top 3 in desc order and that didnt seem to work either.

View 9 Replies View Related

Need A Query To Return Rows Containing ALL Matching Fields

Sep 9, 2004

I have three tables X,Y,Z. Table 'Y' is having foreign key constraints on tables 'X' and 'Z' (which happen to be primary key tables).
I would like to run a query in which I can retrieve rows from Table 'X' only if the matching rows in Table 'Y' have "ALL" their matching rows available in a simple query being run on Table "Z".
The "All" part is very important.

For more clarification, let me give you an example. Table "X" is equivalent to a mathematical "Equation" table which consists of an equation made up of several "Fields". These fields are stored in Table "Z". Table "Y" contains the primary keys from Tables "X" and "Z". i.e. Table "Y" determines what fields are required for an equation to be complete.

I am having a query "Q" on Table "Z" (Fields table) which returns me a bunch of Fields. Now, on the basis of these fields, I want to retrieve only those Equations (Table "X") which have "ALL" their required Fields present in the bunch retrieved by the Query "Q".

I hope I am clear enough.
Does anyone have any solutions???

View 2 Replies View Related

Transact SQL :: Return Values That Are Equal To Both Fields ONLY

May 22, 2015

Im doing a report on total sales, however my statement below will return values that are equal to both fields ONLY.For example I want to do a query using two text boxes 'from' and 'to 'and count the total sales between the product dates 'Veh_Tyres_Date' and Veh_Parts_Date and 'Veh_Tyres Price' and Veh_ Parts Price'. however it works but if for example I do a search for 01/05/2015 from 31/05/2015 it will not return anything if the second field doesnt contain a sales date between that period.

SELECT tblVehicles.Veh_Parts, tblVehicles.Veh_Parts_Date, tblVehicles.Veh_Tyres, tblVehicles.Veh_Tyres_Date
FROM tblVehicles
WHERE (((tblVehicles.Veh_Parts_Date) Between [Enter From Date] And [Enter To])
AND ((tblVehicles.Veh_Tyres_Date) Between [Enter From Date] And [Enter To]));

View 4 Replies View Related

Transact SQL :: Query To Return As Part Of The Fields

Oct 16, 2015

I am working on a query that is quite complex. I need the query to return as part of the fields a field that will contain the total percentage of tickets in a version.The query is below

select cat.name as name,count(distinct bug.id) as numberOfBugs,cast(count(bug.id) * 1000.0 / sum(count(bug.id) * 10.0) over() as decimal(10,2))/100 AS qnt_pct, vers.version, dateadd(s,vers.date_order,'1/1/1970') as "Release_Date"
from mantis_bug_table bug
INNER JOIN mantis_category_table cat on cat.id = bug.category_id
LEFT OUTER JOIN mantis_project_version_table vers on vers.project_id = vers.project_id and vers.version = bug.version

[code]....

View 12 Replies View Related

Return Number Of Days By Year From Date Fields

Dec 3, 2013

I need to associate aggregate gross_revenue with calendar year, but do not have a date field that reflects payment dates, just contract periods a start_date and an end_date. The contract periods are typically 1 or 2 years and can start at any time I.e start_date 6/1/2012, end date 5/31/13. I think by finding the number of days that fall in each calendar year and storing in a temp table, I can create a simple formula to associate revenue to each year.

View 2 Replies View Related

Create A Formula In A Column For Return 2 Fields From A Other Table

Oct 17, 2007

Hello,
I would like create in a table (A) a column with a formula's data.
In this formula I would like implement 2 fields from a Table (B)

So the formula can be :
[TABLE_B].[FIELD1] + [TABLE_B].[FIELD2]

Is it possible?
We can call 2 fields from a other table?

Thank you

View 1 Replies View Related

Multiple CASE Statements - Three Fields To Be True In Order To Return Y

Feb 19, 2014

How do I properly write a CASE statement for the following business logic:

I want three of the following fields to be true in order to return 'Y'.

For example:

If field name 'Accepted' = 1 AND field 'StepNo' = '1' and field 'Visit' = 'V1'

Otherwise, I want it to return 'N'.

I have tried the following code below and it is not working.

, CASE WHEN Accepted = '1' AND StepNo ='1' AND Visit ='V1'
THEN 'Y' ELSE 'N' END AS 'StatusQ (Col N)'

View 2 Replies View Related

Dynamically Select Column

Sep 29, 2004

Hey all. I'm trying to create a stored proc that will update a variable column, depending on the parameter I pass it. Here's the stored proc:


CREATE PROCEDURE VoteStoredProc
(
@PlayerID int,
@VoteID int,
@BootNumber nvarchar(50)
)
AS

DECLARE @SQLStatement varchar(255)
SET @SQLStatement = 'UPDATE myTable SET '+ @BootNumber+'='+ @VoteID + ' WHERE (PlayerID = '+ @PlayerID +')'

EXEC(@SQLStatement)

GO


I get the following error:

Syntax error converting the nvarchar value 'UPDATE myTable SET Boot3=' to a column of data type int


The update statement is good, because if I use the stored proc below (hard-coded the column), it works fine.

CREATE PROCEDURE VoteStoredProc
(
@PlayerID int,
@VoteID int,
@BootNumber nvarchar(50)
)
AS

UPDATE
myTable
SET
Boot3 = @VoteID
WHERE
PlayerID = @PlayerID
GO


Is there a way to dynamically choose a column/field to select from? Or is my syntax incorrect..?
Thanks!

View 2 Replies View Related

How To -- Select * Into Tab_mmddyyyy From Tab Dynamically?

Jan 11, 2008

How to -- select * into tab_mmddyyyy from tab dynamically?

View 1 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

How Do I Dynamically Change The TOP X Portion Of A SELECT

Oct 26, 2006

I'm sure I'm missing something. I am returning the TOP X number of customers by revenue and I'd like to change the number of records returned by passing a parameter but I keep getting an error.    @TopX int ( or varchar)    SELECT @TopX  CompanyName, Amount FROM Sales Where..... Why will this not work?

View 4 Replies View Related

Dynamically Select Rows From Last Month

Nov 9, 2014

This is my Statement.

SELECT a.FIRMA,
a.PSNR,
a.FELDNR,
a.PFLFDNR,
a.INHALT AS Cardnr,
a.PFGLTAB,

[Code] .....

But it deliver me also rows which are not in valid. I need only rows from last month from 01.10 until 31.10 and this rows are over the time. How can i select dynamically all row between 01.10 and 31.10 ?

FIRMAPSNRFELDNRPFLFDNRCardnrPFGLTAB PFGLTBISKSTNRPSPERSNRPSVORNAPSNACHNA
13980211DU9572014111220321231000043000957xxxxx xxxxxx
13990211DU9552014110120321231000010000955xxxxx xxxx
14030211DU9602014120120321231000086000960xxxxxxxxx

View 10 Replies View Related

SQL Server 2012 :: Return A Table Of Table-names Dynamically?

Sep 14, 2015

I have a function that returns a table from a comma-delimited string.

I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.

CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)

[code]....

View 9 Replies View Related

How Can I Dynamically Build Sql SELECT Using ASP 1.0 Array Concept

Jun 6, 2007

The below code is ASP 1.0 to dynamically search a database and I want to use the same concept for a ASP.Net 2.0 solution.  Do I do this in the code behind or on the aspx page and if on the aspx page what controls do I use for the array split?  Basically where do I start.  It took me a long time to get this old code working, I am hoping it is simpler in 2.0.
Thank you
OLD ASP 1.0 code to dynamically build a Sql Select statement for searching a database using one or more search words entered by user.
If Request.Querystring("kwdSearch") <> "" ThenDim kwdString, ArrKwdString, iCountiCount = 0 kwdString = Replace(Request.Querystring("kwdSearch"), "'", "''")ArrKwdString = Trim(kwdString)ArrKwdString = Split(kwdString, " ",-1,1) For iCount = 0 to UBound(ArrKwdString) If iCount < UBound(ArrKwdString) Then  Criteria = Criteria & "tblLinkInfo.L_Keywords LIKE '%" & ArrKwdString(iCount) & "%' AND "  Else  Criteria = Criteria & "tblLinkInfo.L_Keywords LIKE '%" & ArrKwdString(iCount) & "%' " End ifNext    RS.Open "SELECT * FROM tblLinkInfo Where (" & Criteria & ") AND L_Enabled = 1 ORDER BY " & SortBy & "L_Rank", CNN, 3 If RS.EOF Then  If Rs.State Then RS.Close  RS.Open "SELECT * FROM tblLinkInfo WHERE L_Description LIKE '%" & Replace(Request.Querystring("kwdSearch"),"''","'") & "%' AND  L_Enabled = 1  ORDER BY " & SortBy & "L_Rank", CNN, 3  End If
  RESULTS --- Display results with Repeater1.DataBind(); etc
  Exit SubEnd If

View 3 Replies View Related

Dynamically Adding Select Parameters (Filter)

Jul 24, 2007

how do i add parameters like this dynamically? do i need to change the select command? to add the @ID part? 

View 4 Replies View Related

Dynamically Assign Select Statement To SqlDataSource

Jan 31, 2008

I wish to dynamically assign Select Statement to SqlDataSource. Anyone has any idea?? Is it possiable at all?
I am coding using C# under Framwork 2.0

View 1 Replies View Related

Dynamically Assign Select Statement To SqlDataSource

Jun 16, 2008

How can i assign select statement to a sqldatasource
I mean if the user clicks button 1
sqldatasource=-"select * from customers where status='ClOSED'  "
or else if the user clicks button2
 sqldatasource="Select * from Customers Where Status='OPEN'  "
 I want to bind the sqldatasource to a gridview finally .
 Is this possible ?
I tried sqldatasource filters  But am reallly confused
Any suggestions/solutions are appreciated
thankyou

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved