Guys, this is what I want to do. How can I do this ?
Select employid,
Gross=( Select sum (uprtrxam) from Fleet..upr30300 where pyrlrtyp=1),
DedofWages=( Select sum (uprtrxam) from Fleet..upr30300 where
pyrltyp=2),
Gross-DedofWages
from FLEET..UPR00100
The problem is the third field (Gross-DedofWages). It says Invalid
column. Any ideas ?
Thanks, Girish
firstly, how do u write a derived field in sql table, e.g you are following a logical design and you are calculating a score for a match table.
secondly, if a foreign key is being used as a primary key in another table, do u have to set it to NOT NULL ?
I have the following 2 fields that are sourced from an Excel spreadsheet
DocNumber - a 10 digit number
PostingRow - a number between 1 and 999
I would like to produce a new column that is a concatenation of these two fields, but the PostingRow needs to be a 3 digit number eg. 1000256153-001 ....
how to declare multiple derived columns in SSIS Derived Column Task in one attempt.as i have around 150 columns coming from Flat file. I had created the required Expression in Excel and now i want add those in derived column task but its allowing only 1 expression at a time.
View 4 Replies View Related
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies View RelatedI am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View 1 Replies View RelatedPardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
regards.
Julio Diaz.
OK....I know how to write a query to return for example :
All the people that ordered X and Y
but how do I write one for:
All the people that ordered X but not Y?
Thanks,
Trey
I have 3 tables TableA , TableB and TableC
I have to update tableA with the value of TableB by checking A.Field=B.Field
and TABLEB as condition that B.Field should exists in tableC
Update A Set A.Field=B.Field From tableA A
(select B.Field From TableB B where B.Field=A.Field and B.Field in (select C.Field
From tableC C))
How to do it correctly.
Thanks in advance
Hi, wondering if anyone can help me. I currently have a field that has a date and time in it in the format dd/mm/yyyy hh:mm:ss. Ideally I would like to get rid of the time part of it altogether but for it to still be recognised as a date as opposed to a string. However, I've been told that this is not possible in 2005, is this true? If this is the case, what would be the best way to set the time to 00:00:00 after the date for all records on that field?
View 1 Replies View RelatedHi. We have to create an export from our system to be imported into another system. To get the data out we need to create some SQl but we're struggling a bit.
I presently have the following code
SELECT ProSolution.dbo.StudentDetail.RefNo, ProSolution.dbo.StudentDetail.FirstForename, ProSolution.dbo.StudentDetail.Surname,
ProSolution.dbo.StudentDetail.MobileTel, ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID, ProSolution.dbo.Enrolment.CompletionStatusID,
ProSolution.dbo.Offering.Code
FROM ProSolution.dbo.StudentDetail INNER JOIN
ProSolution.dbo.Enrolment ON ProSolution.dbo.StudentDetail.StudentDetailID = ProSolution.dbo.Enrolment.StudentDetailID INNER JOIN
ProSolution.dbo.Offering ON ProSolution.dbo.Enrolment.OfferingID = ProSolution.dbo.Offering.OfferingID
WHERE (ProSolution.dbo.StudentDetail.AcademicYearID = '06/07') AND (ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID = '9') AND
(ProSolution.dbo.Enrolment.CompletionStatusID = '1')
The above code returns the data one line per course but we need it to be one line per student with all their courses on one line too, like follows.
567897 Tom Smith 07111 111111 TCFT1 CKSAN1 DHICS
Can anyone give us any guidance please?
Thanks
Chip
I have two columns made up of 4 digits numbers eg col1 1234, col2 9876
I want to create a derived column so i get a eight digit column, eg col3 12349876
I cannot seem to get the expression right, I seem to be always adding the value which i do not want, can someone help me out with the expression
Thanks
Robbie
I am trying to transfer data from SQL Server 2005 table to another SQL Table. In the source table there is a field called Region [nvarchar(max)]. The values for these fields will be like APAC-China, NA-Racine, etc., i.e Region followed by the country name seperated by - symbol. I want the destination tablre with 2 fields Region[nvarchar(max)] and Country [nvarchar(max)]. I am using a Derived Column task to achieve the same.
Can anyone please help me out in doing this data conversion?
Thanks
Is there a way to group by a derived value? For example, in the query below, is there a way to group the results by the AgeBracket field? I'd like to see adults (0) in one grouping and children (1) in the other.
SELECT AgeBracket = CASE WHEN dbo.GetAge(DOB, GETDATE()) > 18 THEN 0 ELSE 1 END, COUNT(*)
FROM tableX
Thanks!
Hi,
I am doing a task which will get all data based on several base and crosswork tables. I used stored procedure to get the data, but the next step will be using derived column to massaging data, and to load to the destination database. Is there a way to do that since I must generate data on the fly?
Thanks,
Megan
Hi Guys,
How can i put in a derived column the value of 3 columns? I've tried these:
[MyId]+[Paper1]
"[MyId]+[Paper1]"
but no luck. How can i put it right?
Thanks
Gemma
Is there a way to group by a derived value? For example, in the query below, is there a way to group the results by the AgeBracket field? I'd like to see adults (0) in one grouping and children (1) in the other.
SELECT AgeBracket = CASE WHEN dbo.GetAge(DOB, GETDATE()) > 18 THEN 0 ELSE 1 END, COUNT(*)
FROM tableX
GROUP BY ?
Thanks!
I am trying to create a derived column from two separate fields. One is a date field in format of 2008-03-01 and one is a time field. In DTS I use to have something like this:
DTSDestination("TransactionDate") = cdate(DTSSource("DSRHDATI")) & " " & timevalue(DTSSource("DSRHTIME"))
I am trying to figure out how to do this in SSIS. I know I have to use a dervived column but can't seem to come up with the correct syntax. Any help would be apprecaited. thanks.
Stacy
i have a column in my table that will store the sum of 3 other column, e.g the data in total_book column will be equal to the sum of the number in the romance, fiction and thriller column. how do i do this?
I would like to creat a new column called Load_Dt and hard-coded as 06/30/2006 ( date data type) in derived transformation... how can i do this?
Hi,
I have built a database for a university project, but am having trouble with the SQL syntax for inserting derived data from a calculation into a table. At present my SQL is;
INSERT INTO MemberPayment (TotalCharge) VALUES ( [Total] )
SELECT ((MileageHistory.MileageUrban * Vehicle.EmissionsPerGramUrban) * 0.05) + ((MileageHistory.MileageCountry * Vehicle.EmissionsPerGramUrban) * 0.05) AS 'Total'
FROM [NeuCar].[dbo].[MileageHistory] JOIN [NeuCar].[dbo].[Vehicle]
ON MileageHistory.Registration = Vehicle.Registration
JOIN [NeuCar].[dbo].[Member] ON Vehicle.UserName = Member.UserName
WHERE Member.UserName = 'wenger1' AND (MileageHistory.[Date] >= CURRENT_TIMESTAMP - 30);
Would anybody be able to point out where I am going wrong? I would very much appreciate any advice,
Kind regards,
Chima
Hi Folks,
Is there any way to add a derived column into the where clause.
Example:
Select Name, Date, Procedure#,
(Case When Procedure# in ('1','2','3') then 'Y' else 'N') AS Class
From Procs
Where Class = 'Y'
Thanks,
Ray
Hi everyone,
I have a quizzing application where users log in, answer questions, and are ranked relative to each other. For this final ranking, I calculate their score using this formula -
score = (correct Qs answered) * 150 / (total Qs answered) + (total Qs answered)
The SQL query that i use to get this info is -
Code:
SELECT TOP 50 username, (sum(correct) * 150 / count(1) + count(1)) AS score, count(1) as totalq
FROM questionsstats
GROUP BY username
ORDER BY score DESC
This works just fine.
However, on top of this I need to put an additional restriction that only users who have at least answered 20 questions be counted in. How can I do this? Adding a simple 'WHERE totalq > 20' does not work. I get the error "Invalid column name 'totalq'.".
Surely there must be a simple way to do this?
Thanks.
I got an error as follows:
Derived table 'A' is not updatable because a column of the derived table is derived or constant.
when I tried to run this query:
update A set MonthsUnbilled =99999888
FROM (select MonthsUnbilled from dbo.vw_MasterView
WHERE (RecordID =8377396)) A
This is a simplified query in order to pinpoint the culprit. I know I don't need to use a derived table if the real query is this simple.
Thanks in advance!
Hi,
Here's my current query, which throws an error that "AgeCalc" is an invalid column in the WHERE clause:
---------------------------------
SELECT
.
.
.,
AgeCalc =
CASE
WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
THEN datediff (year, B.DOB, B.DateIn) - 1
ELSE datediff (year, B.DOB, B.DateIn)
END
FROM
ResidentData B
WHERE
(AgeCalc >= 18)
---------------------------------
How do I do conditionals on the "AgeCalc" derived column?
Thanks.
Below is my sql, i am getting error msg "Incorrect sytax near 'Votes'."
Can anyone tell me what im doin wrong?
UPDATE #TblExposure
SET E.Unity =
(
SELECT
-- DealId ,DocketId,
CASE Participant2
WHEN nullTHEN 'NA'
ELSE
CASE WHEN Participant2-Vote2 =0 THEN 'Yes'
ELSE 'No'
END
END AS Unanimous1
FROM #tblVoting
) Votes
FROM #tblExposure E INNER JOIN Votes
ONVotes.Dealid = E.Dealid
AND Votes.DocketId =E.DocketId
Two questions regarding Derived Columns in SSIS
1. In a if else expression if condition is false how do you keep the value of the source column eg: Name == "" ? "Unknown" : Name
Above will change all the non blank values to Name and not the actual value
in the Name Column eg. John
2. I have a column (unicode string)that stores date and time (The source is flat file) Is it possible to write expression to select the 1st day of month based on that date, and use this derived column as input to a table with a datetime field.