I am joining a table that has two record id fields (record1, record2) to a view twice--once on each record--and selecting the top 1000. The view consists of several rather large tables, and it's id field is a string concatenation of their respective Ids (this was necessary for some third party software that requires a unique ID for the ...
I am trying to call an IF statement on my trigger so it won't archive expired files. (I only want to keep files that have been deleted but have not been expired) My error is The multi-part identifier "d.ExpiryDate" could not be bound. My Code: ALTER TRIGGER [dbo].[ArchiveDB] ON [dbo].[TBL_Content] AFTER DELETE AS BEGIN...
Simply put, I have a select that will return multiple single characters, and thus won't work. Is there any way to bunch all the single characters into a single returnable string? My current slow and ugly solution: ,'('+(Select Left(max(AE_D1),1) FROM ACCESS_EVENTS WHERE LEFT(AE_D1,1) like 'W' AND replace(HR.first...
I have a GridView with an Update button. I want to update a field in the database but I think the '@' in the code below is causing the problem in my ASP .NET page. What can be done within the grid or in the update (UpdateCommand) statement? Note, I get an Ora 00936 error. <asp:SqlDataSource ID="dsBooks" runat="server" Provid...
I can't seem to figure out how I can select only distinct entries in Llblgen 2.6 self-service model I essentially want this query. select distinct City from peopleTable where *predicates* I've got my PeopleCollection and I'm not sure if there's a distinct method I can call or argument I can pass to GetMulti(). ...
I've been given the task of creating a .net interface for a table that will be retrieved a database. One of the obvious things that the interface must do is retrieve data from the table. So.... I could write a function that simply does Select * from MyTable; and then returns the result. However, the table has ten columns and someone u...
I'm trying to write a custom SQL query that will create a list of the most recent Posts AND comments and I'm having trouble visualizing how i can do this. I can pull the latest comments by date DESC and i can pull the latest posts by date DESC but how do I make a feed / query to show them both? Here is my comment SQL SELECT comment_i...
Looking for the following forms functionality with INFORMIX 4GL?, ISQL with ESQL/C?, PROGRESS-4GL?, ORACLE? etc? I have a CRUD form which displays a customer[master] with all of their asociated transactions in record arrays[detail] on the same screen: [id ] [fullname ] [address1 ] [address2 ...
I am looking for a book or other undeniably authoritative source that gives reasons when you should choose a database vs. when you should choose other storage methods, most notably, a hardcoded table of values in a program. The rest of this message is just supporting info. Below are some unofficial lists of reasons to use one or the ...
Having the classic "posts table, and comments table with foreign key to posts table" scenario, what's the most efficient way to get the IDs of the last 20 posts ordered by the time of their last comment, and the actual comment itself? Here is a query that works but can probably be done much more efficiently: SELECT * FROM ( SELECT *...
In a situation where I have two tables: Games(id, player1Id, player2Id) Players(id, gender) And I would like to select all of the games and the player's gender, how would I get around null playerIds? From this explanation, I would think the answer would be: SELECT Games.id, p1.gender, p2.gender FROM Games JOIN Players p1 on player1...
I have the following SQL query: select AuditStatusId from dbo.ABC_AuditStatus where coalesce(AuditFrequency, 0) <> 0 I'm struggling a bit to understand it. It looks pretty simple, and I know what the coalesce operator does (more or less), but dont' seem to get the MEANING. Without knowing anymore information except the query above,...
How do I round the result of matchpercent to two decimal places (%)? I'm using the following to return some results: DECLARE @topRank int set @topRank=(SELECT MAX(RANK) FROM FREETEXTTABLE(titles, notes, 'recipe cuisine', 1)) SELECT ftt.RANK, (CAST(ftt.RANK as DECIMAL)/@topRank) as matchpercent, --Round this titles.title_id...
I have inherited a piece of SQL that I am working on for a reporting system. The system is centered around Purchase Orders, and when they are Created, Transmitted, Acknowledged, and Confirmed. These states are a progressive state. The piece of SQL that I am reviewing is shown below and is from the WHERE clause: OR (CreateJob.endtime is ...
I just want some advice about mysql database. I'm creating a new database for hired cars that need servicing and repairs. I have created 4 tables. Car_id is linked to a primary key table with car information. I was thinking of putting the repair_id and service_id with in the car data table. But that means it will have 3 primary keys in ...
here is my query: SELECT IIf([Lab Occurrence Form].[2 0 Analytical (Testing Phase)] Like ‘*2.5*Other*’,’2.5 Other’,[Lab Occurrence Form].[2 0 Analytical (Testing Phase)]) AS [Occurrence Code], Count([Lab Occurrence Form].[2 0 Analytical (Testing Phase)]) AS [Count] FROM [Lab Occurrence Form] WHERE ((([Lab Occurrence Form].[Occurrence Da...
In SQL, when I create a database index, I'm required to give the index a name, as in CREATE INDEX timestamp_index ON hit (timestamp); The only time that this name seems to be necessary is if I want to remove the index with DROP INDEX timestamp_index; Is there any other use of the index name? ...
Hi Say I have this great query in my stored procedure. Select * from Temp How would I store the results of this in the same stored procedure since in the next line I want to go through it in a loop(I don't know how to do this yet either) and do stuff to it. I found something like this DECLARE total_count INT DEFAULT 0 SET total_...
Looking for suggestions on cleanest way to get the return value and the result set (without passing a referenced parameter to the stored proc). MY stored procs have return values to show errors, etc and they end with a select statement to get the information. With regular commands I would create an output parameter which would hold th...
If some one does right click on a given Table in a Database using SQL Server management Studio from Microsoft and create script table to query window, it displays the create table code in the window. I notice something like the following CREATE TABLE [dbo].[Login]( [UserId] [int] NOT NULL, [LoginName] nvarchar COLLATE SQL_Latin...