hi,
please check this query and reply back with the appropriate solution.
len(ltrim(rtrim(exec('select' ' ' + 'pay' +convert(substring(@y1,3,2), varchar 2)))))<>0
here the concept is concatenating two string then that result is used as column and retreiveing data.but this is considering it as string instead of column.
can anyone give an appropriate solution.
Regards,
Uma.
I find myself between rock and hard place.
My requirement is to simply output a flat file from an OLE DB source containing three types of records.
The output records should look something like this:
Type 1 contains field1, field2, field3
Type 2 contains field1, field3, field6
Type3 contains field1, field4, field5
The source table contains all six fields.
If I simply map the input to output columns, I get all fields output in each record, which is not what I want.
So I run the source through a derived column transformation, inspecting the record type field and creating a new column containing the relevant fields concatenated together, e.g field1 + field 2 + field3 for the Type 1 record.
The problem is that the fields are now all scrunched together, i.e all the trailing spaces are being stripped out of each field during concatenation.
Is there any way to string these fields together while maintaining the original field length?
I'd like the output to look like
ABC FGH XYZ instead of
ABCFGHXYZ
Hi,I have table which has the following values :ID SEQ Text1 1 A2 1 B3 2 C4 2 D5 2 E6 2 F7 3 GThe result should be :1 AB2 CDEF3 GCould somebody help me with this? I could use an cursor but the tablecould be large and i want a fast solution.Thanx in advance...Hennie
View 6 Replies View Related
I have a table detail where i need to merge the information on two columns. The difficult thing is, the 2nd column must have a different font size when i render the report. Also the merging of the two columns should not generate a space between the contents of both columns. I have done concatenation using the '&', the only problem is the data in the second column inherits the font size of the first column. I have used textboxes, but it generates spaces if the data in the first column contains lesser characters.
Does anyone have a solution?
AJ0520
Hi All,
I am trying to write a select statement which will concatenate all values of a string column and provide me with a result set containing just one row of data containing a concatenation of all values.
For eg:
column1
abc
def
hij
klm
nop
is it possible to write a select statement which would return
result
abcdefghijklmnop
as a result?
TIA
Ketan
Hi,
I need to concatenate a field from certain number of rows. I created a function to return the concatenated value which will be a part of another view/procedure to be used for reporting purposes.
Here's sample data:
iIncidentID iWorkNoteID iseqNum workNoteAll
1 1 1 notes1(1275 chars)
2 1 1 notes2(1275 chars)
2 1 2 notes3(1275 chars)
3 1 1 notes4(1275 chars)
3 1 2 notes5(1275 chars)
3 1 3 notes6(1275 chars)
Final output
iIncidentID workNoteALL
1 notes1(1275 chars)
2 notes2 notes3
3 notes4 notes5 notes6
final woorkNoteAll will be a part of a query in another view which contains many other fields.
Here's the function. I'm passing an ID and based on that ID, the function returns a string. However, when I tested the function it's giving me a null.
/*
--Calling syntax:: Select dbo.getIncidentNotes(187714) as 'Notes'
--Function to get all the latest notes for an incident
*/
CREATE FUNCTION dbo.getIncidentNotes(@iIncidentID int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @allnotes varchar(8000)
DECLARE @seqnotes varchar(255)
DECLARE @seqnum int
DECLARE @counter int
SELECT @counter=1
SELECT @seqnum = max(iseqnum) from dbo.frs_weekly_prospect_status2 where iIncidentId=@iIncidentID
WHILE (@COUNTER <=@seqnum)
BEGIN
SELECT @seqnotes = workNoteALL from dbo.frs_weekly_prospect_status2 where iIncidentId=@iIncidentID and iSeqNum=@counter
SELECT @allnotes = @allnotes + @seqnotes
SELECT @COUNTER = @COUNTER + 1
END --While Begin
RETURN @allnotes
END
Can someone please tell me what's wrong with the code?
I really appreciate it.
Thanks in advance.
I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.
I have 2 database table3 as follows:
eg
1) myGameRecency which contains columns : GameId (identity specification column/primary key/not null), Date (not null), [1], [2], [3], [4] , WeeksSinceDr0, WeeksSinceDr1, WeeksSinceDr2
2) myGameFrequency which contains columns : AllBallsFrequency , WeeksSinceDrawnAllBalls
Using the myGameRecencyAllBalls table ---
I wish to insert a 0 into a column corresponding to a ball that has been drawn, eg if a 4 has been drawn, then a 0 will be inserted into that column. If a ball has not be drawn, then the value in that column will be a running total, signifying the number of draws since it was last drawn ( ie since a 0 was inserted into that column).
I place a 1 in the column corresponding to the number of weeks since a number has been drawn. The name of the column is therefore the concatenation of the string literal 'WeeksSinceDrawn' and the value held by the variable, @lastRowCount obtained by the lastrow_CURSOR.
I have declared a variable @colName to hold the concatenation / Set @colName = 'WeeksSinceDr' + CONVERT(nvarchar(max), @lastRowCount) and then tried to use it as follows: SET [@colName] = 1
however, I receive an error message advising me that I have an invalid column name. Is there any means of setting a column name by concatenating two variables or , a string literal and a variable ?
Thank you
lpbcorp
sqlCmd.CommandText = "DECLARE @colName nvarchar(max) " +
"DECLARE @lastRowCount int " +
"DECLARE lastrow_cursor CURSOR SCROLL FOR " +
"(SELECT [" + i.ToString() + "] FROM " + DBGameName.ToString() + "RecencyAllBalls) " +
"OPEN lastrow_cursor " +
"FETCH LAST FROM lastrow_cursor INTO @lastRowCount " +
"SET @colName = 'WeeksSinceDr' + CONVERT(nvarchar(max), @lastRowCount) " +
"IF @lastRowCount <= 175 " +
"BEGIN UPDATE " + DBGameName.ToString() + "RecencyAllBalls SET [@colName] = 1 WHERE Date = '" + Date + "' " +
"END " +
"ELSE " +
"UPDATE " + DBGameName.ToString() + "RecencyAllBalls SET WeeksSinceDrOver175 = 1 WHERE Date = '" + Date + "' " +
"CLOSE lastrow_cursor " +
"DEALLOCATE lastrow_cursor";
sqlCmd.ExecuteScalar();
I'm puzzled as to how to express what I want in a stored procedure. Assume two columns, Surname and GivenName. The surname might be missing. When I originally wrote this app in Access, I used the following expression:
SELECT Iif( IsNull(Surname), GivenName, Surname + ", " + GivenName ) AS Agent
FROM Agents;
I've looked at the syntax for CASE but I can't figure out exactly how to say what I intend, particularly the AS Agent column aliasing.
Any help greatly appreciated. Please cc me privately so I receive your assistance at once!
TIA,
Arthur
I have a customer who has recently migrated their SQL server to a newserver. In doing so, a portion of a stored procedure has stoppedworking. The code snippet is below:declare @Prefixes varchar(8000),declare @StationID int-- ...select @Prefixes = ''select @Prefixes = @Prefixes + Prefix + '|||'from Devicewhere Station_ID = @StationIDEssentially, we are trying to triple-pipe delimit all the deviceprefixes located at a specified station. This code has workedflawlessly for the last 10 months, but when the database was restoredon the new server, @Prefixes only contains the prefix for the lastdevice.Is there a server, database, or connection option that permits this towork that I am not aware of? Why would this work on the old server andnot on the new? (BTW - both servers are running SQL 2000 StandardSP4).Thanks!
View 6 Replies View RelatedHi, I am a extreme beginer to sql server and i am i'm having big trouble trying to display my sql query properly. Bascially i want to put the results of a one to many query into one row per record. I have read articles and forums discussing 'concatenating the values' or creating a function??? but i dont follow what they mean and i am completely lost. Can anyone provide a really simple explanation on what i need to do to resolve my duplicate row issue? i urgently need to find a solution to this.
Regards
I'm wanting to concatenate something that doesnt exist to an already existing field.
What I need to do is add a generic email address to every record in a data view So, what it would do is take the column that does exist and add @domain.com to another column that I would call username So rather than it just appearing as 911234 it would be a derived column saying 911234@domain.com
i've tried using +''+ but I get a space after the number.
I have two tables I am working with, they are "Institutions" and "InstitutionOversights". The relationship is one-to-many.
The sample data is below.
Table one:
InstitutionID, InstName
------------------------
1 School Alpha
2 School Beta
3 School Charlie
4 School Delta
Table two:
InstitutionOversightID, InstitutionID, Type
------------------------------------------------
1 1 Accreditation
2 1 Verifcation
3 1 Old System
I would like a query to return the results in the following format:
InstitutionID, InstName, TypeList
-----------------------------------------------
1 School Alpha Accreditation, Verification, Old System
2 School Beta null
3 School Charlie null
4 School Delta null
Hello All.
Hopefully someone out there will have an idea as this isdriving me nuts.
Ihave some sample problem. I want to use function replace() on Derived Column.
For example.
when strDate = 2007/03/22
I used ==> replace(strDate, "/", "") ==> 20060322
But If strTest = "123.10" ====>> 123.10
How can i do to replace ( " )double qoute ?
by function replace()
what is a statement for replace (") in Derived Column ?
please tell me for this event.
any suggesstion appreciated
Thank you very much.
Chonnathan
Hi
Books online mention the existence of sample code for several custom tasks, including the one mentioned in the title. But, when I try to find this code in the location mentioned it is nowhere to be found.
I have run a search on the rest of my drive and come up empty.
Can anyone tell me where to find this?
Thanks
Hai
I have one amount field in my table. I need the query to get the record where the amount field must be +/-15%.
Hello,I am a very complex problem in front of me. Kindly help me outin acheiving the same.Say I have a table called InfoName with two columns Name and IDInfoNameName IDOS 1SP 2Driver 3fasdf **** ***** ****(I AM INTERESTED IN ONLY FIRST THREE ROWS )I have another table Infotxt which uses the ID of InfoName asforeign key. It stores the value of this ID as shownInFotxtID Value UnitNAME1 Win 2000 raj2 SP 4 raj3 40 GB raj1 Win xp jay2 SP 2 jay3 20 GB jayNOw I need to present it with unitname's configuration of OS,Sp and disk capacity like below.name OS SP DriveRaj win2000 sp4 40 GBJay winxp sp2 2o GBThat is, the rows of the InfoName table (first 3 rows) shouldbe the columns of my resultant query.How can I achieve the same.Please give me some ideas, and if the question is silly, I amvery sorry, because I am new to database queries...Thanks,cspekcspek*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedIm trying to use VB.net 2005 to write a sample app to access a DB. Are there any samples for this and any samples of how I go about making the DB in the first place?
View 1 Replies View RelatedI have a 'searchpath' column in audit table. I need to extract the folders from the column and display it in separate columns.
Column searchpath has value like:
//content/folder[@name='AFR']/folder?[@name='AFRABC']/folder[@name='CDD']/folder[@name='Packages'] /folder[@name='Deployment']/package[@name='XXX spend name']/model
I need to extract the folder names and display it in separate columns in a new table this way:
AFR AFRABC CDD Packages Deployment XXX spend name
I am working in an environment where i inherited bad design. I have a column in a table. This col contains huge HTML files. We are mostly reading these files and very little updates.
I am changing this whole architecture and going to using Azure Blob storage. I am stuck right now. I need to extract these html strings and save them into separate files in year/month/day/filename.html format. I have another column in the table which has create date saved in it.
I am planning to import all these files into BLOB storage and save link in the table.
1) How can i extract these strings from the table and save them in the year/month/day/filename.html directory/sub-directory format
2) How to import these files into BLOB storage.
I am using query strings to pass data from web form to web form and I have two questions. First if i use a asp:sqldatasouce to fill up a grid view and I have my select command set to a paramater that get whatever is in the query string it will not work because whatever is in the quers string gets a " ' " put in front and in the back of it. So if the query string was 5 whene it does the sql statement it sets my paramater = '5' not just 5 so its wont work. How can I fix this using the asp:sql datasource my aspx code looks like
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Rental PropertiesConnectionString %>"
SelectCommand="SELECT * FROM [APARTMENTS] WHERE ([PROPERITY_ID] = @PROPERITY_ID)">
<SelectParameters>
<asp:QueryStringParameter Name="PROPERITY_ID" QueryStringField="key" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
Also since i have not been able to get around this so i have been wrting code in vb.net to attact a dataset to a grid view to populate it based on the query string i would do the following in vb.net to get ride of the ' in front and behind the query string
Dim y as string = "'" // " ' "
key = Request.QueryString("key").trim(y.tochararray)
But now i am doing another project in C# and I have re-written the above code in C# it will run but it will not take the " ' " out form infront or behind key. How does this need to be changed up?
string y = "'";
key = Request.QueryString["key"].trim(y.tochararray());
Hi There,
Can anybody suggest me what is the best way to take a column with comma separated stings and output them into multiple columns with strings?
for example if I have a column with "watertown, newton" as a string, I need to separate them to two columns with watertown and newton values?
Is Derived column transformation the best way to do it?
Thanks.
Sam.
Hi, I have a SQLDataSource binding to a GridView and can come to the page either with or without a query string attached:
/ProjectManagement/reporting/project.aspx
/ProjectManagement/reporting/project.aspx?portfolio=3
When it comes with a query string, I can see in SQL Server profiler it executes and I get all the right data. When it is an empty string, or with no "?portfolio=" on it, it won't even execute against SQL server. Any ideas?
<asp:GridView ID="grid" runat="server" Width="600px"
ShowHeader="false"
AutoGenerateColumns="false"
DataSourceID="DSportfolio"
AllowSorting = "true"
AllowPaging = "true">
<Columns>
<asp:TemplateField>
<ItemTemplate>
.............
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="DSportfolio" ConnectionString="<%$ AppSettings:SQLConnection1 %>"
SelectCommand="uspSELECT_PROJECT"
SelectCommandType="StoredProcedure"
runat="server">
<SelectParameters>
<asp:QueryStringParameter Name="p_PORTFOLIOID" QueryStringField="portfolio" />
</SelectParameters>
</asp:SqlDataSource>
Thanks,
James
Hi, I am having a problem looking up querystrings in my DB, I have the following code
<asp:SqlDataSource ID="SqlData_products" runat="server" ConnectionString="<%$ ConnectionStrings:ProductDatabaseConnectionString2 %>" SelectCommand="SELECT * FROM [tbl_subProduct],[tbl_subCategory],[tbl_topCategory],[tbl_Material],[tbl_topLevelProduct] WHERE numSubCategoryID = @Category OR numMaterialID = @Material OR txtOrderCode = @Keyword OR txtMovexCode = @Keyword OR txtUKMapCode = @Keyword"> <selectparameters> <asp:QueryStringParameter Name="Category" QueryStringField="SearchCatString" /> <asp:QueryStringParameter Name="Material" QueryStringField="SearchMatString" /> <asp:QueryStringParameter Name="Keyword" QueryStringField="SearchKeyString" /> </selectparameters> </asp:SqlDataSource>
My Querystrings are in the VB file:- SearchCatString = Request.QueryString("txtSelCat")
SearchMatString = Request.QueryString("txtSelMat")
SearchKeyString = Request.QueryString("txtKeyword")These come from a previous page, where a dropdown list copies the ID of the selected item into a text box. My problem is that this is not working please help
I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.
I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.
This is the basic logic goes soemthing like this:
select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database
The output would be
Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/
So far, nothing I have tried has worked.
Any ideas?
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!
Hi all,
If I have a query string that is to be stored in a database, for example
Code:
SELECT prod_id, prod_name, prod_desc FROM products WHERE prod_id = 'variable'
how can I put a variable identifier into this string so that when I need to run the query I call it from the database and simply insert the relevant variable in the correct place.
Is there an appropriate way of doing this in MS SQL Server?
Thanks
Tryst
Hey
Is it possible to search for a column without a value?
$query="select id from table1 where col2=''"; (this didnt work, but how do I do it??)
I need the id for the row that has the col2 empty.
:)
The sql below is a simple example of LIKE query with a table of wildcard strings. I hope this helps others.
declare @LikeTable Table
(
LikeValue nvarchar(50)
)
insert into @LikeTable (LikeValue) values ('%blah')
insert into @LikeTable (LikeValue) values ('%abc%')
insert into @LikeTable (LikeValue) values ('%edf%')
insert into @LikeTable (LikeValue) values ('car%')
insert into @LikeTable (LikeValue) values ('%ome%')
declare @CompareValue nvarchar(50)
set @CompareValue = 'some value'
select * from @LikeTable where (@CompareValue like LikeValue)
I am building myself a datadriven menu control and I have got one table where I store all menu items as rows. On each row there is a column for roles, in this column I have added one or several roles as a string. Today I can retrieve all menu items (rows) for a requested role, but how can I retrieve menu items for two or more roles? I have each role inserted as rows in a temp table (@Role), if that makes it easier?DECLARE @Role TABLE ( role varchar(15)) SELECT SiteMap.Id, SiteMap.Title, SiteMap.UrlFROM SiteMapWHERE (CHARINDEX(@Roles, SiteMap.Roles) > 0 ) Regards, Sigurd
View 2 Replies View RelatedHi All,I have what seems to me to be a difficult query request for a databaseI've inherited.I have a table that has a varchar(2000) column that is used to storesystem and user messages from an on-line ordering system.For some reason (I have no idea why), when the original database wasbeing designed no thought was given to putting these messages inanother table, one row per message, and I've now been asked to providesome stats on the contents of this field across the recordset.A pseudo example of the table would be:custrep, orderid, orderdate, comments1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer askedfor a brown model2, 10002, 2004-04-12, :Comment 3:Comment 4:1, 10003, 2004-04-12, :Comment 2:Comment 8:2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled orderSo, what I've been asked to provide is something like this:orderdate, custrep, syscomment, countofsyscomments2004-04-12, 1, Comment 1, 12004-04-12, 1, Comment 2, 22004-04-12, 1, Comment 3, 12004-04-12, 1, Comment 8, 12004-04-12, 2, Comment 1, 12004-04-12, 2, Comment 3, 12004-04-12, 2, Comment 4, 22004-04-12, 2, Comment 6, 22004-04-12, 2, Comment 7, 1I have a table in which each of the system comments are defined.Anything else appearing in the column is treated as a user comment.Does anyone have any thoughts on how this could be achieved? The endresult will end up in an SQL Server 2000 stored procedure which willbe called from an ASP page to provide order taking stats.Any help will be humbly and immensely appreciated!Much warmth,Murray
View 7 Replies View RelatedHello,
I have a SQL database where I am attempting to perform a complicated query that I cannot seem to figure out. I am using SQL Server.
I have 4 tables (TableA, TableB, TableC, and TableD). TableA and TableB are guaranteed to have a relationship.
TableC and TableD are guaranteed to have a relationship.
The trick is, I need to link between TableA and TableC essentially using a LEFT JOIN. I need to retrieve all of the values from TableA regardless and the information from TableC and TableD if there is a link, if there isn't a link, then the values from TableC and TableD need to be empty strings.
Does anyone know how I can do this? I've been trying for the last 5 hours without any luck. I feel I'm close, but there is something I feel I'm overlooking.
Thank you SO much for your help!
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.
Hello
First post here and am looking for some help with a issue I have with concatenation.
I am looking to concatenate two columns together into one new column...
ins_type + currency
I have searched for help onthis but for some reason nothing I do works...
Any ideas or suggestions?
Thanks
Nuno
SELECT
a.deal_tracking_num AS TemplateDealNum,
a.tran_num AS TemplateTranNum,
a.reference AS TemplateName,
a.ins_type,
a.currency,
a.trade_date AS TemplateCreated,
u.template_tran_num as Misc
FROM
ab_tran a, USER_restricted_templates u
WHERE
a.tran_type = 0 AND
a.asset_type = 2 AND
a.current_flag = 1 AND
a.toolset IN (33,36,15,16) AND
a.tran_status = 15 AND
a.tran_num *= u.template_tran_num