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
ADVERTISEMENT
Mar 31, 2015
I have a query that shows me a list of what employees that are on site assuming that employee badged in correctly. My problem is I need to know when an employee has two entries in a row that are "In" without have an "Out" entry.
For example, if John badges in at 8:00 Am and leaves without badging out, when he arrives the next day at 8:00 AM and badges in the system simply would show him as in with no record of him ever leaving correctly.
I am not sure how to return the correct result. Can I formulate a query that would display a Who's In list where the previous entry was NOT and Out?
View 16 Replies
View Related
Jun 20, 2006
I am doing some calculations with a table and would like to know how to reference a previous row without having to alter the dates in a query at the start of each month.
For example I have to find a percentage change for a series of values. The table has three columns, is based on end of month dates, and the "return" column is the calculated column where the value is. In the example the return value for date 2006-04-30 is Index of (2006-04-30/ Index 2006-03-31)-1.
Date Index return
2006-03-31 6535 .05130
2006-04-30 6949.29 .06333
View 3 Replies
View Related
Sep 28, 2007
Good morning,
I'm trying to use an existing SQL table to calculate the labour value of products being manufactured.
I was hoping to use a CASE statement with the logic; If the part number of the next row is equal to the part number of current row and the operation number of the next row is greater that the operation number of the current row then take the labour value of this row and add it to the labour value of the next row - does that make sense?
However, to do this I need to be able to query the next (or previous) row of data in the table and I don't know if this is possible.
If anyone knows a simple way (I'm not exactly a SQL whizkid) to do this I'd be most appreciative of your help and advice.
Many thanks,
Paul
View 7 Replies
View Related
May 19, 2008
I have three coloumn in Salary Table
Emp ID, Emp Salary , Sequence
a1 1000 1
a1 2000 2
a1 2000 3
a2 4000 1
a2 5000 2
a2 5000 3
a2 6000 4
Now I have to calculate the count on salary if the previous salary is different then count +1 else is previous salary same then add +0.
so output be
EmpID and Updation in Salary
a1 = 2 and for a2 =3
Can anyone help me with the query or storeprocedure i can achieve this output counting according to previous data.
View 9 Replies
View Related
Jan 9, 2001
Good afternoon one and all,
I am trying to write a query that will use tables in more than one SQL database (on the same server). How do refer to the table? I have tried dbasename.tablename.fieldname but I don't think that's working.
TIA for any and all help
Gurmi
View 3 Replies
View Related
Dec 5, 1999
I have created a job which performs linked server inserts, the job is scheduled to run every 15min. The Job inserts to a local linked server, some data from one of our log tables. That linked server inturn gathers more data from other servers and exports that data to another application for further processing.
My problem is that I need to reference local machine (DNS Name) as a variable which can be passed along with the linked server insert. If I can get the Server name dynamically I can run this as a remote job on 2 or 20 or 200 servers and It will give the log data and the name of the source server.
QUESTION: How Do I reference the server name from within TSQL as a variable.
View 1 Replies
View Related
Jul 27, 2004
HI Friends,
Can anybody help in this prob with SQL server Enterprise Manager
Prob:
I cannot use alias name spelled same as the table field-name, in a view. For eg:
SELECT Name AS name
FROM dbo.test
if I take this view in the design view in Enterprise Manager, it will changed to:
SELECT Name
FROM dbo.test
Can anybody help me How can I avoid this???? I need the same alias name with different case....
Nish
View 5 Replies
View Related
Nov 20, 2006
Hello All,
I have the following code:
USE MLS
select sc.name,f.field#,fdesc,flong
from sysobjects so join syscolumns sc
on so.id = sc.id
join fld f
on f.field# = replace(sc.name,'_','')
where so.name = 'dbo.tbl_MLS_Leads_Trans'
I am trying to get the description which is flong and I get the following error message:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'fld'.
What am I doing wrong?
TIA
Kurt
View 2 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
May 20, 2004
When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement:
ie,
SELECT fieldname1 =
CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END
, fieldname2
, fieldname3
FROM tablename1
GROUP BY CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END, , fieldname2, fieldname3
etc.
The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression".
Regards,
Dave
View 5 Replies
View Related
Aug 17, 2007
Hi, Im trying to run this query
UPDATE DataModif
SET t.Ind_des = Replace (t.Tit_Des,"'",'"')
FROM Tit_Modificables t
WHERE
t.Ind_num in
(SELECT CAST (t2.Ind_num AS VARCHAR(10))
FROM Tit_Modificables t2
WHERE t2.Emp_id ='1100004' AND t2.Ejercicio_fiscal = 2003 AND t2.Nom_tabla = CAST (10 AS NUMERIC))
but ir sends this message error
The multi-part identifier "t.Ind_num" could not be bound.
I don't know if there is a problem because I'm using an Update.. FROM
Any help?
View 6 Replies
View Related
Apr 28, 2006
Hi,
When I try execute one query in SQL 2k5, with alias in order by clausule, I retrieve the follow message:
Server: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "alias.fields" could not be bound.
Where alias is a any alias and, fields is a field of the table with alias.
Already exists one fix to patch this?
Thanks
View 39 Replies
View Related
Mar 15, 2006
Hi,In oracle I have a LAG function using which I could get the previousvalue of a field.Do we have anything similar to that in SQL Server or access?ThanksDevi
View 4 Replies
View Related
Jan 17, 2007
Im trying to execute following update SQL:
UPDATE Property SET ImageList = U.ImageList
FROM Property M
INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID
= U.FeedID
WHERE M.FeedID = ?
But following error:
[Execute SQL Task] Error: Executing the query " UPDATE Property SET
ImageList = U.ImageList FROM Property
M INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID
AND M.FeedID = U.FeedID WHERE M.FeedID = ?" failed with
the following error: "The multi-part identifier "M.FeedID" could
not be bound.". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.
ByPassPrepare is set to TRUE and ParameterName = 0 to variable User::Feed_ID
HOWEVER - following query executes fine:
UPDATE Property SET
ImageList = U.ImageList
FROM Property M
INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID
= U.FeedID
WHERE M.FeedID = 11
Beats me - any help with explaining this to me
please?
View 7 Replies
View Related
Mar 15, 2008
record.
For example. I have a table with fields named "From" and "To" which track drill hole measurements. When I add a new record I would like it to autopopulate the "To" field with the "From" value from the previous record.
Example Recordset
Record 1, From=0, To=500, Lithcode=AB1
Record 2, From=500, To=1000, Lithcode=CR4
Record 3, From=1000<- trying to figure out how to auto populate previous record's from value here
TIA
Todd
View 6 Replies
View Related
Nov 20, 2006
Hi Everyone
This is the query and I am getting follwoing error message
"The multi-part identifier "InvDate.Account Reference" could not be bound."
SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]
FROM CUSTOMERCONTACTNOTES AS CCN,
(SELECT *
FROM CUSTOMER) AS CC,
(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])
FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]
GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE]
ORDER BY CC.COMPANY ASC
By the way its SQL Server 2005 Environment.
Mitesh
View 4 Replies
View Related
Aug 12, 2014
I have 1 table with some empty product codes that I have imported from a txt file. And I would like to fill in the empty product codes by taking the previous product code
date product_code
01/01/14 | BIC01 |
03/01/14 | |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | |
03/01/14 | CASH |
I though of using the LAG function but the table does not have any specific order.So after running the select, the table should look like:
date product_code
01/01/14 | BIC01 |
03/01/14 | BIC01 |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | CASH |
03/01/14 | CASH |
View 5 Replies
View Related
Mar 16, 2008
Was wondering if there is easy anyway to autopopulate a field by pulling a value from a field in the previous record?
For example, I have a table with fields name "Dist_From" and "Dist_To". When I add a new record I would like it to populate the "Dist_From" field with the "Dist_to" value from the previous record.
TIA
Todd
View 3 Replies
View Related
Sep 4, 2007
Hi guys,
I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once.
The desired result should by something like:
Stock.REFERENCE
JurnalTransMoves.SUF
SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE
5752
10
60
5752
20
5752
30
5753
400
3000
5753
500
5753
600
5753
700
5753
800
5754
7
15
5754
8
Is there any chance to do that?
Thanks in advance,
Aldo.
Code Snippet
SELECT
Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME',
Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY',
Accounts.FILTER AS 'ACCOUNTS.FILTER',
Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP',
AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME',
Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode',
CreditTerms.DETAILS AS 'CreditTerms.DETAILS'
CreditTerms.CURRENF AS 'CreditTerms.CURRENF'
CreditTerms.MONTH AS 'CreditTerms.MONTH',
CreditTerms.DAYS AS 'CreditTerms.DAYS',
CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC',
CreditTerms.TEMF AS 'CreditTerms.TEMF',
CASE
WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x'
WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y'
ELSE 'Undefined' END AS 'VAT',
Stock.DOCUMENTID AS 'Stock.DOCUMENTID',
DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',
CASE
WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE
ELSE '' END AS 'Invoice No',
Stock.VALUEDATE AS 'Stock.VALUEDATE',
JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION',
JurnalTrans.REF2 AS 'JurnalTrans.REF2',
JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF',
JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF',
JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID'
FROM
JURNALTRANSMOVES AS JurnalTransMoves_1
INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves
INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID
INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY
INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID
INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY
LEFT OUTER JOIN ITEMS AS Items
INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY
INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID
LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE
LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE
LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE
Accounts.SORTGROUP Between '3001' And '3020'
AND Accounts.ACCOUNTKEY IN ('123456')
ORDER BY Accounts.ACCOUNTKEY
View 22 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
Nov 29, 2000
Data from as400 imports into SQL with blank fields which is the way as400 outputs records. How can you insert previous record of data null or blank field. ex:
ONETWO
a1
2
3
b1
2
3
Would want:
ONETWO
a1
a2
a3
b1
b2
b3
View 5 Replies
View Related
Jan 18, 2007
I need to write a t-sql query that will take the value of the previousrecord into consideration before calculating the current row's newcolumn value...Here's the situation...I have a query which return the following table structure...Full_Name Points----------------- ------------Name1 855Name2 805Name3 800Name4 775Name5 775Name6 741etc.... etc...I need to create a calculated column that tells me where the personranks in point position. The problem i run into is that in thesituation where two or more people have the same point value i need thecalculated rank column to display the same rank number (i.e. 4th orjust "4") I'm not sure how to to take into consideration the previousrow's point value to determine if it is the same as the current onebeing evaluated. If i new they were the same i could assign the samerank value (i.e. 4th or just "4").If any one has any insight that would be great.ThanksJeremy
View 2 Replies
View Related
Jun 28, 2005
I have a field which has 4 items separated by a hypen. I need to split this into four different fields. Trying tsql down below, works for the 1st but not the next two
select substring (xmlsite, 1, patindex( '%-%' , xmlsite) -1 ) 'loc1'
,substring (xmlsite, patindex( '%-%', xmlsite) + 1, len(xmlsite))'loc2',
substring (xmlsite, patindex( '%-%', xmlsite) + 1, len(xmlsite))'loc3'
FROM table1
data looks like Cen-2cmed-edf-4c
View 3 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
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
View Related
Jan 14, 2015
My goal is to update the "PriorInsert" field with the "DateInserted" from the previously inserted record where the WorkOrder, MachineNo, and Operator are all in the same group.
While trying to get to the correct previous record, I wrote the query below.
P.S. The attached .txt file includes a create and insert tbl_tmp sampling.
select top 1
a.ID,
a.WorkOrder,
a.MachineNo,
a.Operator,
a.PriorInsert,
[code]...
View 2 Replies
View Related
Nov 24, 2015
We will be moving 2 different databases (SS2005 & SS2008) to a new SS2014 SQL Server. Currently our codes looks something like Server1DBInstance1... & Server2DBInstance2... Is it possible to move the objects from these 2 instances to Server3DBInstance3 and then use an alias to reference the objects? Or does Server3 need to have DBInstance1 & DBIstance2? Basically, is the alias just for the database or for the instance too? Can I create an alias "Server1DBInstance1' on Server3DBInstance3 and assign objects to that alias?
View 16 Replies
View Related
Jan 15, 2004
I am trying to read an Informix 9.3 blob field into a TSQL (SQL Server Stored Procedure) and the data type conversion is not supported thru the MS ODBC Driver installed with Win2000/SQL2000. Does anyone know of another method to retrieve this data, or of a vendor that provides an ODBC driver that supports the Informix blob data type. Thanks!
View 1 Replies
View Related
Sep 10, 2013
Need getting a query which I will get previous year, previous month first day everytime i run the query.
Ex: If i run the script on 9/10/2013 then result should be 8/1/2012. (MM/DD/YYYY)
View 4 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
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
