TRIGGER: Help With 2 IFTHEN Statements Driving Multiple Inserts Into B_items Table...
Jul 30, 2007
Assuming I should be using values from temp inserted to insure correct record...
Need help coding IF...THEN INSERT statements in following After TRIGGER:
Create TRIGGER trg_insertItemRows
ON dbo.a_form
AFTER INSERT
AS
SET NOCOUNT ON
-- Checkbox Driven:
IF a_form.missingCheckbox = -1 THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ 'User checked Missing Data')
-- Textbox Driven:
IF a_form.incorrectTxtbox <> 'na' THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ Correction: Replace '+ incorrectTxtbox + ' with '+replaceWithTxtbox)
Sample code below:
-- Source table the Trigger acts on
Create Table a_form (
form_ID int Not Null,
parent_ID int,
missingCheckbox bit,
missingNote varchar(100),
incorrectTxtbox varchar(50),
replaceWithTxtbox varchar(50)
)
--Target table Trigger inserts into
Create Table b_items (
items_ID int Not Null,
form_ID int Not Null,
parent_ID int,
ItemTitle varchar(150)
)
View 5 Replies
ADVERTISEMENT
Feb 10, 2014
I have a requirement that if in a table update happened based on 1st condition then it should insert in one way and if update happened on second condition the insert statement will differ. That is it should insert the deleted records i.e., previous records existed in a table.The syntax is like
CREATE TRIGGER [dbo].[tr_a] on [dbo].[A]
AFTER UPDATE
AS
BEGIN TRY
IF NOT EXISTS
[code]....
I m getting some syntactical errors.
View 6 Replies
View Related
Oct 13, 2006
I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
View 2 Replies
View Related
Apr 10, 2015
I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABC](
[id] [uniqueidentifier] NOT NULL,
[Code] ....
When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABCD](
[id] [uniqueidentifier] NOT NULL,
[Code] .....
The trigger I've currently written looks like this:
/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]
[Code] ...
This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?
I can't sort by uniqueidentifier in descending as those can be random.
View 9 Replies
View Related
Oct 12, 2015
I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.
If object_ID(N'Bezeichnungen') is not NULL
Drop table Bezeichnungen;
GO
create table Bezeichnungen
(
Artikelnummer nvarchar(18),
Artikelbezeichnung nvarchar(80),
Artikelgruppe nvarchar(13),
[code]...
View 19 Replies
View Related
Oct 30, 2007
Hi...
I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc...
ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END
SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Regards
Karen
View 5 Replies
View Related
Apr 29, 2008
Hi,
OK, trying to return the results from two SQL statements into a DataSet using SqlDataAdapter. The SELECT statements query the same table but are looking for different records based on the date that the records were inserted - the 1st query looks for records fro the current month and the 2nd one looks at the same records but for the previous month. The goal is to be able to do some math within a repeater and get the difference between the two records.
Sounds easy enough and it has worked for me in different variations of the same idea but not this time - here's the code:
Protected Sub buildPartsReport(ByVal varHC) objConn.Open() sSQL = "SELECT ap.id,item_model,item_sn,aircraft_id,item_loc=item_type+ ' on ' +(SELECT tnum FROM T_Aircraft WHERE id=aircraft_id),apt.tot_time As endTimes,apt.tot_cycles as endCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & rD & "' AND item_type LIKE 'engine%';SELECT ap.id,apt.part_id,apt.tot_time as startTimes,apt.tot_cycles as startCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & oldRD & "' AND item_type LIKE 'engine%'" Dim objCommand As New SqlDataAdapter(sSQL, objConn) DS = New DataSet() objCommand.Fill(DS) Repeater1.DataSource = DS Repeater1.DataBind() DS.Dispose() objCommand.Dispose() objConn.Close() End Sub
Sorry if it wrapped a bit. The "rD" and "oldRD" are the variables for the date ranges (currently set to static numbers for testing). I'm getting the following error when I run this on an ASP.Net page:
System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'startTimes'.
The code works fine when run via the Query Tool on the SQL server (SQL 2005 Std) though it produces two distinct "tables" which I'm guessing is the problem. I've tried variations on the code including creating a 2nd dataset and then attempting a merge (no joy) and I've tried the ".TableName" route but it complains about trying to add the tablename twice.
Thoughts? I need to get this to work - it is part of a reporting component for an application that I'm developing and I'm stuck. Thanks as always...
View 5 Replies
View Related
May 13, 2015
I created a CTE which finds a subset of records from a table
I then ran a SELECT statement against the same table as
SELECT * FROM TABLE
EXCEPT (SELECT * FROM CTE)
Is it possible to add another EXCEPT statement after the CTE EXCEPT statement to cover a condition not incorporated in the CTE definition?
View 9 Replies
View Related
Apr 21, 2004
Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.
UPDATE
table1
SET
col1 = 0
WHERE col2 between 10 and 20
Error I am getting is :
Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
What is the best possible way to make it work? Thank you.
View 7 Replies
View Related
Dec 28, 2007
I was able to catch one update but not multiple updates or batch updates done to the table. I know the updated records are residing in inserted and deleted tables. Without using cursors, how can i read and compare all the rows in these two tables?
Following is the table structure:
Customer_Master(custmastercode, customer_company_name,updated_by)
Following is the trigger:
ALTER TRIGGER [TR_UPDATE_CUST]
ON [dbo].[CUSTOMER_MASTER]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT * FROM inserted)
BEGIN
declare @custcode int
Declare @message varchar(5000)
Declare @custommessage varchar(2000)
Declare @CUSTMASTERCODE int
Declare @CUSTOMER_COMPANY_NAME varchar(50)
Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '
select @custcode = [CUSTMASTERCODE],@UPDATED_BY = [UPDATED_BY] from inserted
Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '
IF(update([CUSTOMER_COMPANY_NAME]))
Begin
select @UCUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from deleted
select @CUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from inserted
Set @custommessage = 'Customer company name changed from ' + @UCUSTOMER_COMPANY_NAME + ' to ' + @CUSTOMER_COMPANY_NAME + '.'
Set @message = @message + @custommessage
End
Set @message = @message + ' Updated by ' + @UPDATED_BY + ' at ' + CAST(getdate() AS VARCHAR(20))+ '.'
INSERT INTO [CHANGE_HISTORY]
([CUSTMASTERCODE]
,[CHANGE_DETAILS])
VALUES (@custcode, @message)
END
END
View 7 Replies
View Related
Jul 14, 2005
Hi, I'm trying to create a form where new names can be added to a database. The webform looks like this:<body MS_POSITIONING="GridLayout"> <form id="Form1" method="post" runat="server"> Name:<asp:TextBox ID="newName" runat="server" /> <INPUT id="NewUserBtn" type="button" value="Create New User" name="NewUserBtn" runat="server" onServerClick="NewBtn_Click"> </form>And the code behind looks like this:Public Sub NewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewUserBtn.ServerClick Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter
MyConnection = New SqlConnection("server=databaseserver;database=db;uid=uid;pwd=pwd") MyCommand = New SqlDataAdapter("insert into certifications (name) values ('" & newName.Text & "'); select * from certifications", MyConnection)
DS = New DataSet MyCommand.Fill(DS, "Titles")
Response.Redirect("WebForm1.aspx", True) End SubWhen I try to insert one name it works. When I try to insert a second name, it overwrites the old one. Why is that?Thanks.James
View 3 Replies
View Related
Sep 10, 2007
I'm try to a multiple insert from one database to another by using this code:insert into [mpis].[dbo].[Residents] (acno,surname,name,ID,type) (select top 30 acno,surname,name,id,type from [PretoriaDB].[dbo].[WorkingDB]) but I keep on getting this error:Msg 8152, Level 16, State 9, Line 1String or binary data would be truncated.The statement has been terminated. Can any one help!!
View 7 Replies
View Related
Jun 6, 2004
Hi All,
Iam in a situtation where i have a query Which Inserts into a table from Select statement. But there is another table which is dependent on the Primay key of the inserted table.
Since the insert is multiple iam not able to use the @@Identity.
Can some one suggest me How can i over come this situtation.
Also Triggers cant be used as the the records are of huge numbers.
Eg:-
INSERT INTO Users (FirstName, SecondName) SELECT FirstName, SecondName From Old_Users
INSERT INTO UserDependent(UserID,OtherFields)
VALUES(@@Identity,'SomeOtherValue')
Thanks
Tanveer
View 3 Replies
View Related
Apr 11, 2007
hi I was just writing for some general advice. If I am to do many updates on a table is it okay to keep updating with many insert statements inside of a loop closing the connection each time? Here is some pseudo code to give you an idea:
cnn.open();
cmd = "insert into Cats values (red,4994,homer)";
for(int i=0; i<10000; i++)
{
cmd.executenonquery();
cnn.close();
}
View 2 Replies
View Related
Aug 21, 2006
I need to insert multiple rows for input rows that meet certain conditions.
My input data is as follows.
ZIPCode, Plus 4, Range, City, State
What I need to happen is that if there is a value in the range column that is > 0 then I need to insert a row for every range item.
For instance say I have the following data.
54952, 1001, 10, Menasha, WI
What I need imported is :
54952, 1001, Menasha, WI
54952, 1002, Menasha, WI
54952, 1003, Menasha, WI
54952, 1004, Menasha, WI
54952, 1005, Menasha, WI
54952, 1006, Menasha, WI
54952, 1007, Menasha, WI
54952, 1008, Mensaha, WI
54952, 1009, Mensaha, WI
54952, 1010, Mensaha, WI
54952, 1011, Mensaha, WI
Any help in pointing me in the right direction would be great. Thanks for your help in advance.
View 2 Replies
View Related
Feb 24, 2008
Hello all,
I'm working on a project for fun with some friends and have run into an issue with stored procedures. I've dealt with SQL quite a bit at my current job, but always from the perspective of somebody querying the database. The database was always managed by someone else and I never had to worry about the underlying code. Now, with my own project at home, I'm trying to deal with a situation and would like to use one, but I'm not sure if it is the best option and if so, exactly how to go about it.
Imagine a site that tracks movies. I have 3 tables:
Movies ( MovieID, Title, DirectorID, ActorID )
Actors ( ActorID, Name )
Director (DirectorID, Name)
This is an overly simple example, but it gets to the heart of my problem.
Okay, now what I'm wanting to do is to be able to write a procedure that would let me create my entries from just one call -- for instance
create_movie( 'Super Movie', 'directorJoe', 'actorJohn' )
that would do the following things:
-Look and see if the given director and actor already exist (from previous films)
-If they do, grab their ID values and use those in the new movie entry
-If they do not, create new entries and get THOSE ID values to use in the new movie entry
Can this be done in a stored procedure (I'm pretty sure it can be) and what sort of commands should I look into -- I'm not looking for a complete solution, cause I want to learn, but I am having trouble finding examples that fit my scenario.
Thanks.
View 3 Replies
View Related
May 28, 2008
Hello,
I have a stored procedure that updates my table with values entered in a datatable in my windows app.
An error occurs 1/2 way through the update process. I assumed that by implementing the rollback transaction command that the inserted lines would not be saved to my db. This is not the case.
I will elaborate a little more on what I need. From my windows app I have a datatable with 100 new rows that need to be written to my db. This I can do. However, a problem crops up should there be an error during the transfer. Let's say I have 100 rows in my datatable in my windows app, and row 57 causes an error, what is happening is that rows 1-56 are committed and 57-100 are not. What I need is for 1-100 to NOT be committed to my DB should there be a snag along the way. I need a code sample as I'm having way too much difficulty with this as is.
The basic code that copies to my db(sans rollback):
BEGIN
SET NOCOUNT ON
INSERT INTO userprofile (uid, uname, ustatus)
VALUES @userid, @username, @userstatus;
END
Thank You.
View 3 Replies
View Related
Apr 27, 2005
I am trying to insert a message into my database for every "league" I have in the database. I would like to do this in one sproc but am not sure how this would be done. Here is the loop if it has to be done outside of sql
Code:
message = "This is a test message."
leagues[] = getAllLeaguesInSite()
begin transaction
for each league in leagues
insertLeagueMessage(league, message)
if noErrors
commit transaction
else
rollback transaction
-----
as you can see it would be nice to be able to do this all in mssql . If it can be done please let me know.
Thanks
View 4 Replies
View Related
Jun 18, 2014
I have two tables called ECASE and PROJECT
In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .
When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.
When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.
I need to prevent the deadlock .
View 3 Replies
View Related
Dec 7, 2007
If a table has a trigger on it, and I am profiling.. on StmtCompleted... no filters... all teh stored proc code comes up, but, is there any way at all to see the same for trigger statements? I want to trace thru the proc and thru all trigger code also. Any ideas on work-around to trace trigger code, if Profiler can't do it? Thanks, Bruce
View 4 Replies
View Related
Apr 30, 2008
Hi all,
I am writing a portion of an app that is of intensely high online eCommerce usage. I have a question about identity columns and locking or not.
What I am doing is, I have two tables (normalized), one is OrderDemographics(firstname,lastname,ccum,etc) the other is OrderItems. I have the primary key of OrderDemographics as a column called 'ID' (an Identity Integer that is incrementing). In the OrderItems table, the 'OrderID' column is a foreign key to the OrderDemographics Primary Key column 'ID'.
What I have previously done is to insert the demographics into OrderDemographics, then do a 'select top 1 ID from OrderDemographics order by ID DESC' to get that last ID, since you can't tell what it is until you add another row....
The problem is, there's up to 20,000 users/sessions at once and there is a possiblity that in the fraction of a second it takes to select back that ID integer and use it for the initial OrderItems row, some other user might have clicked 'order' a fraction of a second after the first user and created another row in OrderDemographics, thus incrementing the ID column and throwing all the items that Customer #1 orders into Customer #2's order....
How do I lock a SQL table or lock the Application in .NET to handle this problem and keep it from occurring?
Thanks, appreciate it.
View 2 Replies
View Related
Sep 11, 2015
What are the options for a user to trigger an ssis package or job, it needs to be user friendly or in excel can I have a custom component to do update statements or trigger job?
View 6 Replies
View Related
Aug 7, 2007
Hi,I would have used the aspnet membership tool to auto-create all the ASP.NET membership tables. However, the hosting company don't allow remote connections which meant I had to create the tables by hand, scripting the tables using script to CREATE using management studio.However, I noticed one of the tables has data without any users: aspnet_SchemaVersions, which causes an error when trying to log onto my site.The fix is to make sure the table has the 4-5 rows of data in it (which is missing off the live server). Its just a few rows of data, but I want to script the inserts for each row so I don't have to type them in using myLittleAdmin (the host's web version of management studio). Can anyone point me in the right direction?
View 3 Replies
View Related
Oct 11, 2015
I want the below sql code to force a error if it falls on a saturday or sunday, and there are no rows in the OrderTrans table for the previous day (TransDate) but not sure how to do multiple if statements.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderTrans](
[OrderId] [int] NOT NULL,
[Code] ....
View 0 Replies
View Related
Oct 31, 2006
I am writing a fairly complex data automation procedure that basically uses about 5 stored procedures and some additional sql from C# to populate a database. i would like to utilize transactions in C# and sql to help me if something goes wrong. My question is, do i have to maintain the same connection in order to have one transaction for all my Commands. right now i do things like connection.Open();Command.ExecuteNonQuery(); connection.Close(); connection.Open();Command2.ExecuteNonQuery(); connection.Close(); i think i have to keep the same connection open for the duration of all my commands in order to utilize transactions, is this correct? thanks, mcm
View 5 Replies
View Related
Sep 17, 2007
Is there a way to execute multiple SQL statements within SQL Server? I'm triyng to write a sql statement that will delete from three tables. I need at least two sql statements to delete from these three tables. Can this be done?
View 12 Replies
View Related
Apr 23, 2008
how can take codes below and put them into one store procedure to supplie a gridview. also i will like to define the row name on the left like i did to the column on the top using the 'AS'
Code1....
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE1' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE1')
Code2.....
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE2' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE2')
thanks in advance
View 10 Replies
View Related
Oct 3, 2005
I am just wondering if it is possible using SQL Server 2000 to have multiple SQL Statements executed with one sqlComm.ExecuteNonQuery(); call?
View 1 Replies
View Related
Dec 22, 2005
Hi guys and gals,
I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says:
'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'
My Code is below:
//Start of sql
CREATE PROCEDURE ADMIN_GetSingleUsers( @userID int) AS
DECLARE @userSQL intSET @userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE, Cast(Users.Active as varchar(50)) as Active, Cast(Users.Approved as varchar(50)) as Approved, Users.Unit_ID As usersUnitID, * From TITLE, Users WHERE User_ID = @userID AND TITLE.TITLE_ID = Users.Title_ID )
Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @userSQL
//End of sql
Can you point to what I am doing wrong? Thanks in advance!
View 4 Replies
View Related
Mar 14, 2008
This query brings back 2 rows for each record, one for each case statement. How can I change this to bring in the contsupp.notes for both types of data? Or could it be fixed in the joins?
select
contact1.accountno,
contact1.key5 as "Ch#",
contact2.uexpsort as "Sort",
contact1.company as "Church",
contact1.contact as "Editor",
contact1.phone1 as "Phone",
contact2.uemerg as "Emergency",
contact1.Address1 as "Address",
contact1.city as "City",
contact1.state as "State",
contact1.zip as "Zip",
contact2.ubulletnqt as "Qty",
contact2.ubullcolor as "BullColor",
contact2.ubarcode as "Barcode",
contact2.udelivery as "Delivery",
contact2.uoutputdev as "OutputDev",
contact2.utimedeliv as "Time",
contact2.ucolor as "DelivColor",
contact2.ucollated as "Collated",
contact2.ustapled as "Stapled",
case when contsupp.contsupref = 'Delivery Notes' then contsupp.notes end as "Deliverynotes",
case when contsupp.contsupref = 'Cover Change Slip' then contsupp.notes end as "CoverChangeSlip"
from
Contact1 inner join contact2 on Contact1.Accountno = Contact2.Accountno
inner join contsupp on Contact1.Accountno=Contsupp.Accountno
where
contact1.key5 not like '' and contsupp.contsupref='Delivery Notes' or contsupp.contsupref ='Cover Change Slip' order by contact1.key5
Thanks for your help
View 2 Replies
View Related
Sep 19, 2006
HI Gurus
I have written one CTE (common table expression) and trying to use same CTE with three seperate UPDATE statements which gives me error saying "Invalid Object name" (it works fine when I try to use with 1 update statement (any one from three update statements)
Isnt it possible that I can use 1 CTE with mutiple update statements?
waiting for your reply....
View 4 Replies
View Related
Aug 28, 2007
Does anyone know how to execute more than one MDX statement in a single report? For example, If you wanted to create a session set, run some mdx with the set included, then drop the session set. In Management studio the mdx works perfectly if you separate the individual mdx statements with GO. I tried using semicolons as mdx statement terminators and this didn't work either.
Reporting Services does something between when the report begins it's execution and the time the MDX is submitted to AS. And running a trace to see exactly what is passed in doesn't work either - the only message displayed is the same error message as what is passed back to RS. (I ran a trace with all events selected).
Anyone have any ideas?
Thank you in advance,
John
View 3 Replies
View Related
Sep 5, 2007
I'm hoping someone out there has a creative solution for my scenario. I have the report code below. I want to create a parameter that allows the user to select which where statement to use in the report. Then I want to use the corresponding where description below ( ex: @WHEREDESC1 ) in my header which will describe (in laymen's terms) which parameter was chosen and what is being reported on.
Has anyone achieved this or have an idea how it may be done?
DECLARE @WHEREDESC1 VARCHAR(50), @WHEREDESC2 VARCHAR(50), @WHEREDESC3 VARCHAR(50),
@WHEREDESC4 VARCHAR(50), @WHEREDESC5 VARCHAR(50)
SET @WHEREDESC1 = 'SRM Admitting and Principal Diagnosis do not match'
SET @WHEREDESC2 = 'SRM and AMPFM Admit dates do not match'
SET @WHEREDESC3 = 'SRM and AMPFM Discharge dates do not match'
SET @WHEREDESC4 = 'SRM and AMPFM Visit Type Codes do not match'
SET @WHEREDESC5 = 'SRM and AMPFM DRG codes do not match'
SELECT LEFT(srm.EPISODES.ADMISSION_DATE, 11) AS SRM_Admit_DT, ampfm.rpt_AdtVisit.AdmitDate AS AMPFM_Admit_DT,
LEFT(srm.EPISODES.EPISODE_DATE, 11) AS SRM_Disch_DT, ampfm.rpt_AdtVisit.DischDate AS AMPFM_Disch_DT,
srm.EPISODES.EPISODE_TYPE AS SRM_Visit_Type, ampfm.rpt_AdtVisit.VisitTypeCode AS AMPFM_Visit_Type,
srm.EPISODES.CHARGES AS SRM_Charges, ampfm.rpt_VisitFinancial.TotCharges AS AMPFM_Charges,
ampfm.rpt_VisitFinancial.VisitChargesActive AS AMPFM_Active_Charges, ampfm.rpt_AdtVisit.PatientFullName,
srm.CDMAB_DRG_INFO.DRG_NUMBER AS SRM_Drg, ampfm.rpt_InsuranceDrg.InsDrg AS AMPFM_Drg,
ampfm.rpt_AdtVisit.Complaint AS AMPFM_Admit_Dx, srm.cdmab_base_info.ADM_DX_CODE, srm.cdmab_base_info.ADM_DX_DESC,
srm.cdmab_base_info.PRIN_DX_CODE, srm.cdmab_base_info.PRIN_DX_DESC, srm.EPISODES.ACCOUNT_NUMBER,
srm.EPISODES.MEDREC_NO
FROM srm.EPISODES INNER JOIN
ampfm.rpt_AdtVisit ON srm.EPISODES.ACCOUNT_NUMBER = ampfm.rpt_AdtVisit.AccountNumber INNER JOIN
ampfm.rpt_VisitFinancial ON ampfm.rpt_AdtVisit.IntOOS = ampfm.rpt_VisitFinancial.IntOOS INNER JOIN
ampfm.rpt_InsuranceDrg ON ampfm.rpt_AdtVisit.IntOOS = ampfm.rpt_InsuranceDrg.IntOOS AND
ampfm.rpt_AdtVisit.VisitTypeCode = ampfm.rpt_InsuranceDrg.VisitTypeCode INNER JOIN
srm.CDMAB_DRG_INFO ON srm.EPISODES.EPISODE_KEY = srm.CDMAB_DRG_INFO.EPISODE_KEY INNER JOIN
srm.cdmab_base_info ON srm.EPISODES.EPISODE_KEY = srm.cdmab_base_info.EPISODE_KEY
--WHERE srm.cdmab_base_info.ADM_DX_CODE <> srm.cdmab_base_info.PRIN_DX_CODE
--WHERE srm.EPISODES.ADMISSION_DATE <> ampfm.rpt_AdtVisit.AdmitDate
--WHERE srm.EPISODES.EPISODE_DATE <> ampfm.rpt_AdtVisit.DischDate
--WHERE srm.EPISODES.EPISODE_TYPE <> ampfm.rpt_AdtVisit.VisitTypeCode
--WHERE srm.CDMAB_DRG_INFO.DRG_NUMBER <> ampfm.rpt_InsuranceDrg.InsDrg
Order By srm.EPISODES.ACCOUNT_NUMBER
View 7 Replies
View Related