I have next approximate tables structure: accounts: ID INT, owner_id INT, currency_id TINYINT related to clients: ID INT and currency_types: ID TINYINT, name NVARCHAR(25) I need to write a stored procedure to check existence of accounts with specific currency and all others, i.e. client can have accounts in specific currency, so...
Hi I have a currency(Rec_ID, currency) table which is having M-M relationship with it self it results in CurrencyExchangeRate table (Rec_ID, Currency_FK1 AS 'FROM', Currency_FK2 AS 'TO, Exchange_Rate) Now I need a query to do all combination of currency rec_id (repetition is not allowed), along with the exchange rate or null. if there ...
An example of my tree table is: ([id] is an identity) [id], [parent_id], [path] 1, NULL, 1 2, 1, 1-2 3, 1, 1-3 4, 3, 1-3-4 My goal is to query quickly for multiple rows of this table and view the full path of the node from its root, through its superiors, down to itself. The ultimate question is, should I generate this path on inse...
I need to write a TSQL procedure which would perform 4 selects and join results int one dataset. Can this be done? P.S. Selects are performed in a loop EDIT Here is the schema. I wan't to get N latest points from each Unit which belongs to SOME map. Maps Id Maps2Units MapId UnitId Units Id Points Id UnitId T...
I've set out to write a method in my C# application which can return an ordered subset of names from a table containing about 2000 names starting at the 100th name and returning the next 20 names. I'm doing this so I can populate a WPF DataGrid in my UI and do some custom paging. I've been using LINQ to SQL but hit a snag with this lo...
Using the table structure below how would I create a bcp bulk insert that would handle the XML data. It is important that this is run from command line. CREATE TABLE [dbo].[MyTable]( [Id] [uniqueidentifier] NOT NULL DEFAULT (newid()), [MyXmlField] [xml] NULL Thanks in advance... ...
Hella all, What I want to do is something like that, I will have an SQL table depending on my parameter, DECLARE @find varchar(30) SET @find = 'no' SELECT * FROM ( if @find = 'yes' ( SELECT * FROM myTable WHERE ID= '5882' ) ELSE ( SELECT * FROM myTable WHERE OLD_ID= '5882' ) ) X This is ...
I am getting the error from the application as following with SQL server 2005 "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0" How can i find the stage where this error raised? how can i found the missing transaction or the stored...
In my stored procedure, I would like to check to make sure what I am trying to insert doesn't already exist in the table. I have tried the code below, but it seems to be giving me false positives (aka comes out to true even when it's not in the table). Is there a better way? if not exists (select myID from tableName where myID = @myID a...
A friend has suggested I post here as I'm in need of a bit of help! DB Layout: **salestable** salesorder [primary, unique] (sales order number) salesman (salesperson id) **salesline** salesorder [many sales line to single salestable relationship] saleprice (line amount) isaccessory (0 or 1) I'd like to, in a single select, sum sales...
I have some data like: Chocolate Strawberies Oranges 2 3 1 4 2 4 How do i get is back as Chocolate 2 Chocolate 4 Strawberies 3 Strawberies 2 Oranges 1 Oranges 4 Without using unions and cases? ...
Hi, What is the symbol (like *=) for doing a left join? I've got table A and B, must always return all records from table A even if there is no records in table B. ...
I would like to use essentially the same query in both T-SQL (SQL Server 2000/2005) and PL/SQL (Oracle 10g). Though it is not trivial, it is reasonably vanilla code, but it works in SQL Server yet fails in Oracle. My goal is to generate a count of rows for each unique upper/lower case combination of a particular field, omitting any field...
Currently I have 2 table [Category] -> PK| CAT_ID -> CAT_PARENT (link to itself | if it's a top parent category then it's 0) [Posts] -> PK | POST_ID -> FK | CAT_ID | CREATE_DATE How do I select top 15 ROWS of Posts in every CAT_PARENT which have multiple child category. So the total Posts in CAT_PARENT and all it's child are only 15. ...
In a trigger, I have code like: SET @var1 = (SELECT col1 FROM Inserted); SET @var2 = (SELECT col2 FROM Inserted); Is it possible to write the above in a single line? Something conceptually like: SET (@var1,@var2) = (SELECT col1,col2 FROM Inserted); Obviously I tried the above, without success; am I just stuck with the first method?...
eg. I have 2 tables Customers and Orders Customers has columns CustomerID, Name Orders has columns OrderID, OrderedOn where OrderedOn is a DateTime Now I want a query which will give me the CustomerID OrderID and OrderTally where OrderTally = 'Initial' for the first order OrderTally = 'InMiddle' for everything in the middle and ...
I have this table: Strawberries 2 Strawberries 3 Strawberries 4 Chocolate 3 Chocolate 4 Chocolate 5 Chocolate 6 Apples 3 Apples 4 Apples 5 Apples 6 My idea is to get the number of items, and the total of items per thing, like so: Item Number Total Strawberries 2 9 Strawberries 3 9 Strawberries 4...
I have the following query: SELECT FROM tblMailToSend WHERE (DateToSend < @dateToSend OR DateToSend IS NULL) AND DateSent IS NULL @dateToSend is passed in as a param I'm trying to convert this to linq to sql query. I've got: db.MailToSend.Where(m => m.DateToSend == null || m.DateToSend <= dateToSend) .Where(m =>...
Hi I want to use SQL SERVER 2005 commands to generate tab-delimited file. I want to create a complex query and give it to something (say a function) to export data with columns names into a tab-delimited file. If u don't know something doing this, suggest an idea that reduces the headache of doing it from scratch using C#. AHH, I am...
Hello All, How can I do the following in SQL Server DECLARE @Local nvarchar(20) SET @Local = 'True' SELECT * FROM My_Table WHERE my_ID IN (IF @Local = 'True' SELECT AllIDs FROM ATable ELSE SELECT TeamIDs FROM TeamTable ) ...