Select Self Referencing Table
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
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
Jun 14, 2002
Hi,
I was wondering if it is possible to reference a value in a previous row as a field in a normal select statement, for example:
SELECT a.user_id, a.name, b.user_id, b.name
FROM Users a
LEFT OUTER JOIN Users b
WHERE (b.user_id = a.user_id - 1)
Unfortunantly my user_id values are not in sequence so I get null entries using that code. Instead of doing something as basic as -1 is there another way to reference the previous row?
View 4 Replies
View Related
Mar 3, 2008
I'm just wodnering if you have two select statements, one nested inside another, can you reference tables from the outer loop?
for example, say I would like to find all employees who have at least two clients and employees and clients have a one to many relationship.
select *
from Employee e
where
(
select count(*)
from Clients c
where c.EmployeeId = e.EmployeeId
) >= 2
This obviously doesn't work - but how would i go about doing something like this?
View 10 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
Sep 16, 2004
I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.
Here is a basic breakdown of what I am trying to do:
Table1 (the table where the rows need to be deleted from)
Column_x (Holds the id that is unique to the various rows of data - User ID)
Table2 (Holds the user information & has the associated ID)
Column_z (holds the User ID)
I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.
Code:
SELECT Column_z
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z <> Column_x
This did not seem to do what I needed, it did not delete any rows at all.
I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2
Then I tried another scenerio that I also needed to do:
Code:
SELECT Column_z, Column_a
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z = Column_x AND Column_a='0'
'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.
Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.
Any ideas on what I am doing wrong here?
View 3 Replies
View Related
Mar 19, 2015
IF OBJECT_ID('tTable') IS NOT NULL
DROP TABLE tTable
GO
CREATE TABLE tTable
(nRow_IdINTEGER IDENTITY NOT NULL PRIMARY KEY,
nParent_IdINTEGERNULL,
[Code] ....
gives:
nRow_Id nParent_Id cGroup cValue
----------- ----------- ------- ------
1 1 One A
2 1 One B
3 2 One C
I want to insert a copy of this data, but w/ group = 'TWO', so the table will contain the additional rows
4 4 Two A
5 4 Two B
6 5 Tow C
View 5 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
Feb 9, 2008
Hi
Im trying to create an update statement which references two tables (join) and has a CASE clause attached. Not sure where im going wrong...
Using T-sql!!!
update import set import.gone =
from import
inner join stat
ON stat.id = import.id
CASE
WHEN stat.A = import.field2 THEN import.gone = sec.A
WHEN stat.B = import.field2 THEN import.gone = sec.B
WHEN stat.C = import.field2 THEN import.gone = sec.C
WHEN stat.D = import.field2 THEN import.gone = sec.D
WHEN stat.E = import.field2 THEN import.gone = sec.E
WHEN stat.F = import.field2 THEN import.gone = sec.F
ELSE import.gone = null
END
Any help would be greatly appreciated
View 3 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
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
Jul 22, 2015
I have a very simple bit of code.
SELECT dbo.MF_PATIENT.Forename,
dbo.MF_PATIENT.Surname,
dbo.MF_PATIENT.DOB,
dbo.MF_PATIENT.Postcode,
Count(dbo.MF_PATIENT.HEYNo) AS CountOfHEYNo
[Code] ....
My issue is I want to run this bit of code but only if dbo_MF_PATIENT.MFPatientID appears in any of the 3 tables below:
dbo_ED_ATTENDANCE, dbo_OP_APPOINTMENT, dbo_IP_ADMISSION
Suppose im unsure on the joining because there is only ever one patient in the
dbo_MF_PATIENT table but they could appear dozens of times in any of the other 3 tables.
View 18 Replies
View Related
Feb 28, 2008
I have an Employee table that has
EmployeeID (PK)
SupervisorID (which is really just another EmployeeID)
..random junk...
Now that part makes sense, everyone gets one and only one boss.
Their boss can change, and therefore the SupervisorID would be updated.
Now I have an EmployeeEvals table that has quarterly evaluation data.
I want to relate these two tables.
Eval table has
EvalID (PK)
ReviewedEmployeeID (the one being evaluated)
SupervisorID (the one doing the evaluation)
Now I need to link this back to the employee table (at least I think I do).
So I would want to relate it by the ReviewedEmployeeID going back to EmployeeID in the employee table and I also want the SupervisorID to do the same...
But of course that won't work because that would seem to indicate that a single record on the Employees table (say EmployeeID 55) should have a matching (or could) record in the Eval table that would look like
EvalID: 12345
ReviewedEmployeeID: 55
SupervisorID: 55
which of course wouldn't happen as an employee wouldn't evaluate themself.
How do I handle the relationships for this properly?
Do I just not link the SupervisorID back to anything?
View 2 Replies
View Related
May 6, 2008
I've recently finished an application for a small company with perhaps two hundred employees. Each employee was set up in a Users table in the database, against which application logins were processed.
For just about every other table in the database, other than pure lookup tables, we created columns to indicate the user who created the entry, and the user who last modified the entry. This was done using FK references back to the Users table. Each table contains two references back to the Users table, and there are over 150 tables now that follow this scheme. At first I was not concerned, other than the fact that it makes a visual picture of the data model look very confusing (almost every table has a pair of links back to the Users table), until I encountered an issue where I could no longer delete from the Users table. Upon surpassing 253 FK references to Users, I can no longer delete users, as the Query Optimizer can't complete the query.
Now, all of that so far is really not a big deal. Deleting users was never my intent anyway. The only real question I have is whether this is the standard way of maintaining history for table records. Have others used this method? Is there a better way?
View 1 Replies
View Related
May 1, 2006
Setup:
Using SQL 2005
TableA
ID INT PK
Name VARCHAR
RefID FK
ID Name RefID
1 Test1 <NULL>
2 Test1a 1
3 Test1b 1
4 Test1b1 3
5 Test1b1a 4
CREATE TRIGGER Update_ID
ON TableA INSTEAD OF UPDATE
AS
IF UPDATE(ID)
BEGIN
DECLARE @old_ID INT
DECLARE @new_ID INT
SELECT @old_ID = ID FROM DELETED
SELECT @new_ID = ID FROM INSERTED
UPDATE TableA
SET ID = @new_ID
WHERE ID = @old_ID
UPDATE TableA
SET RefID = @new_ID
WHERE RefID = @old_ID
END
SPROC:
UPDATE TableA
SET ID=6
WHERE ID=2
RefID is the FK to ID
ID is also the PK to another table and has the relationship use cascading Delete and Update.
Problem:
"The UPDATE statement conflicted with the SAME TABLE REFERENCE constraint"
This is referring to the first UPDATE of the Trigger.
It was my understanding that Instead Of checks the constraints after it is all done yet it errs with the first Trigger UPDATE.
I was expecting to overcome the self-reference constraint issue by using Instead Of and with the first Trigger UPDATE, change the ID(PK) and then change the RefID(FK) with the second Trigger UPDATE.
Once that was done, it should have not had any constraint problems. Thoughts?
Thanks, Nathan
View 7 Replies
View Related
Jul 14, 2014
We are in the conversion process of making the database ints.This is a change from a guid PK to an integer based PK that uses an int Identity. The program still uses the guid, and we are trying to map that guid to the databases int.We insert using TVPs passed from code. Since the identity is being set upon insert I have three things to accomplish:
1) Insert all the data into the dbo table
2) Update the parent Id in the table
3) Pass the SetsId guid, Sets_Id int, ParentSets_Id int back to the program
This is a high transaction table that will have a lot of records (millions).
--Sample table creation. There is a FK between Sets_Id to ParentSets_Id, Clustered PK on the Sets_Id
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JSets]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JSets](
[SetsID] [uniqueidentifier] NOT NULL,
[Sets_Id] bigint Identity (1,1) NOT NULL,
[code]...
View 0 Replies
View Related
Feb 15, 2008
Hi everybody,
I know that it is possible for one column to reference two different parent tables by defining two foreign keys on a same column, I have done it and SQL Server does give any error. But I want to know that is it advisable to define multiple foreign key on a column referncing two differnt parent table's primary key in differnt case... ? Means If Case 1 then It should reference to column1 or table1 otherwise it should reference to table2? How is it practicle...?
View 2 Replies
View Related
May 21, 2014
An example of what I am talking about is the employee table in the Adventureworks database. This has employeeID and then ManagerID, ManagerID just being the EmployeeID of the person whom the original reports to.
I know the queries for querying this type of data and even making recursive common table expressions. What I cannot seem to find is how one goes about BUILDING said table. I see all sorts of examples where people are just doing INSERT table VALUES () manually to load the table. The problem is, I need to create a table that has potentially thousands of records.
It will essentially be a dimensional map. Don't even get me started as to they why, I will just suffice to say that is what the client and project want . I have a process that will do this now, but it is not very dynamic and very hard coded. To me, there seems like there should be some sort of standardized methodology for handling this.
View 9 Replies
View Related
Jul 23, 2005
Hi SQL GurusCould anyone please explain how the following stored procedure can beamended to retrieve Subordinates in alphabetical order ?The example below simply retrives records in the order in which theywere entered.It sounds very easy but I can't sort it out ?The following code was taken from Narayana Vyas Kondreddi's website(http://vyaskn.tripod.com/index.htm)Consider the employee table of an organization, that stores all theemployee records. Each employee is linked to his/her manager by amanger ID.CREATE TABLE dbo.Emp(EmpIDintPRIMARY KEY,EmpNamevarchar(30),MgrIDintFOREIGN KEY REFERENCES Emp(EmpID))GOCREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)GOINSERT dbo.Emp SELECT 1, 'President', NULLINSERT dbo.Emp SELECT 2, 'Vice President', 1INSERT dbo.Emp SELECT 3, 'CEO', 2INSERT dbo.Emp SELECT 4, 'CTO', 2INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6INSERT dbo.Emp SELECT 12, 'Tester 1', 11INSERT dbo.Emp SELECT 13, 'Tester 2', 11INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7INSERT dbo.Emp SELECT 18, 'Tester 3', 17INSERT dbo.Emp SELECT 19, 'Tester 4', 17INSERT dbo.Emp SELECT 20, 'Tester 5', 17GOCREATE PROC dbo.ShowHierarchy(@Root int)ASBEGINSET NOCOUNT ONDECLARE @EmpID int, @EmpName varchar(30)SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpNameSET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)WHILE @EmpID IS NOT NULLBEGINEXEC dbo.ShowHierarchy @EmpIDSET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root ANDEmpID > @EmpID)ENDENDGOEXEC dbo.ShowHierarchy 1GO---President------Vice President---------CEO---------CTO------------Group Project Manager---------------Project Manager 1------------------Team Leader 1---------------------Software Engineer 1---------------------Software Engineer 2------------------Test Lead 1---------------------Tester 1---------------------Tester 2---------------Project Manager 2------------------Team Leader 2---------------------Software Engineer 3---------------------Software Engineer 4------------------Test Lead 2---------------------Tester 3---------------------Tester 4---------------------Tester 5
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
Jul 30, 2007
I'm creating a new Integration Services Project that copies data out of a SQL 7 server, transforms it, and places the data on a SQL 2005 (SP 2) Server. When defining a lookup transformation, if I specify an OLE DB Connection to my server running SQL 7 as the reference table, as soon as I click on the Colums tab, Visual Studio closes / crashes and dumps me to windows. I don't get an error message. If however I specify a connection to a server running SQL 8, or SQL 2005, no problems.
Is this supposed to happen?
My workstation is running Windows XP Pro SP2, Visual Studio 2005 Pro.
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00
The server that doesn't work for a reference table is running Windows 2000 Server SP4
SQL 7.00.623
Thanks for your help,
Kirk
View 6 Replies
View Related
Nov 29, 2007
Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
Thanks
View 1 Replies
View Related
Jul 31, 2014
I need to write a select statement that take the upper table and select the lower table.
View 3 Replies
View Related
Oct 15, 2007
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
View 1 Replies
View Related
Oct 1, 2007
I have a query that has the following structure
Select *
From Table
Where Condition And ... (some 'Exists' conditions)
When I run the query using field names the query gets much slower, and I cannot understand Why!
Select MyField
From Table
Where Condition And ... (some 'Exists' conditions)
I'm talking about three times slower using the Select MyField sintax.
Any ideas???
View 8 Replies
View Related
Aug 28, 2015
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
View 8 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
Aug 16, 2007
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
View 3 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
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
