Hello, I am having some trouble dealing with an SQL table that needs to be queried and re-formatted into another table for reporting purposes. Here's the initial table: id int, logtimestamp datetime, serialnumber varchar(255), robotid int, amountconsumed float The robotid's are only from 1 to 4. Eve...
Lets say I have the following tables: Customers Products CustomerProducts Is there a way I can do a select from the Customers and Products tables, where the values are NOT in the map table? Basically I need a matched list of Customers and Products they do NOT own. Another twist: I need to pair one customer per product. So If 5 cus...
I am using sp_send_dbmail in SQL Server 2008 to send out the results of a query. I moved the query into a proc and am trying to use the proc in the sp_send_dbmail prcedure like so: EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile', @from_address = '[email protected]', @reply_to = '[email protected]', @recipients ...
Hi I want to create a simple stored proecudre which does the following: Psudocode @tempSelect = "SELECT * FROM Table" if (@Param is NULL) then exec @tempSelect else exec @tempSelect + ' WHERE id = ' + @Param + ' Is this method efficent? Thank you. ...
Is there a way to immeidately stop execution of a SQL script in SQL server, like a "break" or "exit" command? I have a script that does some validation and lookups before it starts doing inserts, and I want it to stop if any of the validations or lookups fail. ...
I have the following TSQL codes: -- 1. define a cursor DECLARE c_Temp CURSOR FOR SELECT name FROM employees; DECLARE @name varchar(100); -- 2. open it OPEN c_Temp; -- 3. first fetch FETCH NEXT FROM c_Temp INTO @name; WHILE @@FETCH_STATUS = 0 BEGIN print @name; FETCH NEXT FROM c_Temp INTO @name; -- fetch again in a loop END -- 4...
I've been using the crap out of the Nested Set Model lately. I have enjoyed designing queries for just about every useful operation and view. One thing I'm stuck on is how to select the immediate children (and only the children!) of a node. To be honest, I do know of a way - but it involves unmanageable amounts of SQL. I'm sure there is...
I'm getting the error: the multi-part identifier "IC.industry" could not be bound when making this SQL query from a JSP page via JDBC: select C.company, C.shname, C.fullname, count(d_to_c.designer) from companies C left join ind_to_c IC on C.company = IC.company left join d_to_c on C.company= d_to_c.company where IC.indust...
Is there some hard and fast rule about how big is too big for a SQL table? We are storing SCORM tracking data in a name/value pair format and there could be anywhere from 4-12 rows per user per course, down the road is this going to be a bad thing since there are hundreds of courses and thousands of users? ...
I have a table MyTable with a trigger defined like this: ALTER TRIGGER [MyTableInsertDeleteUpdate] ON [dbo].[MyTable] AFTER INSERT,DELETE,UPDATE AS DECLARE @id int; BEGIN SELECT @id = ins.id FROM inserted ins; IF (@id IS NOT NULL) BEGIN -- insert a new record to audit table PRINT 'inserted/updated id: ' + CAS...
Scenario: A sampling survey needs to be performed on membership of 20,000 individuals. Survey sample size is 3500 of the total 20000 members. All membership individuals are in table tblMember. Same survey was performed the previous year and members whom were surveyed are in tblSurvey08. Membership data can change over the year (e.g. n...
I have a list of keywords (strings) in which I need to identify any matches with a blacklist of keywords (strings in a separate table) Any keyword/blacklist matches will be flagged in a bit field: Keyword.IsBlacklisted. Is there an easy way to accomplish this in SQL? The matches might be partial (i.e. blacklist = 'sex' keyword = 'sex ...
Is there a good open-source Java library for parsing SQL statements? If possible, it should be customizable or flexible enough to also be able to parse (or at least ignore) vendor-specific syntax (such as Oracle tablespace definitions or MySQL's LIMIT clause). If not, strict adherence to the SQL standard is also fine. Update: I need t...
I'm hoping someone can help with a problem...I don't work in anything related to programming, but we needed some asset tracking pretty badly, so in my spare time (not very much, we average 10hrs days) and with the tools at hand (a 600mhz pile running winXP) i built a little classic ASP site to handle all the tracking and orders. Everyth...
I need to run a query like: SELECT p.id, p.name, (SELECT name FROM sites s WHERE s.id = p.site_id) AS site_list FROM publications p But I'd like the sub-select to return a comma separated list, instead of a column of data. Is this even possible, and if so, how? ...
I've been using this for some time: SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col)) However recently, I've found a problem with columns with all "0" characters like '00000000' because it never finds a non-"0" character to match. An alternative technique I've seen is to use TRIM: REPLACE(LTRIM(REPLACE(str_col, '0', ' ')...
I was thinking about some stuff lately and I was wondering what would be the RIGHT way to do something like the following scenario (I'm sure it is a quite common thing for DB guys to do something like it). Let's say you have a products table, something like this (MySQL): CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increme...
I have the following example table: Dt Value1 Value2 Value3 ... 2008-12-01 12:34:00 100.1 0.123 43 .... Is there any way by using TSQL to generate trend graphics as image such as jpg? Or do I need Reporting service to do it? I need to do it TSQL so that the daily trend images can be generated in a sche...
Background: iSeries version of DB2. In every environment, there is a table containing positional column information about other tables. As the data in this table is static and has to be re-generated every time a table is altered, problems can occur if it is out of step. All the positional data exists in QSYS2.SYSTABLES and QSYS2.SYSCOLU...
I'm not aware how deep my tree will be. So I think the NSM is fit for me, reading some docs. In sql, this model suppose I'm using an integer value as primary key. I thought to create a twin table only to store the ints (PK,left,righ) connected by a relation one-to-one with the real table. Things are complicating and it is a waste of spac...