I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
) Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
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
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
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'
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'
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?
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 .
I would like to concatenate a String value to an Int value using an SQL statement. At the moment it reads like this:
SELECT 'website.com/shop/product.cfm?ProductID=' + Products.ProductID AS Product_URL
But unfortunately I am getting the error: "Conversion failed when converting the varchar value 'website.com/shop/product.cfm?ProductID=' to data type int."
Any idea how to get around this at all just using an SQL query statement?
-- Prepare sample data DECLARE@Sample TABLE (ID INT, Code VARCHAR(3))
INSERT@Sample SELECT290780, 'LT' UNION ALL SELECT290780, 'AY' UNION ALL SELECT290781, 'ILS' UNION ALL SELECT290780, 'AY'
-- Show the expected output SELECT DISTINCTs1.ID, STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES FROM@Sample AS s1 ORDER BYs1.ID
SELECT DISTINCTs1.ID, STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES FROM@Sample AS s1 ORDER BYs1.ID
SELECT DISTINCTs1.ID, STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES FROM@Sample AS s1 ORDER BYs1.ID
hi.Trying to concatenate two columns:select uname+' '+uaddress as NameAdr from tblUserI only get the first field, name!!??The datatype is both nvarchar. 100 and 50 chars long.If I run this query:select uname, uaddress from tblUsertheres no problem.Any suggestions?ThanksSWN
I have a big problem, as I commented in other thread in this forum I Have a DataReader with an MDX query which right now runs out of memory, but I figured that if I made the query by range I could get what I need without running out of memory. However, to achieve that I need to concatenate at the end of a file with a Flat File destination, the result of every query, but I don't know how to do it, It always writes over the data.
Date ID Name Job Number JobType 12/12/2007 123456 Fred Smith 111111 Full Day 12/12/2007 654321 Bob Blue 222222 Half Day AM 12/12/2007 654321 Bob Blue 333333 Half Day PM
I need the following output: Date ID Name Job Number JobType 12/12/2007 123456 Fred Smith 111111 Full Day 12/12/2007 654321 Bob Blue 222222 Half Day AM 12/12/2007 654321 Bob Blue 333333 Half Day PM
Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222
Could someone please point me in the right direction?
Hi All, I have a very simple question which I can't seem to find an easy way of doing in SSIS. (Alternatively I may be having a "Can't see the wood for the trees" moment )
I have a folder where files are deposited on a regular basis . (Every few seconds)
I want to take those files and concatenate them into a single file in another directory ready for processing.
The equivelent command prompt line would be copy c: estfiles*.* c: estfilescatteddata.dat
How would I be best doing this in SSIS? Also I could do with working out the best way to quiesce the folder so I don't get any locked files errors.
Is there any way of concatenating 2 fields and a seperator, an id (integer) then "|||" then a nvarchar field, it would make my life much easier if I could come up with SQL that works in ms access as well as sql server.. Thanks
Hi all I need some help in concatenatng a string in T-SQL. Having used the Command Microsoft Access inside the 'SQL View' window and typed the following it worked perfectly.
Code Snippet
UPDATE tblValidUsers SET blocked_users = blocked_users + 'name_123@hotmail.com;' WHERE userid='Onam'
However attempting the same command in T-SQL I get the following error:
Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals text.
Reason for having this command is I want to be able to add something to the end of the field "blocked_users" without actually overwriting the fields contents.
So for instance if I had the items: "Item1, Item2, Item3" in blocked_users and I updated it with "Item4" then the value "Item4" would be added to the end thus the use of "+" is used to concatenate. Is there a way of doing this in T-SQL?
Hi everyone, I'm writing a stored procedure where I need to concatenate two XML strings. I would normally write XMLx = XMLy + XMLz However because the variables are in XML SQL2005 doesn't like this and if I convert them to strings the XML structure gets lost. Anyone any ideas ? Thanks D.
I am creating a random-id in this way: declare @rand_id intset @rand_id=(SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))) print @rand_id
now I want to add "-07" to the end of the random number generated. if 12345 is the random number, i want it to be 1245-07. Any idea how to concatenate the "07" to this random number?
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.
I have a View that I'm using to power a Crystal Report. I want to include an extra column that will be a string containing all the values from a single column of another table, seperated by comma.
I am trying to concatenate three columns and when one column might be NULL, the result set is NULL. I have set "concat null yields null" to false and the result is the same. Here is my code:
I have a table within a table that contains descriptions of work done as strings. Some of these need to be edited after a report is generated so it needs to be exported to Excel.
As everyone knows Excel can't process a table within a table so I need a way to concatenate all the separate row entries in the nested table to one Field entry so the nested table is no longer necessary.
Is there a way to recursively concatenate strings from the report designer? The number of rows that need to be concatenated is variable so it has to be flexible.