I have the following data set - there is more to it than whats below, I just made it easier to read and highlight my problem!
SELECT LEFT(actv.ProjID, 4) AS proj, actv.Activity, actv.TotalExpensesLB, actv.PADM
FROM dbo.xtbl_MERActv actv
WHERE LEFT(actv.projID,4) = @project OR actv.PADM = @PADM
What I want to do is have the user enter a 4 digit number (@project) which will correspond to LEFT(actv.ProjID, 4). The way it is now, if the user enters a 4 digit number, no records are returned. If the user enters a 6 digit number ( the real length of the projID), then it runs correctly and I get the records I want.
I have tried to use the alias 'proj' in the where statement, but I get an error message that it is an invalid column name.
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".
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
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?
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?
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
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
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
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
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.'
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 _____.
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?
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.
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'
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?
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.
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
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.
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?
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.
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?
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.
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.
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.
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.
Is there a way to reference a value from a textbox in a matrix? In other words I want to pull the value in the textbox that is the column header into a cell in the matrix under certain conditions.
Let say i have single dataset with many view (tables/list/matrix etc) and utilise "filter" to manipulate each view.
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. People mentioned about SCOPE but I don't think this is relevant to this situation ?!?!
