Analysis :: DAX With IF Statement Very Slow
Jul 21, 2015
I have been trying to optimize a few DAX calculations that I have in my cube and ran into this problem Basically my DAX Measure
Counterparty Collateral Base DKK:=
[Counterparty Collateral Contracts DKK (filtered)]
+
CALCULATE(-[Account Balances DKK], Accounts[Counterpart Type ID] <> 8, Accounts[ContractTypeID] <> 14, Accounts[ContractTypeID] <> 16)
is pretty fast,
whereas as soon as I change the 2nd part to only add Account Balance if its>0, it takes ages i.e.
Counterparty Collateral Base DKK:=
[Counterparty Collateral Contracts DKK (filtered)]
+
IF(CALCULATE(-[Account Balances DKK], Accounts[Counterpart Type ID] <> 8, Accounts[ContractTypeID] <> 14, Accounts[ContractTypeID] <> 16)>0,
CALCULATE(-[Account Balances DKK], Accounts[Counterpart Type ID] <> 8, Accounts[ContractTypeID] <> 14, Accounts[ContractTypeID] <> 16),0)
Offcourse because I check every row I suppose with that "if" statement. Question is how do I improve it?
B
y the Way: [Account Balances DKK] is in the table - Account Balances
[Counterparty Collateral Contracts DKK (filtered)] is in the table- Open Positions
This calculated measure [Counterparty Collateral Base DKK] is in Open Positions.And the query in MDX, that I run to pull this measure, looks like
select {[Measures].[Counterparty Collateral Base DKK]
}on 0,
Non Empty {[Accounts].[Counterpart ID].children
*[Base Currencies].[Currency Code].children
*[Products].[Product Name].children
} on 1
from [model]
where [Dates].[Date].&[2015-06-30T00:00:00]
The requirement is such that I have to add the measures in two separate tables.How can I improve the performance when adding the filter?
View 4 Replies
ADVERTISEMENT
Dec 12, 2005
Hi,
I've got a table with about 500 000 records and growing monthly by about 40 000 records
When I perform the following query:
DELETE from [myTable] WHERE Month = '07' AND Year='2005'
This query will take about 10 minutes to execute. Columns Month & Year are both indexed.
Surely MSSQL can't be this slow on only 500 000 records.
Must I do some other database optimization ???
Thanks
View 6 Replies
View Related
Jul 20, 2005
hello,I have the following query. it returns result in less than 1 second.select *from employee e, address awhere e.id=a.emp_idand e.id=1234The problem is that it becomes extremely slow if i take the last lineout. So the query looks like:select *from employee e, address awhere e.id=a.emp_idThe above query is only supposed to return ~500 rows. but i stillhaven't got the result back after 30 minutes.Does anyone have any suggestions about troubleshooting this problem?Thank you in advance!Eddy
View 5 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related
Nov 14, 2007
The following stored procedure is run whenever someone searches my database. When I first created the site, this search took less than 3 seconds. Now, there is more data in the system and the search is taking over 30 seconds.
I am not very good with SQL and I'm sure I am doing some dumb things. Is there a better way to write this query to speed things up?
Thanks a lot,
Chris MessineoSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Chris Messineo
-- Create date: 4/27/2007
-- Description:Get Search
-- =============================================
ALTER PROCEDURE [dbo].[Search]
@Keyword as varchar(40)=NULL
AS
BEGIN
SELECT
Script.Title,
Script.ScriptID,
Script.Logline,
Member.Name
FROM
Script
Join Contest on (Script.ContestID=Contest.ContestID)
Join ScriptComment on (Script.ScriptID=ScriptComment.ScriptID)
Join Member on (Script.MemberID=Member.MemberID)
WHERE
(Title like '%' + COALESCE(@Keyword, Title) + '%' or
Logline like '%' + COALESCE(@Keyword, Logline) + '%' or
Comments like '%' + COALESCE(@Keyword, Comments) + '%')
GROUP BY
Script.Title,
Script.ScriptID,
Script.Logline,
Member.Name
ORDER BY
Title
END
View 5 Replies
View Related
Jun 11, 2007
Hi, I have a query which returns normally in less than a couple of seconds. When coupled with a "TOP" statement however, the execution time changes in a way I can't explain.
When using "TOP 1000", the query returns in its normal time. Any top value greater than 10 seems to function normally. However, as soon as we hit this "magic number" the query begins taking 30+ seconds to return.
As far as we can see, there is nothing odd about this query or the data that it is searching, and this problem has us stumped! Can anyone explain why top is producing these results, and is there any optomisation changes that anyone can suggest to give us faster queries in these cases?
Many thanks, Andy J.
View 2 Replies
View Related
Apr 6, 2007
I support a website (ASP.NET 2.0) where recently users have been unable to insert data due to timeout issues. The functionality executes a query that inserts a single row into a SQL Server 2005 db table. I tried running this query from the backend, and it took 4:48 to insert a single row! Interestingly, after that initial agony any similar inserts I tried took no time whatsoever. I have checked the execution plan, but unfortunately don't really know what I'm looking for with inserts, as most of my experience with execution plans is with select statements. Any resources anyone could point me to for troubleshooting this would be much appreciated.
Thanks,
-Dave
View 3 Replies
View Related
Jul 12, 2007
We are using sql server 2005 with java and the last version of driver JDBC, some querys show bad performance when you send by application but if you use sql query analyzer the performance is excellent. We have take a profiler trace and saw that Java call SP_PREPEXEC when it makes a call of preparestatement. We taken the sentence from profiler (with SP_PREPEXEC) and saw the same problem, the query plan is bad, but if we avoid use the prepare statement the query works fine and the performance problem is overcome. Since we have the application with java and use every time the preparedstatement sentence from java, it is very expensive for us , change the code. Do you have any solution for these problem?
View 1 Replies
View Related
Nov 2, 2015
Just wanted to know is there any way to write scope statements only for a specific role in the cube.
View 3 Replies
View Related
Nov 3, 2015
I am trying to calculate a measure based on parameter passed I have a liquidation rate of amount/facevalue when the dimension attribute is 'all' and i will like to the 'amount' to change based on the parameter passed and the 'facevalue' should not changed , i tried with the code below.
What I noticed is when i add a calculated member to return the currentmember of the cell member
measures.strat
as
[Strategy].[ICS].CURRENTMEMBER.uniquename
it comes back as
[Strategy].[ICS].[All]
even when I changed the default value for the parameter to another member like red
WITH
MEMBER
MEASURES.TEST
AS
((
CASE
WHEN [Strategy].[ICS].CURRENTMEMBER.uniquename = '[Strategy].[ICS].[All]'
THEN
[Measures].[Measures].[AMOUNT]
[code]....
View 4 Replies
View Related
Jul 2, 2015
I am stuck in a situation where I want to use YTD for three different calendars of our company and don't want to create three different YTD calculations. However I want to make this work for any measure not for a particular measure
If I create one YTD and try to use in context of three calendars in SCOPE statements then it does not give my right results. Following is my syntax but It does not work.
SCOPE([Billing Date].[SalesCalendar].MEMBERS);
( [Aggregate].[AGGREGATE CODE].[YTD] )
= Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[SalesCalendar].[Sales Calendar Year],[Billing Date].[SalesCalendar].CURRENTMEMBER) } );
[Code] ....
However if I comment on one of SCOPE statement , other one works but both don't work simultaneously in context of different calendars.
View 2 Replies
View Related
Mar 5, 2015
I have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.
We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.
View 10 Replies
View Related
May 13, 2014
I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS. In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task. The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube. This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.
I would expect that once development is done, and it is processed and deployed, that is it. My thinking is that the SSIS task should just update the already deployed cube,
View 2 Replies
View Related
Jun 12, 2015
How to right choose key column in"Mining Structure" for Microsoft Analysis Services?
I have table:
"Incoming goods"
Create table Income (
ID int not null identity(1, 1)
[Date] datetime not null,
GoodID int not null,
PriceDeliver decimal(18, 2) not null,
PriceSalse decimal(18, 2) not null,
CONSTRAINT PK_ Income PRIMARY KEY CLUSTERED (ID),
CONSTRAINT FK_IncomeGood foreign key (GoodID) references dbo.Goods ( ID )
)
I'm trying to build a relationship(regression) between “Price Sale” from Good and “Price Deliver”.But I do not know what column better choose as “key column”: ID or GoodID ?
View 2 Replies
View Related
Feb 18, 2013
It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?
View 5 Replies
View Related
Nov 24, 2015
I am using
SSAS: version 2008
Excel: version 2013
I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:
DateKey:
- Type: System.Integer
- Value: yyyyMMdd
Date:
- Type: System.DateTime
DateStr0:
- Type: System.String
- Value: dd/MM/yyyy (note: I am not using US culture)
- Example: 01/11/2015
DateStr1:
- Type: System.String
- Value: %d/%M/yyyy (note: I am not using US culture)
- Example: 1/11/2015
Filtering on date is working fine:
Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:
I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:
But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.
For date values of my data coming from cube, excel consider them as 'General'
2.1. But value here is same as it was in simple test.
2.2. 'Date Filter' works just fine.
2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell.
2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.
2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.
So, I don't know, what is the puzzle piece that I am missing.
1. Date filter works, group does not work
2. Excel consider it as 'General' string.
3. SSAS does not allow to change 'NameColumn' to Date.
View 2 Replies
View Related
May 30, 2015
I would like to know the best practice for running analysis service in terms of port usage. Is it better to run on a specific port or have dynamic ports ? We have clustered servers that run default on 2383 but not sure with non clustered what's the best way to get performance.
View 2 Replies
View Related
Aug 6, 2015
I want to find out if it is possible to create a role in a cube without an AD account, e.g. using a GMail email address.
View 3 Replies
View Related
Oct 17, 2007
Hi,
I have some questions about SQL Servers 2000 and 2005 compatibility.
In my configuration I have to use both servers.
The cubes are stocked in 2005 server.
May I transfer from 2005 to 2000 Analysis Services the cubes?
If yes, what is the procedure? The result of migration is the same in the two different versions?
If not, how can I solve this problem?
Thanks in advance.
View 3 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jul 20, 2005
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
Nov 5, 2015
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies
View Related
Aug 13, 2014
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
What is the best way to script this
View 1 Replies
View Related
Jul 4, 2006
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
View 7 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Feb 4, 2006
I am using ASP.NET 2.0, and am attempting to write some code to connect to the database and query a data table. The compiler is not recognizing my SqlConnection statement. It does recognize other commands. And just to make sure, I created other sql objects such as ObjectDataSource and SqlDataSource. The compiler does not find a problem with that code.
Basically the compiler is telling me that I am missing a "using" directive. The compiler is wrong though, because I am including the statement "usingSystemData" Can someone please take a look at my code below and to see if you notice what the problem might be? Note that I numbered the lines of code below. Note that I also tried putting lines 3 trhough 6 before line 2(The page directive) but that did not fix the problem The compiler still gives me the same compiler message.
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request.Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error:
Line 21: SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");
1 <asp:sqldatasource runat="server"></asp:sqldatasource>
2 <%@ Page Language="C#"%>
3 using System;
4 using System.Data;
5 using System.Collections;
6 using System.Data.SqlClient;
7
8 <script runat=server>
9
10 protected void Page_Load(object o, EventArgs e)
11 {
12 ObjectDataSource dsa; // This works no problems from the compiler here
13 SqlDataSource ds; // This works no problems from the compiler
14
15 if (IsPostBack)
16 {
17 if (AuthenticateUser(txtUsername.Text,txtPassword.Text))
18 {
19 instructions.Text = "Congratulations, your authenticated!";
20 instructions.ForeColor = System.Drawing.Color.Red;
21 SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");
22 String sqlStmt = "Select UserName from LogIn where UserName='" + txtUsername.Text + "' and password='" + sHashedPassword + "'";
23 }
24 else
25 {
26 instructions.Text = "Please try again!";
27 instructions.ForeColor = System.Drawing.Color.Red;
28 }
29 }
30
31 }
32
33 bool AuthenticateUser(string username, string password)
34 {
35 // Authentication code goes here
36
37 }
View 1 Replies
View Related
May 26, 2006
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View 10 Replies
View Related
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
View Related
Feb 20, 2008
i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows
as per data in MS Sql
my tables are as follows
1)material
fields are -- material_id,project_type,project_id,qty, --
2)AB_Corporate_project
fields are-- ab_crp_id,custname,contract_no,field_no
3)Other_project
fields are -- other_proj_id,other_custname,po
for ex :
vales in table's are
AB_Corporate_project
=====================
ab_crp_id custname contract_no field_no
1 abc 234 66
2 xyz 33 20
Other_project
============
other_proj_id other_custname po
1 xxcx 111
2 dsd 222
material
=========
material_id project_type project_id qty
1 AB Corporate 1 3
2 Other Project 2 7
i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project
sample query i write :--
select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id,
ab.custname ,op.other_proj_id,op.other_custname,op. po
case if m.project_type = 'AB Corporate' then
select * from AB_Corporate_project where ab.ab_crp_id = m.project_id
else if m.project_type = 'Other Project' then
select * from Other_project where op.other_proj_id=m.project_id
end
from material m,AB_Corporate_project ab,Other_project op
but this query not work,also it gives errors
i want sql query to show data as follows
material_id project_type project_id custname other_custname qty
1 AB Corporate 1 abc -- 3
2 Other Project 2 -- dsd 7
so plz help me how can i write sql query for to show the output
plz send a sql query
View 8 Replies
View Related
Aug 10, 2006
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View 1 Replies
View Related
Nov 13, 2003
I am writing a data access web page, but I find that the excution speed is too slow.
My data base is just a data table which have five columns: id, code, quantity, price and Date. The data base has about 45000 rows. When I use OSQL or Query function, speed is just fine.
Here is the main code which I think cause the speed slow:
string conn = ConfigurationSettings.AppSettings["connectionstring"];
SqlDataAdapter adapter_2 = new SqlDataAdapter("select * from table",conn);
DataSet ds = new DataSet();
adapter_2.Fill (ds,"table");
DataTable YahooOrders = ds.Tables["YahooOrders"];
DataRow[] product = new DataRow[20000];
.......
foreach (string s in split) // actually the split here has only one string in it
{
product = table.Select ("code like '"+s+"%' and Date >='"+minDate+"' and Date <='"+table.Select("Date = Max(Date)")[0][1].ToString()+"'");
foreach(DataRow myRow in product)
{
int count = Convert.ToInt32(myRow[2]);
itemQuantity = count + itemQuantity;
revenue = Convert.ToDouble(myRow[3]) * count + revenue;
// get product code, ignore repeated code
int myIndex=code.BinarySearch( myRow[1] );
if ( myIndex < 0 )
code.Add(myRow[1]);
}
orderQuantity = product.Length + orderQuantity;
}
The first foreach actually excutes just one time, so it won' t cause any speed problem.
The second foreach' s job is to sum each column of specified rows which is product here.
So, any ideas about this?
Thanks!
View 8 Replies
View Related