Transact SQL :: Server Reference Field From Adjacent CTE
Oct 22, 2015
I am trying to eliminate a scalar function by rewriting a view. I currently have a structure similar to the following:
With FirstQuery as (SELECT Trial, SBOI, TBOI,
FROM TU
WHERE SBOI Between 22000 and 22999 and TBOI < 10000),
strt as (SELECT TOP 1 TimeOfEvent, Trial, SBOI, TBOI
From TU ORDER BY TimeOfEvent),
[Code] ....
What I trying to do is for the Trial, SBOI, and TBOI in FirstQuery, find the Start Time and Stop Time. TimeOfEvent is an integer in milliseconds. Say I have the following data:
TrialID SBOI TBOI TimeOfEvent
A 22000 5000 5
A 22000 5000 10
B 22000 5000 8
B 22000 5000 15
So the DISTINCT output would be:
Trial SBOI TBOI StartTime StopTime
A 22000 5000 5 10
B 22000 5000 8 15
The problem I am having is that strt is selecting the smallest time period. It doesn't seem to care what the current record in cte is. I thought that by using my joins, it would make it select smallest TimeOfEvent for the Trial, SBOI, and TBOI that are selected in cte. Obviously that is not the case. So, I was trying to add a WHERE to the strt Select Statement such as
WHERE TrialID = FirstQuery.TrialID and SBOI = FirstQuery.SBOI and TBOI = FirstQuery.TBOI but it isn't working either.
What can I do?
View 5 Replies
ADVERTISEMENT
Aug 10, 2015
One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
View 2 Replies
View Related
Aug 22, 2007
Hello forum.
How do I go about subtracting adjacent Rows? Let's say each day I entered one row entry into a table (tblFlowerHeight)showing the new height of a flower in one column(Height)...say if I had a second second column (ChangeFromLastHeight), how would I go about showing the difference between current height and the last recorded height?
Please assist.
View 5 Replies
View Related
Mar 8, 2007
I have one table name Magazine_Details, it has a composite primary keyCreate table Magazine_Details
(
MagazineTitle varchar(60),
MagazineType varchar(20),
IssueType varchar(25),
IssueNumber varchar(10),
Qnty int,
Qnty_in_Hand int,
[Date] datetime
constraint PK_Magazine_Details_Composite primary key(MagazineTitle,IssueNumber)
)
Another table is Magazine_Issued and i want reference from Magazine_Details table. but i am auable to create foreign key constaraint in Magazine_Issued table.create table Magazine_Issued
(
Member_ID varchar(100),
MagazineTitle varchar(60) references Magazine_Details(MagazineTitle),
IssueNumber varchar(10) references Magazine_Details(IssueNumber),
IssueDate datetime,
Magazine_Status varchar(10)
)
View 3 Replies
View Related
Dec 5, 2007
Is it possible to reference the data in another field within the same table?
Let's say that one column has a calculation, and a second column has a different calculation. Can the third column find the difference between the first two columns?
(I'm wondering if this is possible to do within the Layout View, not from the Data View)
Thank you!
View 3 Replies
View Related
Jul 28, 2015
I have 2 tables, OBJECTS and LINKS both have common field(OBJECT_ID).
I need to update certain records in table OBJECTS only if they meet certain criteria in table LINKS.
How do I go about doing this..???
View 8 Replies
View Related
Aug 25, 2015
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
View 8 Replies
View Related
Feb 19, 2014
I have a table that contains a Ref field and a TransactionDate field. For each Ref field there are mutliple Transactions. I am trying to put together a query that selects the Latest Transaction date for each Ref field in the table.
I tried:
SELECT *
FROM tabMediaTransaction
WHERE (TransactionDate =
(SELECT MAX(TransactionDate) AS Expr1
FROM tabMediaTransaction AS tabMediaTransaction_1))
But this only returns 1 record for 1 Ref field. I need 1 record for each Ref field
View 7 Replies
View Related
Jul 20, 2005
I currently have two SQL server books for MS SQL Server 2000. One is aprep book for the 70-229 exam, the other is a Wrox book: "professionalSQL Server 2000 Programming."I'm looking for more T-SQL books that give me PRACTICAL tips onwriting advanced queries. What book do you refer to? Please post them.
View 3 Replies
View Related
Jun 1, 2015
Create table #tblActvity
(
activityIDvarchar (50),
activityParentIDvarchar(50)
)
Insert into #tblActvity
SElect '1',Null
[Code] ...
--If I pass activityId 3 or 2 or 4 it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....
I need a sql qry which will require to find a circular reference in it.....
As in above sample of data ,If I pass activityId 3 or 2 or 4 to qry it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....
View 4 Replies
View Related
Jul 21, 2015
I would like to create a function that will replace a string using a reference table
I have a table : reference
ID String ReplaceWith
1 ≈ &
2 < <
3 > >
If I pass a string into a function, function needs to replace a string with replace with string column in reference table
For example, if I pass a string : car $ap; fjld
The function should return car & fjld
How can i create a function like this so that i can call it in Stored procedure....
View 12 Replies
View Related
Jun 1, 2006
My question is simple, I'd like to do something I do in Jet ANSI-89 SQL. Mind you I'm just adding numbers here - they are not actual columns in 'SomeTable'
SELECT 1 AS A, 2 AS B, A+B AS C
FROM SomeTable
The Jet engine evaluates and does arithmetic on the Aliased column names - handy when they contain their own functions. The resultset would show:
A B C
1 2 3
However from what I can tell SQL Server 2005 is not picking this up. Is their an equivalent?
View 1 Replies
View Related
Feb 11, 2011
Is that possible to merge adjacent cells conditionally in SSRS 2008?
For Example
Col A ColB
Col C ColD ColE ColF
If condtion is true
ColC and Col D to be under Col A and ColE and Colf to be under Col B
Or Col C to be under Col ColA and Cold to ColF to be undercol B
View 6 Replies
View Related
Jun 11, 2015
CREATE TABLE PRODUCT_SALES
(
Salesmanid BIGINT,
Productid BIGINT
)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,1)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,3)
SELECT * FROM PRODUCT_SALES
/* SalesmanID is reference key from Sales Master and ProductID is reference key from Product Master. How should i restrict user through DB with any constraint check, if user tries to enter
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2),
It should throw error , if possible user defined message would be more useful.
View 7 Replies
View Related
Aug 28, 2015
I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
Inserted Value -> Result in table
123456-8 -> 123456-8 *Correct
1234567-9 -> 1234567- *Wrong
123456789 -> 123456789 *Correct
1234567#9 -> 1234567# *Wrong
1234567a9 -> 1234567a9 *Correct
Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?
I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.
View 3 Replies
View Related
Sep 10, 2014
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
View 0 Replies
View Related
Apr 21, 2015
I have a column in a table that has a type TEXT,when I pull the length of a row it returns 88222 but when I select from that column it dows not show all the text in the result set.
View 3 Replies
View Related
Nov 16, 2015
I can not modify DDL of the table, so creating a trigger etc is not an option. This is my syntax that I am using, and each #Testing.abcd has different possible options.
For example, when #Testing.abcd = Test1 then #Data.field2
okay values are 'Yes', 'No', 'Maybe', but for #Testing.abcd = Test3 then #Data.field2 okay values are 'Follow-Up', 'Contact Requested'
How can I set-up "acceptable values" for one field, but different based off abcd in my syntax?
Declare abcd varchar(100), @sql varchar(1000)
Create Table #Testing (abcd varchar(100), efgh varchar(500)
Insert Into #Testing Values
('Test1', 'zzz'),
('Test2', 'xxx'),
[Code] ....
View 5 Replies
View Related
Oct 13, 2015
I want to avoid iterating through a table row by row and programmatically incrementing a counter,/I have define a view called Q2 with the following fields of interest:
Trial varchar(25)
BI int
Track int
CAT int
What I would like to do is partition by Trial, BI, and Track, and count the number of times CAT changes value. In Access, I would just iterate through a recordset inside a function, but I would like to see if there is a set based solution.Adding Sample Data and Output.
Trial BI Track CAT
A 5 3 1
B 5 3 2
[code]...
View 5 Replies
View Related
Nov 6, 2015
We are running the following querry which insert data row from an XML source
INSERT INTO End_ReelProcessValue
(LineId
,ReelHist_id
,Variable_tagName
,ProcessMeasure
)
[Code] ....
From that querry, I need to store in a variable the Value of a particular XML entry in order to store it in an other table.The XML parsing is based on the following lines :
[Variable_Tagname] CHAR(50) '@Name',
[ProcessMeasure] NVARCHAR(254) '@Value'
What I need is that if the [Variable Tagname] issue from entry @Name is equal to the string "Quality", then I need to store its value to a variable.Then an other querry will use that variable to insert it to an other table.
How can I retrieve that particular record ?
View 2 Replies
View Related
Aug 10, 2015
I have [TableAccount.AccountType] field which can store these values:
Value
C
S
E
How do I list all available values for a field? Using Distinct can only list all entries in the db, but does not list all values available for the field.
View 4 Replies
View Related
Sep 22, 2015
I have a column with Full Names (e.g. Jane Doe)...how can I transform it via t-sql for end resutl Doe, Jane?
View 8 Replies
View Related
Oct 5, 2015
I am having an XML field in a table "RMRECC" (field name: "Amount") and the value of the field is given below.
<X C="0" I="1">
<E D="1Y0M0W0D" P="0" A="0.05" />
<E D="1Y0M0W0D" P="0" A="0" />
<E D="1Y0M0W0D" P="0" A="0.03" />
</X>
I need to extract the date value "1Y0M0W0D" in MM/DD/YYYY format.how this can be handled in SQL query?
View 2 Replies
View Related
Sep 23, 2015
I have 2 database, databaseA and DatabaseB with same table structure.
I change lot of table structure in databaseB.
So i want compare what field that i add or delete in databaseB.
it's like what field there are in databaseB and not found in databaseA or nothing in databaseB but exist in databaseA.
How can i do that?
View 7 Replies
View Related
Nov 18, 2015
I have a small table named Exclude_Cusno_Item that has a varchar(29) field named
ITEM. When I query the table to show all of the records like this:
Select * from Exclude_Cusno_Item
The result set contains a record with 'AAA' in the ITEM field. However, where I write a query with a WHERE clause to select this specific 'AAA' record like this:
Select * from Exclude_Cusno_Item where item = 'AAA'
I get no results. What could be happening that would prevent the 'AAA' record from appearing in the result set of this query? Note that the 'AAA' entry has no trailing or leading blanks.
View 12 Replies
View Related
Sep 18, 2015
how to separate names but i cannot make work in this case. The name field might contain anywhere from only one name with no delimeters to five names with four delimeters. I want to replace the delimeter with a space and reorder the names.
Original data format: Name2/Name1/Name3/Name4/Name5.
Desired data format: Name1 Name2 Name3 Name4 Name5.
Examples of source data
Company ABCDoe/JohnSmith/Jim/EtalJones/Jeff/Jr/& Sally
Bush/Jim/Sr/Etal/Trustee
View 43 Replies
View Related
Jul 18, 2015
use Northwind
Go
select
dbo.Orders.OrderID
,Cast(dbo.Orders.OrderDate As DATE)Order_Date
, dbo.Customers.CustomerID
, dbo.Customers.CompanyName
, dbo.Products.ProductName
[code].....
I cannot use the alias field names as part of additional calculations for new columns.
total_Amount and Grand_Total cannot be done with my skill level.
View 9 Replies
View Related
Oct 6, 2015
When we are getting data in a table and we want to replace characters with other characters. For example, We have a table with a street address, and there are numerous ascii character values we want to review and replace if they exist. We were looking at using a table with 2 columns, 1 containing each ascii character value the other it's preferred replacement value. Then updating the street address searching through each ascii character and replacing it if needed. Currently, we are running it through a looping process searching each individual address for each ascii character, and updating it.
View 4 Replies
View Related
Sep 17, 2015
How to modify values in a SQL Database. In the table PRDDEmpmaster there is a field called email_address, and for those fields that have a value I need to change that value to add { } opening/closing "curly braces" to that field.
For example,
RPARSONS@YAHOO.COM becomes
{RPARSONS@YAHOO.COM} . Nulls/empty fields remain as is.
View 6 Replies
View Related
Oct 8, 2015
I have the following querry:
SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
,PIT.ProductItemCode
,PIT.Name,
[Code] ....
That query is suppose to add to calculation field OutStock and InStock based on the value of n
When executing this query I get the following message :
Column 'Sales.ActionPlan_History.ReturnDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
View 3 Replies
View Related
Aug 21, 2015
Any easier way to do what I am trying, without having a table with all the dates and week numbers.
Scenario: Week number of a DateTime Field where the year does not start on January 01<sup>st</sup> but April 1<sup>st</sup> to 31 Mach.
Issue: A week always starts on a Monday so if the 1<sup>st</sup> is on Tuesday, the first week is Tuesday – Sunday, if the 1<sup>st</sup> April is on Friday, the 1<sup>st</sup> week is Friday – Sunday and 7 day periods from there.
CREATE
TABLE [dbo].[DailyCanx](
[ID] [int] IDENTITY(1,1)
NOT NULL,
[DateCancelled] [datetime] NULL
)
ON [PRIMARY]
[code]....
I could create a table with all the start date and end dates of all the week numbers but I think there must be a better way. using SQL Server 2008 R2.
View 5 Replies
View Related
Jul 7, 2015
I have this rows as result of my query:
aaaaa bbbbb date result
------- ------- -------- ---------
a 100 7-7-15
a 100 7-7-15
a 100 8-7-15
b 200 7-7-15
b 200 8-7-15
b 200 8-7-15
I need this (divide bbbbb by row count as result, group by "aaaaa and date":
aaaaa bbbbb date result
------- ------- -------- ---------
a 100 7-7-15 50
a 100 7-7-15 50
a 100 8-7-15 100
b 200 7-7-15 200
b 200 8-7-15 100
b 200 8-7-15 100
Can I do it?
My SQL Code is
(date=FEC, RESULT=COSTECUB, aaaaaa=TRACTA, bbbbb=TRAPR3) :
SELECT VTRA00F.TRACTA, ACRDES, TRAPRL, TRAPR1, TRAPR2, TRAPR3,
CASE WHEN TRAPR3 IS NULL THEN
(CASE WHEN (ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2) <= TRAPRL THEN (TRAPR1*(ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2)) ELSE (TRAPR2*
[Code] .....
View 8 Replies
View Related
Dec 2, 2015
I have a table of errors with a DateTime field for when the error occurred. I want to query the table for a given date range omitting the time portion. What is the most efficient way to perform this query?
View 5 Replies
View Related