I need to create a derived column for each row in a SQL dataset.
This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0).
One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ?
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A B C D E F G H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
I want to have an other employee table named employee_modified
Empno empname salary commission derived_column1(salary+commission) derived_column2(derived_column1 + xxxx) and so on derive other columns based on the earlier derived columns)
Is that possible to do it.. or am I doing something wrong.
I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g.
Table 1
Org
aaa:aaa:aa
aaa:aaa:ab
aaa:aab:aa
Table 2
delim
Seg1Start
Seg1Len
Seg2Start
Seg2Len
Seg3Start
Seg3Len
:
1
3
5
3
9
2
My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work.
Any ideas? Can this be done in SSIS?
I'd really appreciate any insight that anyone might have.
I have a MS SQL table with a derived column, for date the records were imported, and need to delete records, based on the content of this column. What I need to do is delete all records from the table with a date of '2011-11-18'. Now this column is a datetime column, so it contains the time info after the date, i.e. 2011-11-18 09:29:38.000, but no matter what command I try for this:
-Delete from table where Date_Imported like '2011-11-18%' -Delete from table where Date_Imported like '2011-11-18' -Delete from table where Date_Imported = 2011-11-18
It comes back saying "0 rows affected", even though I know there are records with that date in the table.
I have 3 different companies that share the same ticket_types(CRMS System). I need to display the Ticket Types and the 3 company's Ticket Count:
Ticket Type | Company A Count | Company B Count | Company C Count
I can get the information individually for each company, but if a company doesn't have a ticket in one of the ticket_types, then it isn't displayed in a row. So, I tried to write the following, which isn't pulling back any data.
DECLARE @startdate date = '20150306' DECLARE @enddate date = '20151031' DECLARE @AcctGrp varchar(20) = '111' ;WITH TType AS ( SELECT ctp.description as TicketType
[Code] .....
If I run each SELECT individually from above (excluding the last SELECT), it works and I get the following:
TicketType AR Request Credit Availability/Rush Cancel Order Credit Card Payment Expedite Order Freight Quote
[Code] ...
How to get the query results? Am I even close to getting it right?
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.
I'm new to SQL Server and would like to add a calculated column to this query from the report writer in our ERP system based on the NextFreq case statement result.
Basically, I want to create a column called service with result as follows:
If IV.meter > NextFreq then the result should be 'OVERDUE' If (NextFreq - IV.meter) <50 then the result should be 'DUE SOON' Otherwise the result should be 'NOT DUE'
This is the code from the current report writer query:
Select IV.item, IV.meter, isnull(wt.name,0)as name, case when whh.meterstop is null then 0 end meterstop, whh.rejected, Case when cast(meterstop as int) > 0 then cast(meterstop as int) when meterstop is null then isnull(IV.meter,0) else isnull(IV.meter,0) end EndMeter, ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) as LastFreq, case when whh.rejected = 1 then ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 0 then 100 when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 100
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
Till now I get data form multiple table using join, but unable to understand how can i get the this result based on given table -
Result should be -
ProCodeProductName PRO00001;PRO00002Product Test SearchedPromotion One;Promotion Two PRO00001;PRO00002;PRO00002Product Final SearchedPromotion One;Promotion Two;Promotion Three PRO00002TestingPromotion Two
Tables - select * from ProMaster CodeName PRO00001Promotion One PRO00002Promotion Two PRO00003Promotion Three
select * from ProDetail IDProCodeProduct 1PRO00001;PRO00002Product Test Searched 2PRO00001;PRO00002;PRO00002Product Final Searched 3PRO00002Testing
I am a newbie to SQL Server. I have a problem, in filtering the records returned by a query. I have a table which contains 1 million records, it has a user defined primary key which is of character type. The problem is i need to filter the output of a select query on the table based on two parameters i send to that query. The first parameter will be the starting row number and the second one is the ending row number. I need a procedure to do this.
For Eg: MyProc_GetRowsFromBigTable(startRowNo,endRowNo) should get me only the rows in the specified range.
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB 123 colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Hi,I got another question here:I want to use query like this:SELECT * FROM (sp_lock AS T) WHERE objectID = ...The purpose is that I want to query the result set returned by sp_lockusing derived table, but it doesn't work. Why?Thanks,Baihao--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
I am firing a query like:SELECT TaskName, StartDate FROMTasksWHERE StartDate >= '01-Aug-2003'Now the result comes as:TaskName StartDate--------------------------Task1 01-Aug-2003Task5 10-Oct-2003etc.Now what I want is this:Sl. No. TaskName StartDate----------------------------------1 Task1 01-Aug-20032 Task5 10-Oct-2003How do I get the Sl. No. field (it does not exist in the table).Can I do it?-surajit
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc tblGroups - with GroupID, GroupName tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups. What I want is a list of all contacts, with their IDs, names and what groups they are related to:
Hello Friends My problem is Suppose Query is - Select * from tbl_Employee TBL_EMPLOYEE HAS ONLY TWO COLUMNS NAME,POST I need that an extra column get inserted in result through query showing serial Number with each row.So that query result look like this. Serial Name Post 1 XYZ QER 2 SDF OPO 3 WER IPO If any body knows please post the solution its urgent.
I have a query that returns a list of results from the database and insert the results into a datatable. Is there any way to add a column to the query result that has autonumbers in it. for example the first record would have 1 the next 2, the next 3, and so on?
i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!
Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file....
I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.
I'll try to simplify the problem as much as I can.
There's survey. It has different types of question answer pairs, but the problem is in Multiple Choices, Multiple Answers questions, where user can choose, for example 3 checkboxes from 5 at the web form, for the question.
tbl_results IDresult (primary) IDquestion (foreign, to tbl_question) result (string based on CSV, may contain few IDanswers, because visitor may choose few checkboxes, for example it can be "4,6,7")
Inside those tables are data (I'll put here just two questions):
Example: tbl_questions IDquestion_question 1_________Which OS do you use? 2_________Which databases do you use? tbl_answers IDanswer_IDquestion_answer 1________1_________Windows 2________1_________Linux 3________1_________Mac OS 4________2_________SQL Server 5________2_________MS Access 6________2_________MySQL 7________2_________Oracle 8________2_________Other 9________2_________No, I do not use databases tbl_results IDresult_IDquestion_results ........ 23_______1________1,2 24_______2________4,6,7 ........ Is there a possibility to make query, using columns, but also the data inside the CSV format in results (varchar or char) column?
Result of the query would be: IDquestion_IDanswer_answer 1_________1________Windows 1_________1________Linux 2_________4________SQL Server 2_________6________MySQL 2_________7________Oracle
Further queries based on this query should be able to make statistics (GROUP BY).
I have 2 independent queries which run just fine, the 2nd one returns a dollar value. I need this dollar value as a column in my first query so that I can return it in a reporting program. The 2 queries are pasted below.
SELECT dbo.vw_tphillip_Customer_Inv.InvoiceNumber AS "InvoiceNumber", dbo.vw_tphillip_Customer_Inv.TransactionType AS "TransactionType", dbo.vw_tphillip_Customer_Inv.Date AS "Date", dbo.vw_tphillip_Customer_Inv.DueDate AS "DueDate", dbo.vw_tphillip_Customer_Inv.CustomerName AS "CustomerName",
I have a stored procedure which selects results based on some date calculations.
In my table I have a status column and two date fields (Approval Date and Signature Date) If the value in the status column says approved I want to select results where approval date - signature date is less than a certain number of days.
If the status is naything other than approved i want to select results where sysdate - signature date is less than the given number of days.
I have written a Query to Pivot this data like below:
SELECT WAREHOUSE,ITEM, QTY
FROM
(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10, for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21, for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p
UNPIVOT
(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7, for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19, for20,for21,for22,for23,for24))AS unpvt
I would like to add some more code to the query, so for each FOR% column, i can put a numeric value in it. The value will be the numbers ,1 - 24 . One for each line as this represents Months Forward.
I have a set of data spread across a number of tables regarding stock market data. An example of this follows:
Market Capitalization...
Date CompA CompB 01/01/11 100 5 02/01/11 102 4
Share Price....
Date CompA CompB 01/01/11 100 100 02/01/11 101 99
Event Data...
Date Company 01/01/11 CompA 02/01/11 CompB
Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.
I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.
For instance: SELECT Event.Date, Event.Company FROM Event
how do I now say.....
SELECT MarketCapitalisation.Column WHERE Column = Event.Company AND MarketCapitalisation.Date = Event.Date.
I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).
I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.
We have a table with a couple of computed columns. The value of the computed column represents a foreign key reference into another table. We're seeing a major performance problem doing a query joining between the two tables with one of the columns, but not the other. In other words, this kind of query is very fast:
select * from TheTable A, FKeyTable B where A.ComputedColumn1 = B.KeyColumn
but this one sends the CPU usage of SQL Server to 99% for a very long time:
select * from TheTable A, FKeyTable B where A.ComputedColumn2 = B.KeyColumn
The main difference we can see that the computed column that causes problems is based on a UDF, and the other one isn't (but again, both are computed). When I look at the execution plan, the slow query shows a Nested Loop (Inner Join) with a "No Join Predicate" warning, with the estimated # of rows being 70 million (which correponds to the product of 1016 rows in TheTable and 69K rows in FKeyTable). The fast query doesn't have that warning, and shows 1016 rows (the # of rows in TheTable).
Does anyone know why the usage of a UDF would induce this horribly inefficient join behavior? Anything we can do to fix it?
