How Do I Concatenate Text To A Sql Statement
Oct 20, 2005
The following sql statement fails:
SELECT IdFlight, OrganizerLastName + ", " + OrganizerFirstName + "
Flt:" + FlightNumber As Concat FROM TableFlights Order By
OrganizerLastName, OrganizerFirstName
I'm trying to concatenate fields and words, but my asp.net page gives
me an error saying " Flt:" is not a field.
Thanks,
Marvin
View 1 Replies
ADVERTISEMENT
Sep 8, 2000
I would like to concatenate text datatypes. Is this possible??
I'm using field1 + " " + field2
I cannot convert to varchar since the size is larger than 8000.
Thanks, Vic
View 1 Replies
View Related
Mar 29, 2006
New to using MS SQL server!
I have a requirements DB. Before I came on board, the DB was seeded with requirements text and a requirements ID in separate columns. I've been asked to append the ID in front of the text:
Text - The user shall be able....
ID - 1.01.01.
Combined - 1.01.01.The user shall be able....
I've created the following query that displays the columns:
CREATE VIEW dbo.ReqID
AS
SELECT RQREQUIREMENTS.ID, RQREQUIREMENTS.REQUIREMENTNAME,
RQUSERDEFINEDFIELDVALUES.FIELDVALUE
FROM RQREQUIREMENTS LEFT OUTER JOIN
RQUSERDEFINEDFIELDVALUES ON
RQREQUIREMENTS.ID =
RQUSERDEFINEDFIELDVALUES.REQUIREMENTID
WHERE (RQUSERDEFINEDFIELDVALUES.FIELDID = 171)
The Text field is REQUIREMENTNAME
The ReqId is FIELDVALUE
Should I create an UPDATE query based on this SELECT, updating the REQUIREMENTNAME column with FIELDVALUE + REQUIREMENTNAME?
Please advise - thanks!
View 8 Replies
View Related
Oct 11, 2005
I have a couple columns in a table that have a data type of Text. In a query I need to concatenate these two columns into one result.
For example
Code:
SELECT (Table1.TextColumn1 + ' ' + Table1.TextColumn2) AS 'Text'
FROM Table1
However when I try this I get the error
Invalid operator for data type. Operator equals add, type equals text.
View 2 Replies
View Related
May 29, 2008
I am trying to add a carriage return to the end of a text field through a script. This is what I'm trying:
UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '
' WHERE Column_TEXT = 'Some text'
I also tried
UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '<cr>' WHERE Column_TEXT = 'Some text'
But I keep getting the error
The data types text and varchar are incompatible in the equal to operator.
Help!!
Thanks in advance
Mangala
View 3 Replies
View Related
Nov 12, 2007
I am running an insert in a stored procedure, but need tio concatenate on of the fields. How do I do that? Below is an example:
INSERTsam.dbo.product
(productName,
ProdDesc,
TransDateBegin,
TransDateEnd
)
SELECTCode, Description + 'the product is displayed' + <br><br>'To purchase this <a href=/purchaseitem.aspx?' >here</a>
FROMweb.dbo.order_codes AS OC
WHEREproductName = code
Any ideas how I can get the select statement working?
SB
View 4 Replies
View Related
May 17, 2008
I have a query that returns multiple results
Select fname from table
returns
fred
joe
dave
What I want to do is have the query return only one result like this
"fred, joe, dave"
Any ideas?
Thanks
Kal
View 2 Replies
View Related
Jan 2, 2007
Hi all, I am creating a search table where the keywords field is madeup of several text fields and this is causing me some problems. I canconcatentate the text ok but i can't seem to concatenate matchingrecords here is the cursor loop. I'm not a fan of cursors but alsodidn't see another way of achieving this.declare @ptr1 varbinary(16)declare @Ptr2 varbinary(16)declare @profileid intdeclare @x intset @profileid = 0while @profileid is not nullbeginselect@profileid = min([id]),@ptr1 = MIN(textptr(text1))from #holdingwhere [id] @profileiddeclare c2 cursor fast_forward forselect textptr(searchterms), datalength(searchterms)from searchwhere search.[id] = @profileidopen c2fetch c2 into @ptr2, @xwhile @@fetch_status = 0beginupdatetext search.searchterms @ptr2 null 0 #holding.text1 @ptr1fetch c2 into @ptr2, @xendclose c2deallocate c2endThe #holding table contains the fields that i want to concatenate andthe search table is the resulting table. This example would loopthrough search and find id 1 in search and then append another fieldmatching id 1 in holding then move onto the next field in turn goingthrough the whole table.i.e.search holding result after each loopid text id text1 abc 1 def abcdef2 ghi 2 jkl ghijklWhen I run this, some of the records concatenate properly but most dontwith the same text being appended to the end of searchterms. i.e loadsof results will end up with jkl tagged onto the end. I can't figure outwhen my loop is falliing over!!! Can anyone help?Dan
View 4 Replies
View Related
Feb 19, 2007
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?
View 2 Replies
View Related
Mar 28, 2006
I am trying to take a value from at textbox and insert into a database. I think I need to convert the text to an integer - but I keep getting an error message saying I'm not allowed to use column names. Basically, I need to figure out how to take user input from these tet boxes and insert them into a database. Any ideas would be welcomed. the exact error is at the bottom the code.Here is the code:Imports System.DataImports System.Data.SqlClientPartial Class County_ConversionTest Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim v1 As Integer Dim v2 As Integer 'Dim v3 As Integer v1 = Integer.Parse(TextBox1.Text) v2 = Integer.Parse(TextBox2.Text) TextBox3.Text = (v1 + v2) End Sub Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click Dim strconn As String = "Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrootHCBSApp_DataDivAging_Data.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True" Dim cnnDivAging As SqlConnection = New SqlConnection(strconn) Dim v1 As Integer Dim v2 As Integer Dim v3 As Integer v1 = CInt(TextBox1.Text) v2 = CInt(TextBox2.Text) v3 = CInt(TextBox3.Text) Dim strsql As String = "INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _ "Values ('George',v1,v2,v3)" Dim cmdUpdates As SqlCommand = New SqlCommand(strSQL, cnnDivAging) cmdUpdates.Connection = cnnDivAging cnnDivAging.Open() cmdUpdates.CommandType = CommandType.Text cmdUpdates.ExecuteNonQuery() cnnDivAging.Close() End SubEnd ClassHere is the error message -
The name "v1" is not permitted in this context. Valid expressions are
constants, constant expressions, and (in some contexts) variables.
Column names are not permitted.
View 2 Replies
View Related
Feb 20, 2004
Hi,
Can you guys help me out?
I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".
I do get the answer but its not the correct figure. My SQL statement is as follow:
SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName
Please help.
View 6 Replies
View Related
Jan 1, 2007
Is it possible to combine fields and text in a select statement?
In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:
SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe
FROM GE_Data
WHERE (DIST = @DIST)
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.
If this is possible, or there's a better way to do it, I'd love some more info.
Thanks!
-Mathminded
View 7 Replies
View Related
Mar 8, 2008
Hi everyone,
So what I am trying to do is to have a simple textbox where the person enters a code.
Then I need to have that code in an SQL statement like: SELECT Participant_Code FROM Contacts WHERE (this is where im stuck, I need to have something like WHERE Participant_code = code (code is the textbox ID))
After this is done I need to keep this code from page to page, because I will need to add data from other textboxes to the database.
Thanks
View 2 Replies
View Related
Jul 5, 2014
I have trouble converting a text field into decimals in a select statement.
The select statement is as follows:
select cast(ltrim(rtrim(units)) as decimal) as units
and I get an error message:
Msg 8114, Level 16, State 5, Line 23
Error converting data type nvarchar to numeric.
and the 'units' column contains ' 324,32 '
View 2 Replies
View Related
Sep 20, 2007
I am trying to add a case/select/if statement in the text color property of reporting services. I want it so if the value of a variable is 1 then "GREEN" else "RED". Any ideas?
View 1 Replies
View Related
Jul 20, 2005
I received this error message in the application event logs. Can anyone tell me what this is pertaining to?Error: 7102, Severity: 20, State: 99SQL Server Internal Error. Text manager cannot continue with currentstatement.Thanks Barb
View 2 Replies
View Related
Sep 10, 2015
Created a new scheduled job using Transact-SQL.
Running a basic script SELECT FROM WHERE
As of now, it places it into one column, for all the fields.
I need the fields to be placed in their respective columns. Tried saving it as .xls and .csv. Same issue. Running SQL Server 2008R2.
View 2 Replies
View Related
Mar 30, 2008
I wish I had a better idea about how to describe this but here goes. I'm trying to display a list of majors/minors/graduate programs for a particular user. Now, I've gotten it display perfectly and was able to append (Graduate), (Major), (Minor) to each of the 3 different sections. My question is is it possible to add like rows that don't actually exist in table to the beginning of the select? For instance I want to add All and General before the rest of the select. I was also wondering where I should do a group by cuz I'd like to keep it in blocks of like graduates then majors and finally minors but currently it is just doing it alphabetically...Anyways below are the tables and at the end the current stored procedure i'm using, which works great...just not to the degree I'm aiming for.
Code Snippet
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[GraduateDiscipline] Script Date: 03/30/2008 14:45:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GraduateDiscipline](
[GraduateDisciplineID] [int] IDENTITY(0,1) NOT NULL,
[DegreeID] [nchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GraduateID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DisciplineName] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_GraduateDiscipline] PRIMARY KEY CLUSTERED
(
[GraduateDisciplineID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[GraduateDiscipline] WITH CHECK ADD CONSTRAINT [FK_GraduateDiscipline_DegreeShortName] FOREIGN KEY([DegreeID])
REFERENCES [dbo].[Degree] ([DegreeID])
GO
ALTER TABLE [dbo].[GraduateDiscipline] CHECK CONSTRAINT [FK_GraduateDiscipline_DegreeShortName]
GO
ALTER TABLE [dbo].[GraduateDiscipline] WITH CHECK ADD CONSTRAINT [FK_GraduateDiscipline_GraduateName] FOREIGN KEY([GraduateID])
REFERENCES [dbo].[Graduate] ([GraduateID])
GO
ALTER TABLE [dbo].[GraduateDiscipline] CHECK CONSTRAINT [FK_GraduateDiscipline_GraduateName]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Student_Graduates] Script Date: 03/30/2008 14:46:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student_Graduates](
[GraduateDisciplineID] [int] NOT NULL,
[StudentID] [int] NOT NULL,
CONSTRAINT [PK_Student_Graduates] PRIMARY KEY CLUSTERED
(
[GraduateDisciplineID] ASC,
[StudentID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Student_Graduates] WITH CHECK ADD CONSTRAINT [FK_Student_Graduates_GraduateDisciplineID] FOREIGN KEY([GraduateDisciplineID])
REFERENCES [dbo].[GraduateDiscipline] ([GraduateDisciplineID])
GO
ALTER TABLE [dbo].[Student_Graduates] CHECK CONSTRAINT [FK_Student_Graduates_GraduateDisciplineID]
GO
ALTER TABLE [dbo].[Student_Graduates] WITH CHECK ADD CONSTRAINT [FK_Student_Graduates_StudentID] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Students] ([StudentID])
GO
ALTER TABLE [dbo].[Student_Graduates] CHECK CONSTRAINT [FK_Student_Graduates_StudentID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MajorDiscipline] Script Date: 03/30/2008 14:46:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MajorDiscipline](
[MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL,
[DegreeID] [nchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MajorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DisciplineName] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED
(
[MajorDisciplineID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[MajorDiscipline] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID])
REFERENCES [dbo].[Degree] ([DegreeID])
GO
ALTER TABLE [dbo].[MajorDiscipline] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID]
GO
ALTER TABLE [dbo].[MajorDiscipline] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID])
REFERENCES [dbo].[Majors] ([MajorID])
GO
ALTER TABLE [dbo].[MajorDiscipline] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Student_Majors] Script Date: 03/30/2008 14:46:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student_Majors](
[MajorDisciplineID] [int] NOT NULL,
[StudentID] [int] NOT NULL,
CONSTRAINT [PK_Student_Majors] PRIMARY KEY CLUSTERED
(
[MajorDisciplineID] ASC,
[StudentID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Student_Majors] WITH CHECK ADD CONSTRAINT [FK_Student_Majors_MajorDisciplineID] FOREIGN KEY([MajorDisciplineID])
REFERENCES [dbo].[MajorDiscipline] ([MajorDisciplineID])
GO
ALTER TABLE [dbo].[Student_Majors] CHECK CONSTRAINT [FK_Student_Majors_MajorDisciplineID]
GO
ALTER TABLE [dbo].[Student_Majors] WITH CHECK ADD CONSTRAINT [FK_Student_Majors_StudentID] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Students] ([StudentID])
GO
ALTER TABLE [dbo].[Student_Majors] CHECK CONSTRAINT [FK_Student_Majors_StudentID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Minors] Script Date: 03/30/2008 14:46:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Minors](
[MinorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Minors] PRIMARY KEY CLUSTERED
(
[MinorID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Student_Minors] Script Date: 03/30/2008 14:46:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student_Minors](
[MinorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[StudentID] [int] NOT NULL,
CONSTRAINT [PK_Student_Minors] PRIMARY KEY CLUSTERED
(
[MinorID] ASC,
[StudentID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Student_Minors] WITH CHECK ADD CONSTRAINT [FK_Student_Minors_Minors] FOREIGN KEY([MinorID])
REFERENCES [dbo].[Minors] ([MinorID])
GO
ALTER TABLE [dbo].[Student_Minors] CHECK CONSTRAINT [FK_Student_Minors_Minors]
GO
ALTER TABLE [dbo].[Student_Minors] WITH CHECK ADD CONSTRAINT [FK_Student_Minors_StudentID] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Students] ([StudentID])
GO
ALTER TABLE [dbo].[Student_Minors] CHECK CONSTRAINT [FK_Student_Minors_StudentID]
ALTER PROCEDURE [dbo].[Student_Deciplines]
@studid int
AS
BEGIN
SELECT RTRIM(gradDis.DisciplineName) + ' (Graduate)' FROM GraduateDiscipline gradDis, Student_Graduates sGrads WHERE gradDis.GraduateDisciplineID = sGrads.GraduateDisciplineID AND sGrads.StudentID = @studid
UNION
SELECT RTRIM(majDis.DisciplineName) + ' (Major)' FROM MajorDiscipline majDis, Student_Majors sMajors WHERE majDis.MajorDisciplineID = sMajors.MajorDisciplineID AND sMajors.StudentID = @studid
UNION
SELECT RTRIM(mins.MinorID) + ' (Minor)' FROM Minors mins, Student_Minors sMinors WHERE mins.MinorID = sMinors.MinorID AND sMinors.StudentID = @studid
END
Some sample data:
GraduateDiscipline
GraduateDisciplineID DegreeID GraduateID DisciplineName ....
0 M.B.A. 1 Master of Business Administration
Student_Graduates
GraduateDisciplineID StudentID
0 0
MajorDiscipline
MajorDisciplineID DegreeID MajorID DisciplineName
1 B.S. Computer Science Computer Science
Student_Majors
MajorDisciplineID StudentID
1 0
Minors
MinorID Description Criteria
Computer Science blah blah blah blah
Student_Minors
MinorID StudentID
Computer Science 0
Output I'd like for StudentID 0
All
General
Master of Business Administration (Graduate)
Computer Science (Major)
Computer Science (Minor)
Hope someone can help!
View 5 Replies
View Related
Feb 27, 2015
I am doing a web app on the full text search.My select statement using CONTAINS but it doesn't seem to return all the string that contains the search word I included in the query.For example:
Select CustomerID, customername,
from CustomerTable
where CONTAINS ((CustomerID), '"430*")
the query result only return all customer id which has space in front 430 or start with 430
ex: "xxx 430xxx" or "430 xxx" or "xxx 430 xxx"
but did not return customerID which doesn't has space in front of it
Ex: x430xxx
If I use LIKE '%430%', the result return are both have space in front and don't have space in front.
View 1 Replies
View Related
Jul 20, 2005
Hi,I have boolean values in a table for ex. Federation. And I want toselect followingSelect 'Insert into' + member + 'test' as test1from federationThen I get error messageServer: Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals bit.Someone can help me out of it.Thanks an advance- Loi -
View 3 Replies
View Related
Aug 1, 2007
Hello,
I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.
View 6 Replies
View Related
Jul 9, 2015
I need to set a field value based on two date time parameter's.What is the correct syntax to allow me to pass the value into the field in my SSRS expression ?
=IIf(Parameters!EndDate > Parameters!StartDate.Value, "Overdue")
View 3 Replies
View Related
Sep 4, 2014
The FirstName and LastName values being passed in are in cyrillic text.
How do I add the N prefix in the update statement in order to store the text correctly.
I've tried FirstName = N@Firstname
or FirstName = '''N''' + @FirstName + '''
[dbo].[sp_UpdateDealerPeopleInfo](
@PersonId char(9),
@OriginalSID char(9),
@FirstName Varchar(50),
@LastName Varchar(50),
[code].....
View 8 Replies
View Related
Sep 21, 2006
When my production server processing some queries suddenly the SQL Server service crashed and following error was in the error log:
SQL Server Internal Error. Text manager cannot continue with current statement.
The server is running SQL Server 2000 with SP4.
I am really concerned because this is a production sever and there are over 300 users access concurrently.
Please help me to find a solution.
Thanks,
Roshan.
View 8 Replies
View Related
Nov 18, 2015
Slow loading issue with an if statement. In the raw data the field [Location] is a text field e.g. 0010. I have a parameterised query that get a Location_Value from Excel and passes it to the PQ query using:
#"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))
This works fine if you chose a single location. However I wanted to be able to select all locations and text is horrible to work with so in PQ I used the change type function to change the location column into whole numbers. I changed excel to also pass a number as Location_Value. I was therefore surprised when the same query took 2.5 times longer to refresh????
My PQ now looks like this
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Location", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))
I'm wondering if I need to do something to the ([Location] = Location_Value) bit as maybe it still thinks [Loation] is text and it is trying to compare it to a number. I st assumed the step above meant that [Location] was now a number, but maybe you still have wrap it with some kind of VALUES or TEXT function?
View 9 Replies
View Related
Feb 1, 2007
Need help. How to concat into 1 row from the example below:
Table Address
Block Flr Road
2 1 Green
3 - Maine
4 3 Bell
5 2 Prade
Result :
Block 2 Flr 1 Green Road
Block 3 Maine Road
Block 4 Flr 3 Bell Road
Block 5 Flr 2 Prade Road
Cheers!
View 7 Replies
View Related
Aug 31, 2007
is it possible to concatenate 2 recordsets ? i have 2 select, and i need then to be displayed one after other.using union all the results are gathered, ordered like only one select.thanks for all
View 5 Replies
View Related
Oct 1, 2007
Hi, I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar. But i am receiving the error:'Conversion failed when converting the varchar value ',' to data type int.The SQL I am trying to use is:select game_no + ',' + team_name as matchfrom result Thanks
View 2 Replies
View Related
Feb 23, 2001
I'm trying to use a variable (@sqlfields1) for building a select statement.
Can someone please help me because the variable isn't set correctly when I'm trying to concatenate it with itself (the "@sqlfields1"-variable is declared as "char(50)":
If @showo1 = 1
select @sqlfields1 = 'o1,'
If @showo5 = 1
select @sqlfields1 = @sqlfields1 + 'o5'
I want @sqlfields1 to be = 'o1, o5'.
Regards,
Abrazame
View 2 Replies
View Related
Oct 25, 2004
Please help me with this if you can.
I have one table with CustomerID and some other data.
In other table i have CustomerID(the link with the first table) and Agent
The relation of the first with the second one is ONE TO MANY.
I want something like this:
Customer,'Agent1,Agent2,Agent3'
Is it possible.
Please help :)
View 3 Replies
View Related
Jul 27, 2007
Hey all
Just like the title asks...Is it possible...I have a table that stores memos but breaks the memo text field up and assigns a key to associate it all as one big text field in the application. What I am needing to do is to create a custom table for a customization to have the memo txt in one row per customer, per memo number. So, is there any way to concatenate memotext into one row per memo?
Thanks
tibor
View 14 Replies
View Related
Aug 31, 2007
is it possible to concatenate 2 recordsets ?
i have 2 selects and i want to display all results from the first
select and then the results from the second select query
with paging.
if i use union, all the results will be mixed.
thanks for the help!
View 7 Replies
View Related
Sep 29, 2012
Is there a way to concatenate the values of a particular field together into one field for all the records in each group of a GROUP BY without using XML?
Ideally, I want to accomplish this in a stored procedure. Normally, I would handle this in the front end, but my front end is Crystal ...
For example, if my data was:
1 "A"
1 "B"
1 "C"
2 "D"
2 "E"
I want to group by the first value and concatenate the second value:
1 "A,B,C"
2 "D,E"
Very easy using code - I simply add a function to the query in Access (not very efficient, but effective), or using recordsets in the app (which is actually very efficient) except my front end is Crystal in this case .
View 2 Replies
View Related