Search Results

Search found 27986 results on 1120 pages for 'select n plus 1'.

Page 24/1120 | < Previous Page | 20 21 22 23 24 25 26 27 28 29 30 31  | Next Page >

  • Want to avoid the particular rows from select join query... See description

    - by OM The Eternity
    I have a Select Left Join Query whis displays me the rows for the latest changedone(its a time) column name ("field" should not be equal) column name ("trackid" should not be equal), and column name "Operation should be "UPDATE" ", below is the query I am talking about... SELECT j1. * FROM jos_audittrail j1 LEFT OUTER JOIN jos_audittrail j2 ON ( j1.trackid != j2.trackid AND j1.field != j2.field AND j1.changedone < j2.changedone ) WHERE j1.operation = 'UPDATE' AND j2.id IS NULL Now here I don't want a row to be displayed with a two particular column's value i.e. "field's value" the value is "LastvisitDate" and "hits" Now if if append the condition in the above query that " AND j1.field != 'lastvistDate' AND j1.field != 'hits' " theni do not get any result... The table structure is jos_audittrail: id trackid operation oldvalue newvalue table_name live changedone(its a time) I hope i have given the details properly If u still find something missing I will try to provide it more better way... Pls help me to avoid those two rows with those to mentioned value of "field"

    Read the article

  • SQL Server script commands to check if object exists and drop it

    - by deadlydog
    Over the past couple years I’ve been keeping track of common SQL Server script commands that I use so I don’t have to constantly Google them.  Most of them are how to check if a SQL object exists before dropping it.  I thought others might find these useful to have them all in one place, so here you go: 1: --=============================== 2: -- Create a new table and add keys and constraints 3: --=============================== 4: IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo') 5: BEGIN 6: CREATE TABLE [dbo].[TableName] 7: ( 8: [ColumnName1] INT NOT NULL, -- To have a field auto-increment add IDENTITY(1,1) 9: [ColumnName2] INT NULL, 10: [ColumnName3] VARCHAR(30) NOT NULL DEFAULT('') 11: ) 12: 13: -- Add the table's primary key 14: ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY NONCLUSTERED 15: ( 16: [ColumnName1], 17: [ColumnName2] 18: ) 19: 20: -- Add a foreign key constraint 21: ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [FK_Name] FOREIGN KEY 22: ( 23: [ColumnName1], 24: [ColumnName2] 25: ) 26: REFERENCES [dbo].[Table2Name] 27: ( 28: [OtherColumnName1], 29: [OtherColumnName2] 30: ) 31: 32: -- Add indexes on columns that are often used for retrieval 33: CREATE INDEX IN_ColumnNames ON [dbo].[TableName] 34: ( 35: [ColumnName2], 36: [ColumnName3] 37: ) 38: 39: -- Add a check constraint 40: ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [CH_Name] CHECK (([ColumnName] >= 0.0000)) 41: END 42: 43: --=============================== 44: -- Add a new column to an existing table 45: --=============================== 46: IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='dbo' 47: AND TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName') 48: BEGIN 49: ALTER TABLE [dbo].[TableName] ADD [ColumnName] INT NOT NULL DEFAULT(0) 50: 51: -- Add a description extended property to the column to specify what its purpose is. 52: EXEC sys.sp_addextendedproperty @name=N'MS_Description', 53: @value = N'Add column comments here, describing what this column is for.' , 54: @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE', 55: @level1name = N'TableName', @level2type=N'COLUMN', 56: @level2name = N'ColumnName' 57: END 58: 59: --=============================== 60: -- Drop a table 61: --=============================== 62: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo') 63: BEGIN 64: DROP TABLE [dbo].[TableName] 65: END 66: 67: --=============================== 68: -- Drop a view 69: --=============================== 70: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewName' AND TABLE_SCHEMA='dbo') 71: BEGIN 72: DROP VIEW [dbo].[ViewName] 73: END 74: 75: --=============================== 76: -- Drop a column 77: --=============================== 78: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='dbo' 79: AND TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName') 80: BEGIN 81: 82: -- If the column has an extended property, drop it first. 83: IF EXISTS (SELECT * FROM sys.fn_listExtendedProperty(N'MS_Description', N'SCHEMA', N'dbo', N'Table', 84: N'TableName', N'COLUMN', N'ColumnName') 85: BEGIN 86: EXEC sys.sp_dropextendedproperty @name=N'MS_Description', 87: @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE', 88: @level1name = N'TableName', @level2type=N'COLUMN', 89: @level2name = N'ColumnName' 90: END 91: 92: ALTER TABLE [dbo].[TableName] DROP COLUMN [ColumnName] 93: END 94: 95: --=============================== 96: -- Drop Primary key constraint 97: --=============================== 98: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_SCHEMA='dbo' 99: AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'PK_Name') 100: BEGIN 101: ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [PK_Name] 102: END 103: 104: --=============================== 105: -- Drop Foreign key constraint 106: --=============================== 107: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_SCHEMA='dbo' 108: AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'FK_Name') 109: BEGIN 110: ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [FK_Name] 111: END 112: 113: --=============================== 114: -- Drop Unique key constraint 115: --=============================== 116: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo' 117: AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'UNI_Name') 118: BEGIN 119: ALTER TABLE [dbo].[TableNames] DROP CONSTRAINT [UNI_Name] 120: END 121: 122: --=============================== 123: -- Drop Check constraint 124: --=============================== 125: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='CHECK' AND TABLE_SCHEMA='dbo' 126: AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'CH_Name') 127: BEGIN 128: ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [CH_Name] 129: END 130: 131: --=============================== 132: -- Drop a column's Default value constraint 133: --=============================== 134: DECLARE @ConstraintName VARCHAR(100) 135: SET @ConstraintName = (SELECT TOP 1 s.name FROM sys.sysobjects s JOIN sys.syscolumns c ON s.parent_obj=c.id 136: WHERE s.xtype='d' AND c.cdefault=s.id 137: AND parent_obj = OBJECT_ID('TableName') AND c.name ='ColumnName') 138: 139: IF @ConstraintName IS NOT NULL 140: BEGIN 141: EXEC ('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT ' + @ConstraintName) 142: END 143: 144: --=============================== 145: -- Example of how to drop dynamically named Unique constraint 146: --=============================== 147: DECLARE @ConstraintName VARCHAR(100) 148: SET @ConstraintName = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 149: WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo' 150: AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME LIKE 'FirstPartOfConstraintName%') 151: 152: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo' 153: AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = @ConstraintName) 154: BEGIN 155: EXEC ('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT ' + @ConstraintName) 156: END 157: 158: --=============================== 159: -- Check for and drop a temp table 160: --=============================== 161: IF OBJECT_ID('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName 162: 163: --=============================== 164: -- Drop a stored procedure 165: --=============================== 166: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='dbo' AND 167: ROUTINE_NAME = 'StoredProcedureName') 168: BEGIN 169: DROP PROCEDURE [dbo].[StoredProcedureName] 170: END 171: 172: --=============================== 173: -- Drop a UDF 174: --=============================== 175: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION' AND ROUTINE_SCHEMA='dbo' AND 176: ROUTINE_NAME = 'UDFName') 177: BEGIN 178: DROP FUNCTION [dbo].[UDFName] 179: END 180: 181: --=============================== 182: -- Drop an Index 183: --=============================== 184: IF EXISTS (SELECT * FROM SYS.INDEXES WHERE name = 'IndexName') 185: BEGIN 186: DROP INDEX TableName.IndexName 187: END 188: 189: --=============================== 190: -- Drop a Schema 191: --=============================== 192: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'SchemaName') 193: BEGIN 194: EXEC('DROP SCHEMA SchemaName') 195: END And here’s the same code, just not in the little code view window so that you don’t have to scroll it.--=============================== -- Create a new table and add keys and constraints --=============================== IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo') BEGIN CREATE TABLE [dbo].[TableName]  ( [ColumnName1] INT NOT NULL, -- To have a field auto-increment add IDENTITY(1,1) [ColumnName2] INT NULL, [ColumnName3] VARCHAR(30) NOT NULL DEFAULT('') ) -- Add the table's primary key ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY NONCLUSTERED ( [ColumnName1],  [ColumnName2] ) -- Add a foreign key constraint ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [FK_Name] FOREIGN KEY ( [ColumnName1],  [ColumnName2] ) REFERENCES [dbo].[Table2Name]  ( [OtherColumnName1],  [OtherColumnName2] ) -- Add indexes on columns that are often used for retrieval CREATE INDEX IN_ColumnNames ON [dbo].[TableName] ( [ColumnName2], [ColumnName3] ) -- Add a check constraint ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [CH_Name] CHECK (([ColumnName] >= 0.0000)) END --=============================== -- Add a new column to an existing table --=============================== IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='dbo' AND TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName') BEGIN ALTER TABLE [dbo].[TableName] ADD [ColumnName] INT NOT NULL DEFAULT(0) -- Add a description extended property to the column to specify what its purpose is. EXEC sys.sp_addextendedproperty @name=N'MS_Description',  @value = N'Add column comments here, describing what this column is for.' ,  @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE', @level1name = N'TableName', @level2type=N'COLUMN', @level2name = N'ColumnName' END --=============================== -- Drop a table --=============================== IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo') BEGIN DROP TABLE [dbo].[TableName] END --=============================== -- Drop a view --=============================== IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewName' AND TABLE_SCHEMA='dbo') BEGIN DROP VIEW [dbo].[ViewName] END --=============================== -- Drop a column --=============================== IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='dbo' AND TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName') BEGIN -- If the column has an extended property, drop it first. IF EXISTS (SELECT * FROM sys.fn_listExtendedProperty(N'MS_Description', N'SCHEMA', N'dbo', N'Table', N'TableName', N'COLUMN', N'ColumnName') BEGIN EXEC sys.sp_dropextendedproperty @name=N'MS_Description',  @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE', @level1name = N'TableName', @level2type=N'COLUMN', @level2name = N'ColumnName' END ALTER TABLE [dbo].[TableName] DROP COLUMN [ColumnName] END --=============================== -- Drop Primary key constraint --=============================== IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_SCHEMA='dbo' AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'PK_Name') BEGIN ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [PK_Name] END --=============================== -- Drop Foreign key constraint --=============================== IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_SCHEMA='dbo' AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'FK_Name') BEGIN ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [FK_Name] END --=============================== -- Drop Unique key constraint --=============================== IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo' AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'UNI_Name') BEGIN ALTER TABLE [dbo].[TableNames] DROP CONSTRAINT [UNI_Name] END --=============================== -- Drop Check constraint --=============================== IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='CHECK' AND TABLE_SCHEMA='dbo' AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'CH_Name') BEGIN ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [CH_Name] END --=============================== -- Drop a column's Default value constraint --=============================== DECLARE @ConstraintName VARCHAR(100) SET @ConstraintName = (SELECT TOP 1 s.name FROM sys.sysobjects s JOIN sys.syscolumns c ON s.parent_obj=c.id WHERE s.xtype='d' AND c.cdefault=s.id  AND parent_obj = OBJECT_ID('TableName') AND c.name ='ColumnName') IF @ConstraintName IS NOT NULL BEGIN EXEC ('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT ' + @ConstraintName) END --=============================== -- Example of how to drop dynamically named Unique constraint --=============================== DECLARE @ConstraintName VARCHAR(100) SET @ConstraintName = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo' AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME LIKE 'FirstPartOfConstraintName%') IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo' AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = @ConstraintName) BEGIN EXEC ('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT ' + @ConstraintName) END --=============================== -- Check for and drop a temp table --=============================== IF OBJECT_ID('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName --=============================== -- Drop a stored procedure --=============================== IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='dbo' AND ROUTINE_NAME = 'StoredProcedureName') BEGIN DROP PROCEDURE [dbo].[StoredProcedureName] END --=============================== -- Drop a UDF --=============================== IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION' AND ROUTINE_SCHEMA='dbo' AND  ROUTINE_NAME = 'UDFName') BEGIN DROP FUNCTION [dbo].[UDFName] END --=============================== -- Drop an Index --=============================== IF EXISTS (SELECT * FROM SYS.INDEXES WHERE name = 'IndexName') BEGIN DROP INDEX TableName.IndexName END --=============================== -- Drop a Schema --=============================== IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'SchemaName') BEGIN EXEC('DROP SCHEMA SchemaName') END

    Read the article

  • How to raycast select a scaled OBB?

    - by user3254944
    I have the OBB picking code to select an OBB with code inspired from Real time Rendering 3 and opengl-tutorial.org. I can successfully select objects that have been moved or rotated. However, I cant correctly select an object that has been scaled. The bounding box scales right, but the I can only select the object in a thin strip on its center. How do I fix the checkForHits() function to allow it to read the scaling that I passed to it in the raycast matrix? void GLWidget::selectObjRaycast() { glm::vec2 mouse = (glm::vec2(mousePos.x(), mousePos.y()) / glm::vec2(this->width(), this->height())) * 2.0f - 1.0f; mouse.y *= -1; glm::mat4 toWorld = glm::inverse(ProjectionM * ViewM); glm::vec4 from = toWorld * glm::vec4(mouse, -1.0f, 1.0f); glm::vec4 to = toWorld * glm::vec4(mouse, 1.0f, 1.0f); from /= from.w; to /= to.w; fromAABB = glm::vec3(from); toAABB = glm::normalize(glm::vec3(to - from)); checkForHits(); } void GLWidget::checkForHits() { for (int i = 0; i < myWin.myEtc->allObj.size(); ++i) //check for hits on each obj's bb { bool miss = 0; float tMin = 0.0f; float tMax = 100000.0f; glm::vec3 bbPos(myWin.myEtc->allObj[i]->raycastM[3].x, myWin.myEtc->allObj[i]->raycastM[3].y, myWin.myEtc->allObj[i]->raycastM[3].z); glm::vec3 delta = bbPos - fromAABB; for (int j = 0; j < 3; ++j) { glm::vec3 axis(myWin.myEtc->allObj[i]->raycastM[j].x, myWin.myEtc->allObj[i]->raycastM[j].y, myWin.myEtc->allObj[i]->raycastM[j].z); float e = glm::dot(axis, delta); float f = glm::dot(toAABB, axis); if (fabs(f) > 0.001f) { float t1 = (e + myWin.myEtc->allObj[i]->bbMin[j]) / f; float t2 = (e + myWin.myEtc->allObj[i]->bbMax[j]) / f; if (t1 > t2) { float w = t1; t1 = t2; t2 = w; } if (t2 < tMax) tMax = t2; if (t1 > tMin) tMin = t1; if (tMax < tMin) miss = 1; } else { if (-e + myWin.myEtc->allObj[i]->bbMin[j] > 0.0f || -e + myWin.myEtc->allObj[i]->bbMax[j] < 0.0f) miss = 1; } } if (miss == 0) { intersection_distance = tMin; myWin.myEtc->sel.push_back(myWin.myEtc->allObj[i]); myWin.myEtc->allObj[i]->highlight = myWin.myGLHelp->highlight; break; } } } void Object::render(glm::mat4 PV) { scaleM = glm::scale(glm::mat4(), s->val_3); r_quat = glm::quat(glm::radians(r->val_3)); rotationM = glm::toMat4(r_quat); translationM = glm::translate(glm::mat4(), t->val_3); transLocal1M = glm::translate(glm::mat4(), -rsPivot->val_3); transLocal2M = glm::translate(glm::mat4(), rsPivot->val_3); raycastM = translationM * transLocal2M * rotationM * scaleM * transLocal1M; // MVP = PV * translationM * transLocal2M * rotationM * scaleM * transLocal1M; }

    Read the article

  • SQL to select random mix of rows fairly [migrated]

    - by Matt Sieker
    Here's my problem: I have a set of tables in a database populated with data from a client that contains product information. In addition to the basic product information, there is also information about the manufacturer, and categories for those products (a product can be in one or more categories). These categories are then referred to as "Product Categories", and which stores these products are available at. These tables are updated once a week from a feed from the customer. Since for our purposes, some of the product categories are the same, or closely related for our purposes, there is another level of categories called "General Categories", a general category can have one or more product categories. For the scope of these tables, here's some rough numbers: Data Tables: Products: 475,000 Manufacturers: 1300 Stores: 150 General Categories: 245 Product Categories: 500 Mapping Tables: Product Category -> Product: 655,000 Stores -> Products: 50,000,000 Now, for the actual problem: As part of our software, we need to select n random products, given a store and a general category. However, we also need to ensure a good mix of manufacturers, as in some categories, a single manufacturer dominates the results, and selecting rows at random causes the results to strongly favor that manufacturer. The solution that is currently in place, works for most cases, involves selecting all of the rows that match the store and category criteria, partition them on manufacturer, and include their row number from within their partition, then select from that where the row number for that manufacturer is less than n, and use ROWCOUNT to clamp the total rows returned to n. This query looks something like this: SET ROWCOUNT 6 select p.Id, GeneralCategory_Id, Product_Id, ISNULL(m.DisplayName, m.Name) AS Vendor, MSRP, MemberPrice, FamilyImageName from (select p.Id, gc.Id GeneralCategory_Id, p.Id Product_Id, ctp.Store_id, Manufacturer_id, ROW_NUMBER() OVER (PARTITION BY Manufacturer_id ORDER BY NEWID()) AS 'VendorOrder', MSRP, MemberPrice, FamilyImageName from GeneralCategory gc inner join GeneralCategoriesToProductCategories gctpc ON gc.Id=gctpc.GeneralCategory_Id inner join ProductCategoryToProduct pctp on gctpc.ProductCategory_Id = pctp.ProductCategory_Id inner join Product p on p.Id = pctp.Product_Id inner join StoreToProduct ctp on p.Id = ctp.Product_id where gc.Id = @GeneralCategory and ctp.Store_id=@StoreId and p.Active=1 and p.MemberPrice >0) p inner join Manufacturer m on m.Id = p.Manufacturer_id where VendorOrder <=6 order by NEWID() SET ROWCOUNT 0 (I've tried to somewhat format it to make it cleaner, but I don't think it really helps) Running this query with an execution plan shows that for the majority of these tables, it's doing a Clustered Index Seek. There are two operations that take up roughly 90% of the time: Index Seek (Nonclustered) on StoreToProduct: 17%. This table just contains the key of the store, and the key of the product. It seems that NHibernate decided not to make a composite key when making this table, but I'm not concerned about this at this point, as compared to the other seek... Clustered Index Seek on Product: 69%. I really have no clue how I could make this one more performant. On categories without a lot of products, performance is acceptable (<50ms), however larger categories can take a few hundred ms, with the largest category taking 3s (which has about 170k products). It seems I have two ways to go from this point: Somehow optimize the existing query and table indices to lower the query time. As almost every expensive operation is already a clustered index scan, I don't know what could be done there. The inner query could be tuned to not return all of the possible rows for that category, but I am unsure how to do this, and maintain the requirements (random products, with a good mix of manufacturers) Denormalize this data for the purpose of this query when doing the once a week import. However, I am unsure how to do this and maintain the requirements. Does anyone have any input on either of these items?

    Read the article

  • Running SQL*Plus with bash causes wrong encoding

    - by Petr Mensik
    I have a problem with running SQL*Plus in the bash. Here is my code #!/bin/bash #curl http://192.168.168.165:8080/api_test/xsql/f_exp_order_1016.xsql > script.sql wget -O script.sql 192.168.168.165:8080/api_test/xsql/f_exp_order_1016.xsql set NLS_LANG=_.UTF8 sqlplus /nolog << ENDL connect login/password set sqlblanklines on start script.sql exit <<endl I download the insert statements from our intranet, put it into sql file and run it through SQL*Plus. This is working fine. My problem is that when I save the file script.sql my encoding goes wrong. All special characters(like íášc) are broken and that's causing inserting wrong characters into my DB. Encoding of that file is UTF-8, also UTF-8 is set on the XSQL page on our intranet. So I really don't know where could be a problem. And also any advices regarding to my script are welcomed, I am total newbie in Linux scripting:-)

    Read the article

  • jquery - targetting a select using $this

    - by Maureen
    I am trying to get individual selects (which have the same class as other selects) to respond to a .change function, however it only works on one of the selects. If you test out this code it will make more sense. Try to add a few "ON/OFF events", and then select "Specified Time" in the various selects. You'll see only the first one responds. Any ideas? Thanks! Please see the following code: $(document).ready(function() { var neweventstring = '<div class="event">Turns <select name="ONOFF"><option value="On">ON</option><option value="Off">OFF</option></select> at: <select name="setto" class="setto"><option>Select Time</option><option value="Sunrise">Sunrise</option><option value="Sunset">Sunset</option><option value="specifiedtime">Specified Time</option></select></div>'; $('#addmondaysevent').click(function () { $('#monday .events').append(neweventstring); }); $('.setto').change(function() { alert('The function is called'); if($("option:selected", this).val() =="specifiedtime"){ alert('If returns true'); $(this).css("background-color", "#cc0000"); $(this).after('<div class="specifictime"><input type="text" value="00" style="width:30px"/> : <input type="text" value="00" style="width:30px"> <select name="ampm"><option value="AM" selected>AM</option><option value="PM">PM</option></select></div>') } }); }); And my HTML: <div id="monday"> <h2>Mondays</h2> <div class="events"> <div class="event"> Turn <select name="ONOFF"> <option value="On">ON</option> <option value="Off">OFF</option> </select> at: <select name="setto" class="setto"> <option>Select Time</option> <option value="Sunrise">Sunrise</option> <option value="Sunset">Sunset</option> <option value="specifiedtime">Specified Time</option> </select> </div> [<a id="addmondaysevent">Add an ON/OFF event</a>] </div> </div>

    Read the article

  • SQL Select syntax error

    - by Odette
    hi guys thanks for your help yesterday. I am now trying to incorporate the query from yesterday into an existing query so I can show the highest itemcode's reporting group in the existing query..but I have a syntax error somewhere at my Select statement. ERROR: Keyword SELECT not expected. I have tried putting brackets at every possible place but still no go..can you please help? (ps-this whole query has been giving me nightmares!) WITH CALC1 AS (SELECT OTQUOT, OTIT01 AS ITEMS, ROUND(OQCQ01 * OVRC01,2) AS COST FROM @[email protected] WHERE OTIT01 < '' UNION ALL SELECT OTQUOT, OTIT02 AS ITEMS, ROUND(OQCQ02 * OVRC02,2) AS COST FROM @[email protected] WHERE OTIT02 < '' UNION ALL SELECT OTQUOT, OTIT03 AS ITEMS, ROUND(OQCQ03 * OVRC03,2) AS COST FROM @[email protected] WHERE OTIT03 < '' UNION ALL SELECT OTQUOT, OTIT04 AS ITEMS, ROUND(OQCQ04 * OVRC04,2) AS COST FROM @[email protected] WHERE OTIT04 < '' UNION ALL SELECT OTQUOT, OTIT05 AS ITEMS, ROUND(OQCQ05 * OVRC05,2) AS COST FROM @[email protected] WHERE OTIT05 < '' UNION ALL SELECT OTQUOT, OTIT06 AS ITEMS, ROUND(OQCQ06 * OVRC06,2) AS COST FROM @[email protected] WHERE OTIT06 < '' UNION ALL SELECT OTQUOT, OTIT07 AS ITEMS, ROUND(OQCQ07 * OVRC07,2) AS COST FROM @[email protected] WHERE OTIT07 < '' UNION ALL SELECT OTQUOT, OTIT08 AS ITEMS, ROUND(OQCQ08 * OVRC08,2) AS COST FROM @[email protected] WHERE OTIT08 < '' UNION ALL SELECT OTQUOT, OTIT09 AS ITEMS, ROUND(OQCQ09 * OVRC09,2) AS COST FROM @[email protected] WHERE OTIT09 < '' UNION ALL SELECT OTQUOT, OTIT10 AS ITEMS, ROUND(OQCQ10 * OVRC10,2) AS COST FROM @[email protected] WHERE OTIT10 < '' ) (SELECT OTQUOT, DESC FROM ( SELECT OTQUOT, ITEMS, B.IXRPGP AS GROUP, C.OTRDSC AS DESC, COST, ROW_NUMBER() OVER (ORDER BY COST DESC) AS RN FROM CALC1 AS A INNER JOIN @[email protected] AS B ON (A.ITEMS = B.IKITMC) INNER JOIN DATAGRP.GDSGRP AS C ON (B.IXRPGP = C.OKRPGP) ) T WHERE T.RN = 1) SELECT A.OKPBRN, A.OCAREA, A.OTCCDE, A.OTCNAM, A.OTSMAN, A.OKPBRN||A.OAPNUM AS OTQUOT, A.OTONUM, A.OTCAD1, A.OTCAD2, A.OTCAD3, A.OTPCDE, A.OTDEL1, A.OTDEL2, A.OTDEL3, CHAR(DATE(CASE WHEN SUBSTR(A.ODOQDT,5,4) = '0000' THEN '0001' ELSE SUBSTR(A.ODOQDT,5,4) END ||'-'|| CASE WHEN SUBSTR(A.ODOQDT,4,2) = '00' THEN '01' ELSE SUBSTR(A.ODOQDT,3,2) END ||'-'|| CASE WHEN SUBSTR(A.ODOQDT,1,2) = '00' THEN '01' ELSE SUBSTR(A.ODOQDT,1,2) END), ISO) AS ODOQDT_CCYYMMDD, CHAR(DATE(CASE WHEN SUBSTR(A.ODDELD,7,2) = '' THEN '0001' ELSE '20'||SUBSTR(A.ODDELD,7,2) END ||'-'|| CASE WHEN SUBSTR(A.ODDELD,4,2) = '' THEN '01' ELSE SUBSTR(A.ODDELD,4,2) END ||'-'|| CASE WHEN SUBSTR(A.ODDELD,1,2) = '' THEN '01' ELSE SUBSTR(A.ODDELD,1,2) END), ISO) AS ODDELD_CCYYMMDD, B.DESC, A.OVQTVL FROM @[email protected] AS A INNER JOIN CALC1 AS B ON (A.OKPBRN||A.OAPNUM = B.OTQUOT) WHERE A.OKPBRN = '@OKPBRN@' AND A.OTCCDE NOT LIKE '*DEP%' AND CHAR(DATE(CASE WHEN SUBSTR(A.ODOQDT,5,4) = '0000' THEN '0001' ELSE SUBSTR (A.ODOQDT,5,4) END ||'-'|| CASE WHEN SUBSTR(A.ODOQDT,4,2) = '00' THEN '01' ELSE SUBSTR(A.ODOQDT,3,2) END ||'-'|| CASE WHEN SUBSTR(A.ODOQDT,1,2) = '00' THEN '01' ELSE SUBSTR(A.ODOQDT,1,2) END), ISO) = CHAR(CURDATE() - 3 MONTH, ISO) AND A.OCQF01 = '0' AND A.OCQF02 = '0' AND A.OCQF04 = '0' AND A.OCQF05 = '0' AND A.OCQF06 = '0' AND A.OCQF07 = '0' AND A.OCQF08 = '0' AND A.OCQF09 = '0' AND A.OCQF10 = '1' AND A.OTCGRP LIKE 'S/%' ORDER BY A.OTSMAN ASC, A.OVQTVL DESC, CHAR(DATE(CASE WHEN SUBSTR(A.ODDELD,7,2) = '' THEN '0001' ELSE '20'||SUBSTR(A.ODDELD,7,2) END ||'-'|| CASE WHEN SUBSTR(A.ODDELD,4,2) = '' THEN '01' ELSE SUBSTR(A.ODDELD,4,2) END ||'-'|| CASE WHEN SUBSTR(A.ODDELD,1,2) = '' THEN '01' ELSE SUBSTR(A.ODDELD,1,2) END),ISO) ASC

    Read the article

  • AJAX Generated Select Won't Redirect

    - by James
    So, basically I have this select / drop down menu that I use AJAX to retrieve and create, though when an option is selected (so onChange) I want it to redirect! Though, this still isn't working, I don't get any errors thrown when trying, and tried to do alert() debug methods yet the alerts don't get called. jquery $("#schoolMenu").change(function() { option = $("#schoolMenu option:selected").text(); alert(option); if(option != "- Please Select -") { window.location = "http://www.itmustbecollege.com/pics/pics-from-"+$("#schoolMenu option:selected").val(); } }); This is what is used to call the AJAX // // Populate Schools // $("#state").change(function() { option = $("#state option:selected").text(); if($(this).attr("class") == "menu") { if(option != "- Please Select -") { $.ajax({ type: "GET", url: "/includes/functions.php", data: "f=school&p="+option+"&m=yes", success: function(msg) { $("#fSchool").html("<p style=\"margin-left: 20px;\">Select School:</p>"+ msg); $("#fSchool").show(); $("#school").focus(); } }); } else { $("#fSchool").html(""); $("#fSchool").hide(); } } else { if(option != "- Please Select -") { $.ajax({ type: "GET", url: "/includes/functions.php", data: "f=school&p="+option, success: function(msg) { $("#fSchool").html(msg); $("#fSchool").show(); $("#school").focus(); } }); } else { $("#fSchool").html(""); $("#fSchool").hide(); } } }); It loads perfectly, if you look at http://www.itmustbecollege.com/quotes/ on that bar where you can do "sort by" of Popular | Newest | Category | School if you hover over school a dropdown comes up, select any country and another one appears, though when that is changed nothing happens. here is the PHP for that second drop down // Get College List function getCollege($state, $m = "no", $l = "no") { // Displays Schools if($m == "yes") { $options = '<select id="schoolMenu" name="school"><option value="select" selected="selected">- Please Select -</option>'; } else if($l == "yes" || $l == "yes2") { $options = ''; } else { $options = '<select name="school"><option value="select" selected="selected">- Please Select -</option>'; } $listArray = split("\|\\\\", $list); for($i=0; $i < count($listArray); $i++) { if($m == "yes") { $options .= '<option value="'. trim(titleReplace($listArray[$i])) .'">'. trim($listArray[$i]) .'</option>'; } else if($l == "yes") { $options .= '<li><a href="/quotes/quotes-from-'. titleReplace($listArray[$i]) .'" title="'. trim($listArray[$i]) .' Quotes">'. trim($listArray[$i]) .'</a></li>'; } else if($l == "yes2") { $options .= '<li><a href="/pics/pics-from-'. titleReplace($listArray[$i]) .'" title="'. trim($listArray[$i]) .' Pictures">'. trim($listArray[$i]) .'</a></li>'; } else { $options .= '<option value="'. trim($listArray[$i]) .'">'. trim($listArray[$i]) .'</option>'; } } echo $options .='</select>'; return false; } any help would be great! EDIT: Also, the way I have those drop downs coming for the menus is a bit weird and when you hover over any other "sort by" link they disappear, this is a problem with the "sort by school" because the first select box shows the list up, and if you go and select a school then somehow float over another link it disappears, any help on how to delay that or fix that minor problem?

    Read the article

  • How can I select values from different rows depending on the most recent entry date, all for the sam

    - by user321185
    Basically I have a table which is used to hold employee work wear details. It is formed of the columns: EmployeeID, CostCentre, AssociateLevel, IssueDate, TrouserSize, TrouserLength, TopSize & ShoeSize. An employee can be assigned a pair of trousers, a top and shoes at the same time or only one or two pieces of clothing. As we all know peoples sizes and employee levels can change which is why I need help really. Different types of employees (associatelevels) require different colours of clothing but you can ignore this part. Everytime an employee receives an item of clothing a new row will be inserted into the table with an input date. I need to be able to select the most recent clothes size for each item of clothing for each employee. It is not necessary for all the columns to hold values because an employee could receive trousers or poloshirts at different times in the year. So for example if employee '54664LSS' was given a pair of 'XL' trousers and a 'L' top on 24/03/11 but then received a 'M' top on 26/05/10. The input of these items would be help on two different rows obviously. So if I wanted to select the most recent clothing for each clothes category. Then the values of the 'M' sized top and the 'L' sized trousers would need to be returned. Any help would be greatly appreciated as I'm pretty stuck :(. Thanks.

    Read the article

  • Conditionally Summing the same Column multiple times in a single select statement?

    - by btollett
    I have a single table that shows employee deployments, for various types of deployment, in a given location for each month: ID | Location_ID | Date | NumEmployees | DeploymentType_ID As an example, a few records might be: 1 | L1 | 12/2010 | 7 | 1 (=Permanent) 2 | L1 | 12/2010 | 2 | 2 (=Temp) 3 | L1 | 12/2010 | 1 | 3 (=Support) 4 | L1 | 01/2011 | 4 | 1 5 | L1 | 01/2011 | 2 | 2 6 | L1 | 01/2011 | 1 | 3 7 | L2 | 12/2010 | 6 | 1 8 | L2 | 01/2011 | 6 | 1 9 | L2 | 12/2010 | 3 | 2 What I need to do is sum the various types of people by date, such that the results look something like this: Date | Total Perm | Total Temp | Total Supp 12/2010 | 13 | 5 | 1 01/2011 | 10 | 2 | 1 Currently, I've created a separate query for each deployment type that looks like this: SELECT Date, SUM(NumEmployees) AS "Total Permanent" FROM tblDeployment WHERE DeploymentType_ID=1 GROUP BY Date; We'll call that query qSumPermDeployments. Then, I'm using a couple of joins to combine the queries: SELECT qSumPermDeployments.Date, qSumPermDeployments.["Total Permanent"] AS "Permanent" qSumTempDeployments.["Total Temp"] AS "Temp" qSumSupportDeployments.["Total Support"] AS Support FROM (qSumPermDeployments LEFT JOIN qSumTempDeployments ON qSumPermDeployments.Date = qSumTempDeployments.Date) LEFT JOIN qSumSupportDeployments ON qSumPermDeployments.Date = qSumSupportDeployments.Date; Note that I'm currently constructing that final query under the assumption that a location will only have temp or support employees if they also have permanent employees. Thus, I can create the joins using the permanent employee results as the base table. Given all of the data I currently have, that assumption holds up, but ideally I'd like to move away from that assumption. So finally, my question. Is there a way to simplify this down to a single query or is it best to separate it out into multiple queries - if for no other reason that readability.

    Read the article

  • how to diffrentiate between same field names of two tables in a select query??

    - by developer
    i have more than two tables in my database and all of them contains same field names like table A table B table C field1 field1 field1 field2 field2 field2 field3 field3 field3 . . . . . . . . . . . . I have to write a SELECT query which gets almost all same fields from these 3 tables.Iam using something like this :- select a.field1,a.field2,a.field3,b.field1,b.field2,b.field3,c.field1,c.field2,c.field3 from table A as a, table B as b,table C as c where so and so. but when i print field1's value it gives me the last table values. How can i get all the values of three tables with the same field names??? do i have to write individual query for every table OR there is any ways of fetching them all in a single query????

    Read the article

  • Using a variable derived from a drop-down list as the column name in a select statement ... Access DB

    - by user1459698
    I'm working with the world's worst DB that was already here so don't blame me for that. So, here's what I have so far ... module = txtModule.value presstype = txtPressType.value SQL_query = "SELECT * FROM tbl_spareparts WHERE '"& module &"' <> '""' AND '"& module &"' = '"& presstype &"' AND Manufacturer = '"& txtsrch.value &"' ORDER BY SAP_Part_No" Set rsData = conn.Execute(SQL_query) This brings up the following SQL statement: SELECT * FROM tbl_spareparts WHERE 'Banyan_Module' <> '"' AND 'Banyan_Module' = 'PB' AND Manufacturer = 'Tester' ORDER BY SAP_Part_No Is there any way I can use the module variable as a column name - obviously the ''s around the column name are causing an error. This is really bothering me. BTW, I'm writing this in VBScript inside a .HTA application page as it has to run locally on tech PCs. Thanks. R.

    Read the article

  • Eclipse won't let me select a Windows share as Workspace

    - by Android Eve
    Environment: Ubuntu 10.04 (64-bit), Eclipse Helios 3.6 (64-bit), Android 2.3 SDK + ADT. All works great, but I can only select a workspace that's on the local system. Eclipse won't let me select shared folder on a Samba server. Ubuntu's URI for this share is of the form: smb://[email protected]/sandbox/workspace But even if I typed this manually into the edit box, Eclipse won't accept it. I don't have this problem with Eclipse 3.6 on Windows. Is there a workaround to solve this?

    Read the article

  • Automatically select headphones when plugged in

    - by Joachim Pileborg
    When I plug headphones into my desktop computer, they are not automatically selected for output, instead all sound still goes through my S/PDIF output to the stereo. The headphones alternative is added in the sound settings, and I have to manually select it as output device. $ cat /proc/asound/pcm 00-00: ALC898 Analog : ALC898 Analog : playback 1 : capture 1 00-01: ALC898 Digital : ALC898 Digital : playback 1 00-02: ALC898 Analog : ALC898 Analog : capture 2 I have done a Google search, as well as search askubuntu.com, but none of the answers in the hits I found seems to help. Also, after listening with the headphones and then unplug them, the previous output is not automatically selected, so I have no sound at all then. I have to manually select the correct output in the settings.

    Read the article

  • Boot-up hangs unless I manually select the kernel in the bootloader

    - by The Photon
    In my stock Ubuntu install (forgot which critter its named for, about one year old), I am able to boot up normally only if I manually select the kernel in the bootloader (Grub). If I step away and it boots from the default, the system will hang with the word "Ubuntu" on a graphics screen and a few white/red blinking dots beneath it. umb@digdug:~$ uname -a Linux digdug 2.6.32-42-generic #95-Ubuntu SMP Wed Jul 25 15:57:54 UTC 2012 i686 GNU/Linux Synaptics says I have 'grub-pc' version 1.98-1ubuntu-13 installed. My grub.cfg does have default="0" which I would expect to make the default be the first kernel shown in the selection screen. The system is a laptop with an i7 processor, and I have had trouble with some kernels not being able to boot at all in the past, and power management is not working perfectly, but I have not had problems booting the latest kernel (2.6.32-42) if I select it manually in Grub. Any idea what is going on here and how can I fix it so that I can re-boot unattended?

    Read the article

  • how to change dolphin's select region of a file/folder (in detail view mode)

    - by Daniel
    Since nautilus has removed the dual pane. So I turn to dolphin. I am wondering how to change the default select behavior in detail view mode? Now to select a file/folder, my cursor has to hover over that file/folder and then the left click will take effect, but I much prefer the nautilus way, that is as long as your cursor is in the same row of that file/folder, left click picks up that item. This I think is more robust for user selection, it is faster. Any ideas how to achieve this in dolphin?

    Read the article

  • Entretiens d'embauche : quelles questions et quels tests vous ont le plus marqué ? Et ceux que l'on vous a demandés le plus souvent ?

    Entretiens d'embauche : quelles questions et quels tests vous ont le plus marqué ? Et ceux qu'on vous a demandés le plus souvent ? Un développeur C/C++ américain a tenté de compiler tous les tests et toutes les questions auxquels il a eu droit au cours de 9 entretiens d'embauche dans 5 entreprises de tailles différentes. La liste qui en résulte a de quoi surprendre tant elle est centrée sur la pratique et la résolution de problèmes concrets, notamment de bas niveau : manipulation des chaînes de caractères, listes, tableaux, etc... Même les questions d'ordres théoriques et de conception en surprendraient plus d'un. Exemple : pour concevoir un jeu d'échec, quels objet...

    Read the article

  • How to select parent row only if has at least one child?

    - by Matt McCormick
    I have a simple one-to-many relationship. I would like to select rows from the parent only when they have at least one child. So, if there are no children, then the parent row is not returned in the result set. Eg. Parent: +--+---------+ |id| text | +--+---------+ | 1| Blah | | 2| Blah2 | | 3| Blah3 | +--+---------+ Children +--+------+-------+ |id|parent| other | +--+------+-------+ | 1| 1 | blah | | 2| 1 | blah2 | | 3| 2 | blah3 | +--+------+-------+ I want the results to be: +----+------+ |p.id|p.text| +----+------+ | 1 | Blah | | 2 | Blah2| +----+------+

    Read the article

  • Select statement that combines similar rows with certain ids?

    - by vegatron
    hi I have a warehouse_products table which defines how many products in the warehouses so lets say I have 20 records/rows in the table, some rows may contain the same product id but in a different warehouse I need to create select statement that give every product one row, and in this row I must have the quantity in warehouse A and warehouse B .. so in the end I will get for example 10 rows that contain all the data

    Read the article

< Previous Page | 20 21 22 23 24 25 26 27 28 29 30 31  | Next Page >