Referencing Data From A Query
Sep 30, 2006
I'm quite new to using t-sql, so hopefully the answer to this should be fairly simple... I have the following basic stored procedure:
Quote: CREATE PROCEDURE dbo.SP_Check_Login (
@arg_UserEmail VARCHAR(255),
@arg_UserPassword VARCHAR(255))
AS
BEGIN
SELECT a.userArchived,a.userPasswordDate
FROM app_users a
WHERE a.userEmail = @arg_UserEmail
AND a.userPassword = @arg_UserPassword
END;
GO
What I want to do is some conditional statements on the query results.
i.e:
IF (userArchived = 1)
RETURN "Archived"
ELSE IF (userPasswordDate < dateadd(month,-3,getdate())
RETURN "UpdatePassword"
ELSE
RETURN "OK"
So firstly how to I reference the data returned by the query, and secondly am I on the right track with the conditional code?
Thanks, Mike
View 2 Replies
ADVERTISEMENT
Jun 15, 2006
For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table.
-------Employees table--------
EmployeeID . . . . . . . . . . int
Name . . . . . . . . . . . nvarchar(50)
ManagerID . . . . . . . . . . . int
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager.
How can that be achieved?
View 6 Replies
View Related
Nov 24, 2005
I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005
SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2
This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it.
The only way how to avoid the issue in the SQLExpress seems probably to be
- Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss)
- Utilize computed columns if possible (no idea on performance impact here)
Am I right or is there any other way how to reference a calculated field?
Any suggestion is greatly welcomed! Thanks in advance.
View 1 Replies
View Related
Sep 13, 2007
I need a statement that can return a hierarchy of folders. There are a number of good articles out there describing how to do this in SQL 2005 with a Common Table Expression (CTE) that references itself under a UNION ALL. Yet every example I come across involves a self-referencing table. Unfortunately the architect of my project did not use that simple structure. Instead, we have a Folder table and Folder Properties table. Creating a child Folder involves adding a row in the Properties table that links to a parent. Here's the statement I came up with:
WITH Folders (ID, ParentID, FolderName, FolderDepth)AS ( SELECT FOLDER_ID, NULL, FOLDER_NAME, 0 FROM ZZ_FOLDER WHERE FOLDER_ID = 1 UNION ALL SELECT F.FOLDER_ID, Folders.ID, F.FOLDER_NAME, Folders.FolderDepth + 1 FROM ZZ_FOLDER F INNER JOIN ZZ_FOLDER_PROP FP ON FP.FPROP_FOLDER_ID = F.FOLDER_ID WHERE FP.FPROP_LINK_ID = Folders.ID )SELECT * FROM Folders
The initial statement brings back the top of the folder structure. The recursive statement brings back Folder rows that have an entry in their Property rows for the parent. Yet when I try to execute this, I get:
Msg 4104, Level 16, State 1, Line 1The multi-part identifier "Folders.ID" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "Folders.ID" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "Folders.FolderDepth" could not be bound.
It sounds like all the recursive references to the CTE are unavailable. What am I doing wrong? Would welcome any suggestions, too. I realize this isn't a T-SQL specific question, more SQL in general, but I didn't see a more relevant forum. What follows are some tables/data to run the above statement against.
-- Create Folder TablesCREATE TABLE ZZ_FOLDER ( FOLDER_ID INTEGER NOT NULL PRIMARY KEY, FOLDER_NAME VARCHAR(50))CREATE TABLE ZZ_FOLDER_PROP ( FPROP_ID INTEGER NOT NULL PRIMARY KEY, FPROP_FOLDER_ID INTEGER NOT NULL, FPROP_LINK_TYPE VARCHAR(50), FPROP_LINK_ID INTEGER NOT NULL)GO-- Populate Folder TablesINSERT INTO ZZ_FOLDER VALUES (1, 'Top Level')INSERT INTO ZZ_FOLDER VALUES (2, 'Second Level A')INSERT INTO ZZ_FOLDER VALUES (3, 'Second Level B')INSERT INTO ZZ_FOLDER VALUES (4, 'Second Level C')INSERT INTO ZZ_FOLDER VALUES (5, 'Third Level A1')INSERT INTO ZZ_FOLDER VALUES (6, 'Third Level A2')INSERT INTO ZZ_FOLDER VALUES (7, 'Third Level A3')INSERT INTO ZZ_FOLDER VALUES (8, 'Third Level B1')INSERT INTO ZZ_FOLDER VALUES (9, 'Third Level C1')INSERT INTO ZZ_FOLDER VALUES (10, 'Third Level C2')INSERT INTO ZZ_FOLDER VALUES (11, 'Fourth Level A3A')INSERT INTO ZZ_FOLDER_PROP VALUES (1, 2, 'Folder', 1)INSERT INTO ZZ_FOLDER_PROP VALUES (2, 3, 'Folder', 1)INSERT INTO ZZ_FOLDER_PROP VALUES (3, 4, 'Folder', 1)INSERT INTO ZZ_FOLDER_PROP VALUES (4, 5, 'Folder', 2)INSERT INTO ZZ_FOLDER_PROP VALUES (5, 6, 'Folder', 2)INSERT INTO ZZ_FOLDER_PROP VALUES (6, 7, 'Folder', 2)INSERT INTO ZZ_FOLDER_PROP VALUES (7, 8, 'Folder', 3)INSERT INTO ZZ_FOLDER_PROP VALUES (8, 9, 'Folder', 4)INSERT INTO ZZ_FOLDER_PROP VALUES (9, 10, 'Folder', 4)INSERT INTO ZZ_FOLDER_PROP VALUES (10, 11, 'Folder', 7)GO/*Top Level Second Level A Third Level A1 Third Level A2 Third Level A3 Fourth Level A3A Second Level B Third Level B1 Second Level C Third Level C1 Third Level C2*/
View 3 Replies
View Related
Apr 23, 2006
Hello All
I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @THeader" . @THeader is the name of the variable table and the error occurs with such references as @THeader.ApplyAmt, @THeader.TransactionHeaderID, etc.
declare @THeader TABLE (
TransactionHeaderID [int] NOT NULL ,
PatientID [int] NOT NULL ,
TransactionAllocationAmount [money] NOT NULL ,
ApplyAmt [money] NULL ) - create table variable
insert into @THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable
UPDATE @THeader
set TransactionAllocationAmount =
(SELECT isnull(Sum(mtblTransactionAllocation.Amount),0)
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or
mtblTransactionAllocation.CRID = TransactionHeaderID) from @THeader, mtblTransactionAllocation - do the updates on the table variable
Update @THeader
set ApplyAmt = (SELECT mtblTransactionAllocation.Amount
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and
mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @THeader, mtblTransactionAllocation - do the updates on the table variable
- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @THeader.ApplyAmt
UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = @THeader.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = @THeader.ApplyAmt
FROM @THeader, mtblTransactionHeader
WHERE @THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table
Thanks in advance
smHaig
View 1 Replies
View Related
Jan 14, 2008
Hi,
I have a datasource to an SQL server table which is used in a dataset with the following sql
e.g.
select *
from tblcustomer
I need to join tblcustomer to another SQL table located on a different server and database (datasource)
how would i go about doing this?
I tried the below which failed, anyone can help?
select *
from tblcustomer LEFT JOIN server1.database1.tbluser on server1.database1.tbluser.userid = tblcustomer.userid
View 4 Replies
View Related
May 31, 2007
Hello
I have referrenced System.Data.SqlServerCE.dll to my web project in order to create a .sdf file. I haven't wrote any code,just referenced it. When I build the project I get this error:
Error 2 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' cannot be loaded.
View 2 Replies
View Related
Dec 8, 2006
Hello everyone,
This is my first time posting here, I hope this questions has not been asked before. I tried to search for it but I came not with nothing.
Recreating the error :
I am using VS2005. I created a Pocket PC 2003 project.
I have downloaded the SQL Server Compact Edition and installed it. I get the System.Data.SqlServerCe.dll file from the installation directory.
I reference to that DLL using Add Reference in VS2005.
Build it. In the Bin folder, a long list of files suddenly appears.
System.data.dll
System.data.oracleClient.dll
system.web.dll
system.enterpriseservices.dll
system.enterpriseservices.wrapper.dll
system.transactions.dll
and the rest of your original files in Bin
The worst of it all, all of these files are deployed into the Emulator! Causing it to run out of memory and unable to deploy.
Something is not right here, I just cannot figure it out! If this happens, each mobile devices can hold one applications. Thats not the way it should be, right?
If you have solved this before, do help. I am at my wits end at the moment.
Thanking you in advance.
Sincerely,
Lasker
View 1 Replies
View Related
Aug 4, 2015
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL
SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code
FROM dbo.ADSCHL AD
INNER JOIN TESTCEP FD
ON AD.SCHOOL_NAME = FD.School_Name
View 10 Replies
View Related
May 12, 2008
How do i insert data into multiple tables. Lets say i have 2 tables: Schedules and Event
Schedules data is entered into the Schedules Table first
then now i need to insert Event table's data by refrencing the (PK ID) from the schedules table.
How do i insert data into Event table referencing the (PK ID) from Schedules Table ?
Fields inside each of the tables can be found below:
Event Table
(PK,FK) ScheduleID
EventTitle
AccountManager
Presenter
EventStatus
Comment
Schedule Table
(PK) ID
AletrnateID
name
UserID
UserName
StartTime
EndTime
ReserveSource
Status
StatusRetry
NextStatusDateTime
StatusRemarks
View 2 Replies
View Related
Nov 16, 2006
I have a SqlDataSource - The select statement
selects the DB row with an ID that equals a querystring value.... So I
know I am only selecting one row..Now I want to create a little
Sub that grabs any field(s) of my choice and then I can assign the
value of it to a textbox on my page or a variable if I need to... Any
idea how I actually reference the fields via the SQLDataSource in a
sub? I see you can reference parameters using..SqlDataSource1.SelectParameters()I was hoping I could use something similar to get the actual DB fields like the below...SqlDataSource1.Select("MyDataBaseField").Value = TextBox.TextCan anyone help please??Thanks
View 7 Replies
View Related
Mar 13, 2007
The Background:
From page 1 a search is created and this value is converted to a querystring and given the name "id=" and passed to page 2. Page 2 then does a lookup in the sql 2000 db useing this querystring.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ListingDBConnectionString %>"
SelectCommand="SELECT [ID], [CompName], [Package] FROM [CDetails] WHERE ([ID] = @ID)">
<SelectParameters>
<asp:QueryStringParameter Name="ID" QueryStringField="id" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
The Problem:
What I then need to do is an IF THEN ELSE statement.
IF [Package in SQL1] = 4 Then
Package 4
IF [Package in SQL1] = 3 Then
Package 3
Only how do I reference the sql value in the if statement.
I have tried
<%IF Eval("Package") = 4 Then %> and with Bind - No good
View 4 Replies
View Related
May 4, 2008
Hi all, How do I return pictures along with my search results? For example, I have a shoe database and for the results I would like one column to hold a picture of the shoe (the other columns hold Brand, Name, Price, etc) The way I am looking at implementing this is by referencing the image and retrieving it from the server. I'm a bit lost on how to do this though. Any help? Thanks in advance,Nick
View 4 Replies
View Related
Sep 1, 2007
Hi,
I'm using MS SQL 2005 Express.
CREATE TABLE Folder (
iD int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
folderName varchar(50) NOT NULL,
parentFolderID int NULL
FOREIGN KEY REFERENCES Folder (iD)
)
GO
if I add an ON DELETE CASCADE to the foreign key, then i get an error... which is annoying. If a folder is deleted, then all its sub-folders should also be automatically deleted.
The error is: 'Introducing FOREIGN KEY constraint 'FK__Folder__parentFo__7D78A4E7' on table 'Folder' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.'
Anyone got any advice?
View 1 Replies
View Related
Jul 20, 2005
I was created a function named aa.It is possible to reference to it without using the dbo. prnounce ?eg: select aa(), not select dbo.aa()thanks,Viktor
View 1 Replies
View Related
Nov 16, 2007
Hello,
I need to sum up a sub-report value, but I don't know the syntax to reference the subreport.
How do I reference a subreports value to sum it up?
Thanks,
View 7 Replies
View Related
Nov 28, 2007
I'm setting up a trigger that will fire off an email explaining that this entry has been updated, however I need a reference to the updated row so I only send that bit of information off.
Heres my code thus far:
ALTER TRIGGER [dbo].[completeCreation2]
ON [Database_Test].[dbo].[Projects]
AFTER UPDATE
AS
-- SEND EMAIL
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administration',
@recipients = 'email@email.com',
@body = 'A new project has been created view details of the project below:',
@subject = 'Project Created',
@body_format = 'TEXT',
@importance = 'High',
@query = 'Select * FROM updated';
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
END
I thought the select From updated would work but it doesn't I need a reference for that line, select * FROM _____.
Thanks
View 3 Replies
View Related
Apr 17, 2008
I am trying to run a mobile device with SQL's mobile 3.5.
When I run my application I am given an error that sqlceme35.dll plus several other references cannot be found
When I try referencing them It does not allow me too.
Is there any steps I should take to make referencing allowed?
View 1 Replies
View Related
Nov 20, 2007
Hi
This is probably a noob question. In reference to my previous post about OLE DB and variables, I need to copy the whole result set to a table. i tried
Code Block
exec('insert into '+@tableName+' ('+@ColumnNames+') '+@curTableResult)
where @tableName is the table name, @ColumnNames are the column names and @curTableResult is the Result set produced by the Execute SQL statement before. Obviously didnt work.
How do I copy the Result set to a table?
Thanks
CoyoteM
View 7 Replies
View Related
Nov 8, 2007
Hi there everyone,
A quick question. How do I reference variables (system and user) in SSIS by name instead of by just using a "?". The reason why I'm asking is because I'm trying to log what happens in a package to SQL, and if I cannot reference a variable by name it tries to insert variable values into incorrect fields?
An example of the code I'm trying to use can be found below:
Code Block
UPDATE dbo.History_Control_Table
SET
End_Date = GetDate()
, Result = 'Success'
, Status = NULL
, No_Of_Records_Processed = ? --This is where I want to tell it which variable to use
, Package_Name = ?
, No_Of_Records_Rejected = ?
--etc etc etc
WHERE
Package_Name = ?
AND Task_Name = 'Deals_Fact_Stg'
AND Status = 'Running'
Thanks in advance.
Chow.
View 11 Replies
View Related
Sep 18, 2006
This book 'MS SQL Server 2000 Bible' says the following:
'The foreign key can reference primary keys, unique constraints, or unique indexes of any table'
I've never heard of this before, I thought that the FK always established referential integrity by referencing the PK in the parent table. Does anyone have a further explanation of this?
thx,
Kat
View 5 Replies
View Related
Feb 7, 2008
Is there a way to reference textboxes in an SSRS table like one would for cells in Excel? For example, something like =textbox1/textbox2? I am trying to replace a spreadsheet by turning it into an automated report and need to do some horizontal formulas and when I put in the grouped total, it always defaults to averaging percetages from the rows above, which isnt a true summary in my case.
View 11 Replies
View Related
May 25, 2005
I have a table that holds a ParentID and the RecordID. There is a column called IsEnabled which is a bit field indicating if a folder can be displayed or not. 0 = NO, 1 = YESThe table is for a directory structure which is virtual and displays folders on a web page.Root----- 1---------- 1-1---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4---------- 1- 3---------- 1- 4I need a query that will not select any children that are under a Parent that is disabled. So if ' 1- 2 ' is disabled then:---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4SHOULD NOT SHOW.Can anyone give me a query that will overcome this problem i have.-J
View 4 Replies
View Related
Jul 24, 2002
Anyone know a way to pull specific fields from an excel spreadsheet.
I'm using SQL7 DTS to pull data into a SQL table. I can pull the entire
sheet but I just want certain fields.
thanks, archie
View 2 Replies
View Related
Jul 18, 2000
I would like a stored proc to be fed the table name as a paramater.
I tried below
create procedure testit as
set nocount on
declare @tblnm varchar(50)
select * from @tblnm
but it get Server: Msg 170, Level 15, State 1, Procedure testit, Line 5
Line 5: Incorrect syntax near '@tblnm'.
Any idea
View 1 Replies
View Related
Oct 17, 2005
Hello,
I have an access database and an SQL database and using data transformation services, i want to update the access database using the SQL data.
Can anyone tell me the syntax for referencing the access database?
Is it something like: [TABLENAME].dbo.FIELDNAME ?
Just to clarify, i have
Microsoft Access Database
Table 1 (UnitHistory)
SQL Database
Table 1 (UnitHistory)
How do i reference these seperately? I want to update the microsoft access database based on the SQL database data.
Eventually i'm trying to update an access database using the data held on my SQL server. Is DTS the best way for me to acomplish this or should i use another method?
Thanks guys
View 1 Replies
View Related
May 8, 2008
Hi there
Let say i have single dataset with different view (tables/list/matrix etc) with filter on it. Is it possible referencing from one view (table/list/matrix) to another? Basically how to access the other field for instance SUM from different view and used for the other view instead.
Thank you
View 1 Replies
View Related
May 16, 2008
I have a UniqueIdentifier as a self referencing foreign key. The pk gets set by default using the newid(). I need the foreign key to default to that same value. how can i do that? @@identity doesnt work, $rowguid doesnt work, column name doesnt work. Any ideas?
View 9 Replies
View Related
Jun 17, 2008
Hi there,
I'm trying to calculate a sort of rating:
select Player,
sum(case when Event = 'S' then 1.0 else 0.0 end) as Success,
sum(case when Event in ('S', 'F') then 1.0 else 0.0 end) as Attempts,
case when Attempts > 0 then Success / Attempts else NULL end as Rating
from EventList
group by Player
It says comlumn names are invalid. Any idea?
Thanks,
Bjoern
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
View 4 Replies
View Related
Feb 27, 2008
Excuse me if my terminlogy is inaccurate, i'm a .NETer that's new to SQL.
I was wondering if it's possible to reference a column in the WHERE CLAUSE that has been customily defined in the SELECT statement
for example
select employeeID, case when JobCode = 'A' then 'Accountant'
case when JobCode = 'C' then 'Consultant'
case when JobCode = 'B' then 'Biller'
End as JobType
from Employee
where JobType is not null
This does not work, and saids JobType is an invalid column name. Basically, what I want is to display the jobtype of the employee but i also want to only display the employees that are Accountants, consultants and billers.
Is this possible and what would be the best way of doing this?
I do not seem to be about to reference JobType in the WHERE statement.
View 10 Replies
View Related
Oct 23, 2007
Is there a way to refer to "current report item" using something like "this" or "me"? For example: I find myself often cutting and copying code to change the format or color of text boxes based on their value thoughout a report, and it seems like it'd be more efficient to write the formatting code once and then call it from each text box.
View 1 Replies
View Related
Mar 2, 2007
help how to reference n rename table..
is it possible by code?
View 7 Replies
View Related
Feb 27, 2008
Excuse me if my terminlogy is inaccurate, i'm a .NETer that's new to SQL.
I was wondering if it's possible to reference a column in the WHERE CLAUSE that has been customily defined in the SELECT statement
for example
select employeeID, case when JobCode = 'A' then 'Accountant'
case when JobCode = 'C' then 'Consultant'
case when JobCode = 'B' then 'Biller'
End as JobType
from Employee
where JobType is not null
This does not work, and saids JobType is an invalid column name. Basically, what I want is to display the jobtype of the employee but i also want to only display the employees that are Accountants, consultants and billers.
Is this possible and what would be the best way of doing this?
I do not seem to be about to reference JobType in the WHERE statement.
View 1 Replies
View Related
