SQL Server 2008 :: Selecting Field That Contains Data From Another Field
May 28, 2015
We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.
ie.
Code Description Brand
ABC1 BLANK DVD SONY
ABC2 SONY BLANK DVD SONY
what I need to do is identify where the Brand is in the Description field ...
I have tried ;
select * from Table
where Description Like Brand
not very successful.
View 3 Replies
ADVERTISEMENT
Feb 19, 2007
With this query:
SELECT Table1.1ID, Table1.Field1, Table2.2ID, Table2.Field1, Table3.3ID, Table3.Field1, sum(Table1.Field1), sum(Table2.Field1), sum(Table3.Field1)
FROM Table1 INNER JOIN
Table2 ON Table1.1ID = Table2.2ID INNER JOIN
Table3 ON Table2.2ID = Table3.3ID
I get this error:
Column 'fieldname' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Is it because you can't do what I'm trying above or am I missing something?
Thank you!
--PhB
View 1 Replies
View Related
Jul 1, 2015
We have a custom web C#/SQL2K8R2 workflow application that I need to pull Oracle data into a varchar(max) field as an XML DOM document. I have no problem pulling the Oracle data using OLEDB, but I'm not sure how to create the XML DOM doc. Once I get it into the DOM doc, I then need to assign metadata about the XML DOM doc and insert it all into a staging table:
CREATE TABLE [stg].[EtlImports](
[EtlImportId] [int] IDENTITY(1,1) NOT NULL,
[EtlSource] [varchar](50) NOT NULL,
[EtlType] [varchar](50) NULL,
[EtlDefn] [varchar](max) NULL, --Either a SQL statement or path to file on disk
[EtlData] [varchar](max) NULL, --BLOB field to hold the XML data or FILESTREAM path to file on disk
[EtlDateLanded] [datetime] NOT NULL,
[EtlDateProcessed] [datetime] NULL,
[EtlStatus] [varchar](50) NULL,
[Comments] [varchar](4000) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I will have a separate SSIS package to pull the [XML/File] field and process the data into the workflow tables. Is there a wasy I can use the ADO Record-set Destination task to accomplish this, or do I have to create a custom C# script to create the XML DOM Doc?
View 0 Replies
View Related
Jul 11, 2005
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies
View Related
May 31, 2006
I have a problem selecting maximum values inside a data flow, when the field is varchar. For example, I could have an incoming text file with following rows (the real life problem is a bit more complicated, but the idea is the same)
ID Desc
1 Car
1 Truck
1 Bicycle
2 Horse
2 Cow
Now I would like to group by ID, and have the maximum value from the Desc field, resulting
ID Desc
1 Truck
2 Horse
I tried to sort the data flow first with ID ascending and Desc descending, followed by sort with ID ascending and "Remove duplicate sort values" turned on. However, the sort does not seem to select the first Desc value it gets, because the result is.
ID Desc
1 Bicycle
2 Horse
If you have any hints, how to tackle this problem, please help!
View 3 Replies
View Related
May 17, 2015
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
View 3 Replies
View Related
Nov 25, 2007
Hi,
I have one table (PermProportionalCosting) that contains employeecode, costcentre and proportionpercentage. An employee may have two or more records in this table, indicating that their salary has been split across multiple costcentres.
Another table (Employee) contains a default costcentre for each employee.
Another table (TransActualHistoricalMaster) contains the hours paid to each employee for each pay period.
My query so far selects all employees and the total hours worked from the TransMaster table, and then selects the costcentre and proportionpercentage from the PermProportionalCosting table. Multiple lines are created for all employees in the PermProportionalCosting table, with each line indicating the proportionpercentage and associated costcentre.
This is my query so far:
SELECT TransActualHistoricalMaster.EmployeeCode, Employee.PreferredName, Employee.LastName, CostCentre.Description, PermanentProportionalCosting.CostCentreCode,PermanentProportionalCosting.PercentageSplit,
TransActualHistoricalMaster.OrdHours
FROM TransActualHistoricalMaster
INNER JOIN Employee ON TransActualHistoricalMaster.EmployeeCode = Employee.EmployeeCode
INNER JOIN CostCentre ON Employee.CostCentreCode = CostCentre.CostCentreCode
FULL OUTER JOIN PermanentProportionalCosting ON TransActualHistoricalMaster.EmployeeCode = PermanentProportionalCosting.EmployeeCode
GROUP BY TransActualHistoricalMaster.EmployeeCode, Employee.PreferredName, Employee.LastName, CostCentre.Description, PermanentProportionalCosting.CostCentreCode, PermanentProportionalCosting.PercentageSplit, TransActualHistoricalMaster.OrdHours
This next bit is what I am unsure of.
I would like the CostCentre field from the Employee table to go into the PermanentProportionalCosting.CostCentreCode column where the employee doesn't have a record in the PermanentProportionalCosting table. In the above query, there is a null value for each employee that doesn't have a record in the PermanentProportionalCosting table, so I would like my query to replace the null value with the costcentre from the Employee table.
Any assistance would really be appreciated.
Thanks.
View 3 Replies
View Related
Mar 25, 2008
How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???
View 3 Replies
View Related
Oct 20, 2015
3 table join query
Problem : city name the same
View 6 Replies
View Related
May 24, 2015
preparation of a query to get the data from xml which has xmlns in first few lines.
Here I am attaching the xml file(not able to attach ".xml" file, so converted it to ".txt" ).
View 9 Replies
View Related
Dec 5, 2007
i want to import/copy a varchar field numeric data in to number field pls suggest the solution
one thing more can i convert field type of a table how?
jto it
View 5 Replies
View Related
Apr 2, 2008
Hi all experters,
Please suggest how to build the report in below case:
Raw data:
ID
Member
Functions
1
Alan
A
1
Alan
B
2
Tom
A
2
Tom
B
2
Tom
C
3
Mary
D
3
Mary
E
Report Shows:
ID
Member
Functions
1
Alan
A,B
2
Tom
A,B,C
3
Mary
D,E
I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.
Any good suggestion?
Thanks in advance,
Steve Wang
2008/4/2
View 6 Replies
View Related
Jan 29, 2015
I have a table returning results like that
Row1 ||Row2 ||ERPID||ParentID||LevelID||Category||SubCategory||DDate ||publish
1 ||1 ||10152159||1015 ||2159 ||LOCTITE ||LOCTITE1||29/01/2015 12:10||0
1 ||2 ||10152134||1015 ||2134 ||LOCTITE ||LOCTITE2||29/01/2015 12:10||0
1 ||3 ||10152157||1015 ||2157 ||LOCTITE ||LOCTITE3||29/01/2015 12:10||0
2 ||1 ||10062003||1006 ||2003 ||COMPUTER||COMPUTER1||29/01/2015 12:10||1
[code]....
But I want to look like that
Row1||Row2||ERPID||ParentID||LevelID||Category||SubCategory||DDate||publish
1||1||10151015||1015 ||1015||LOCTITE||||29/01/2015 12:10||0
1||1||10152159||1015 ||2159||LOCTITE||LOCTITE1||29/01/2015 12:10||0
1||2||10152134||1015||2134||LOCTITE||LOCTITE2||29/01/2015 12:10||0
1||3||10152157||1015||2157||LOCTITE||LOCTITE3||29/01/2015 12:10||0
2||2||10061006||1006||1006||COMPUTER||||29/01/2015 12:10||1
[code]....
View 3 Replies
View Related
Mar 19, 2015
I have a field which looks like "LastName, FirstName (DOB: 01/01/1900)"
How do I get the "01/01/1900" between ":" and ")"
View 6 Replies
View Related
Apr 23, 2015
I have the Person table with the following fields:
Id_Person
Nm_Person
Id_AddressResidential
Id_AddressCommercial
Another table called Address with the following fields:
Id_Address
Ds_Street
I want to make the relationship between these tables so that when deleting a related field address in Person is set to null.the SQL Server allows me to make this relationship in only one field.A person can live and work in the same place. If I delete her address, I want the two Individual fields are set to null.
View 5 Replies
View Related
Nov 2, 2015
If I have a datetime field, RequestDate, and a time field, DecisionTime, how do I add just the date portion of RequestDate to DecisionTime?
View 6 Replies
View Related
Feb 12, 2015
I have 2 databases: one for our intranet & another for our internet website.In the intranet database, we have a table called "Clients". A new client can be added in one of 2 ways:
1) One of our employees manually adds the client via the intranet
2) A new customer subscribes to our services via our website, thus becoming a new "client" (when subscribing online, we also add a record to an "Accounts" table located in our internet website database, but we'd also like to add a record to our intranet's Clients table as well).
In the client table is a field called "CreatedBy" which expects the UserId of whoever created the account. Again, this UserId can belong to either an employee via the intranet or a new customer via our internet website. how do I distinguish where the UserId in the dbo.Clients.CreatedBy field is coming from?
My 1st thought was to append a negative to the CreatedBy UserId# if it came from a customer via the website, but that just seemed too quirky (i.e. if an employee added a client and his UserId was '10', the Clients.CreatedBy would be '10'. If a customer subscribed via the website, the Clients.CreatedBy would be '-10'. Where customers can once again sign up online. So, I need a way to keep these databases separate but keep track of which db a user's coming from when we create a new Client record.
View 0 Replies
View Related
Jun 10, 2015
I would like to pull data from two seperate columns based on the vaule for MakeFlag. So if MakeFlag = 0 I would like the description to show but anything else I would like catalog description to show up.
DECLARE @MyVari varchar(20)
SELECT [ProductID]
,[prod].[Name]
,[ProductNumber]
,[MakeFlag]
[Code] ....
View 1 Replies
View Related
Jun 26, 2015
I need to have a table that has a primary key
CREATE TABLE [dbo].[testing](
[tid] [int] IDENTITY(1,1) NOT NULL,
[sometext] [varchar](150) NOT NULL,
[idcopied] [varchar](50) NULL,
CONSTRAINT [testing_PrimaryKey] PRIMARY KEY CLUSTERED
..and eveytime I add 'sometext' as another row, the tid # needs to be duplicated to idcopied field
insert into [testing] (sometext,idcopied) values ('some junk',@@identity)
???
View 4 Replies
View Related
Sep 6, 2005
CREATE PROCEDURE {databaseOwner}{objectQualifier} [PreciseData_IssueTracker_GetAllIssues]@moduleId intASSELECT PreciseData_IssueTracker_Issue.id AS 'issueId',PreciseData_IssueTracker_Status.name AS 'statusName',PreciseData_IssueTracker_Issue.subject AS 'subject',PreciseData_IssueTracker_Type.name AS 'typeName',Users.Username AS 'assignedUserName',Users.Username AS 'raiserUserName',PreciseData_IssueTracker_Issue.raiseDate AS 'raiseDate'FROMPreciseData_IssueTracker_Issue INNER JOIN PreciseData_IssueTracker_Status ON PreciseData_IssueTracker_Issue.statusId=PreciseData_IssueTracker_Status.idINNER JOIN PreciseData_IssueTracker_Type ON PreciseData_IssueTracker_Issue.typeId=PreciseData_IssueTracker_Type.idINNER JOIN Users ON Users.UserID=PreciseData_IssueTracker_Issue.assignedUserIdINNER JOIN Users ON Users.UserID=PreciseData_IssueTracker_Issue.raiserUserIdWHERE PreciseData_IssueTracker_Issue.moduleId=@moduleIdORDER BY PreciseData_IssueTracker_Issue.raiseDate DESCGOHow do i make it work?
View 1 Replies
View Related
Sep 7, 2006
For a select statement like:
select f1, max(f2), f3 from t group by f1
how do I say 'f3 from the same record that you found the max of f2'?
Thanks!
View 4 Replies
View Related
May 12, 2006
Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin
View 1 Replies
View Related
Feb 1, 2011
I've got two databases on the same server and replicate some tables from one database to another.The replication is configured so not to drop the table if it exists, but to delete the data based on the filter if one exists. There are two tables on the subscriber that have some extra columns.
I get "field size too large" error when trying to replicate them. Is there a workaround without having to make the publisher and the subscriber tables identical by schema?
View 5 Replies
View Related
Mar 24, 2015
I have a table with 201 columns . I am importing 200 columns from a file using DFT. I want update the 201th column with the fileId of the file that just imported. I am storing the fileId of the file in varFileID .
How do I go back and update the 201th column ( column name sfileId) with the varFileID value?
I can use Execute SQL Task but how will I know it's the records of the files that I just imported not other rows.
View 0 Replies
View Related
Apr 16, 2015
I'm pulling individual address fields into my script, some of which are null.
I would like to add in a carriage return after ONLY those fields that are NOT NULL.
Current script is:
SELECT
ISNULL(SAD.HOUSENUMBER,'') + ISNULL(SAD.ADDRESS1,'') + ISNULL(SAD.ADDRESS2,'') + ISNULL(SAD.ADDRESS3,'') + ISNULL(SAD.CITY,'') + ISNULL(SAD.COUNTRY,'') + ISNULL(SAD.ZIP,'') as 'FULL_ADDRESS'
FROM
SFAADDRESS SAD
View 5 Replies
View Related
Oct 26, 2007
I have FeaturedClassifiedsCount field, which I would like to update each time record is selected. How do I do it in stored procedure on SQL 2005?
This is my existing code:alter PROCEDURE dbo.SP_FeaturedClassifieds
@PageIndex INT,
@NumRows INT,
@FeaturedClassifiedsCount INT OUTPUT
AS
BEGIN
select @FeaturedClassifiedsCount = (Select Count(*) From classifieds_Ads Where AdStatus=100 And Adlevel=50 )
Declare @startRowIndex INT;
Set @startRowIndex = (@PageIndex * @NumRows) + 1;
With FeaturedClassifieds as (Select ROW_NUMBER() OVER (Order By FeaturedDisplayedCount * (1-(Weight-1)/100) ASC) as
Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCountFrom
classifieds_Ads
WhereAdStatus=100 And AdLevel=50
)
SelectId, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount
From
FeaturedClassifieds
Where
Row between@startRowIndex And @startRowIndex+@NumRows-1
END
View 1 Replies
View Related
Jul 26, 2015
I have the following query:
Select p.Id [SenderId], p.Username, up.PhotoId,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage,
p.LastLoggedIn, p.LoggedIn
[Code] ....
The above query returns me all messages (inbox functionality) that have been sent to mr.ReceipientId, the issue I have is when I send another email to the recipient the readdate field will be null, and the other emails linked to the recipient which have also been sent via me will have a readdate date. This causes duplicate rows to appear due to the case statement, I'm trying to figure out if / how it is possible to only display the one row per conversation and set newmessage to 1 if there is an un-read message otherwise show 0 ?
So instead of showing this:
2Person102015-07-26 17:07:24.9370
2Person112015-07-26 17:07:24.9370
I'm trying to get it to look like this:
2Person112015-07-26 17:07:24.9370
Or if we have no new mail (read date is not null) then it will appear like this, and I can confirm when there is no new mail it works as expected.
2Person102015-07-26 17:07:24.9370
View 1 Replies
View Related
Aug 11, 2015
I have about 100 K records of the form below in Example 1 and I would like to turn them into the form of Example 2, basically turn the entries in field2 into a coma separated list of values sorted by field1.
Example 1:
field1_field2
1_____a
1_____b
1_____c
2_____f
2_____g
and I would like to get it in the form
Example 2:
field1_field3
1_____a,b,c
2_____f,g
View 2 Replies
View Related
Sep 22, 2015
I have a field I am trying to bring into a SQL statement
,ISNULL(Convert(nvarchar(50),OPP1.OriginatingLeadId),'') AS 'OriginatingLeadId'
I get this error message
Conversion failed when converting from a character string to uniqueidentifier.
the field specs' originatingleadid is attached ....
View 9 Replies
View Related
Oct 7, 2015
SQL code for the following? (SQL Server 2008 R2 - SQL Server 2012).
I have Table1 Containing two fields with the below entries
VehicleType Name
Two Wheels Bicycle
Two Wheels Scooter
Two Wheels Motorcycle
Four Wheels Sedan
Four Wheels SUV
Four Wheels Pickup
Four Wheels Minivan
The result I'm looking for would be
Table2
Vehicle Type
Two Wheels Bicycle, Scooter, Motorcycle
Four Wheels Sedan, SUV, Pickup, Minivan
View 1 Replies
View Related
Oct 1, 2010
When creating xml fileformat its throwing me error "invalid ordinal".
When created non-xml file format, no error, and was also able to load data file into sql table. Not sure why bcp (Version: 10.50.1600.1) is not able to create xml file format.
C:>BCP "MyGDB.dbo.Items_Import" format nul -f"C:AnkitTempBCPItemsMaster.xml" -x -w -T -S"(Local)"
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid ordinal for field 2 in xml format file.
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
Item Numbervarcharno18 noyesnoSQL_Latin1_General_CP1_CI_AS
Description1nvarcharno80 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
Description2nvarcharno80 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
UMvarcharno3 yesyesyesSQL_Latin1_General_CP1_CI_AS
View 1 Replies
View Related
Mar 13, 2006
I need to do a SQL query based on the two biggest values of a field. PLS , what s the SQL syntax : select * from Table where PRICE ......Ex: if the values of the field PRICE are : 50, 40, 100, 30 and 150.The request should select the rows that have Price equal 100 or 120Thanks a lot for help
View 2 Replies
View Related
Mar 13, 2006
I need to do a SQL query based on the two biggest values of a field. PLS , what s the SQL syntax : select * from Table where PRICE ......
Ex: if the values of the field PRICE are : 50, 40, 100, 30 and 150.
The request should select the rows that have Price equal 100 or 120
Thanks a lot for help
View 5 Replies
View Related
