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

Transact SQL :: Find All Stored Procedures That Reference Oracle Table Name Within Server OPENQUERY Statement

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

Subtracting Adjacent Rows.

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

How To Get Reference From CompositePrimaryKey Field ???...

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

Can You Reference The Data In Another Field?

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

Transact SQL :: Cross Reference 2 Tables

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

Transact SQL :: Return Field When A Field Contains Text From Another Field

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

Latest Record For Each Reference Field

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

Advanced Transact-SQL Reference Book - Need Recommendations!

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

Transact SQL :: Find Circular Reference In The Table

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

Transact SQL :: Replace A String Using Reference Table

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

How To Reference A Previous Field Alias In TSQL As In Jet SQL?

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

Reporting Services :: Merging Adjacent Cells Using Expression In SSRS

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

Transact SQL :: Database Constraint Check On Table With Reference Key

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

Transact SQL :: NVARCHAR Field Getting Truncated After Increasing Field Length?

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

SQL 2012 :: DTSX Giving Errors - Object Reference Not Set To Instance Reference

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

Transact SQL :: Server Text Field Not Returning Full Text

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

Transact SQL :: Only Allow Certain Values In A Field

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

Transact SQL :: Way To Count Changes In Value Of A Field

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

Transact SQL :: How To Get Value Of A Particular Field From XML Data

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

Transact SQL :: List All Available Values Of A Field?

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

Transact SQL :: Manipulating Full Name Field

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

Transact SQL :: How To Extract Date Value From XML Field Value

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

Transact SQL :: How To Compare Two Database Field

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

Transact SQL :: Value In Field Not Returned By WHERE Clause

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

Transact SQL :: Parse Name Field Based On A Delimiter

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

Transact SQL :: Use Field Alias In Calculated Fields?

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

Transact SQL :: Replacing Invalid Characters In A Field

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

Transact SQL :: Inserting Character At The Beginning And End Of A Field

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

Transact SQL :: GROUP By Field Syntax Error

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

Transact SQL :: Get Week Number From DateTime Field?

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

Transact SQL :: Divide A Number By Field And Date

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

Transact SQL :: Query DateTime Field By Date Range

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







Copyrights 2005-15 www.BigResource.com, All rights reserved