.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Joining Dimtime table by a referenced Join create a problem

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server


I am trying to join a dimtime table by a refernced join to an orderlines table which is the fact.

(the referenced join was defined in the relationship matrix of the cube ).

The referenced table is the ordersheader which had no measures in it but only used a linking table between those two other.

The join from the dimtime to the ordersheader  is done by date and from ordersheader to orderlines by orderid

The problem is that in the cube browser measures are shown as they were multiplied across all dates (like the join was done in a wrong way )

I have created an sql query and the logic is working just fine when quering directly from the DB


What am I doing wrong

View Complete Post

More Related Resource Links

Table JOIN Problem


I write the following queries :

select y.date, y.item,y.ingredient, (CAST(z.rate AS DECIMAL(10,4)) * CAST(y.qty AS DECIMAL(10,4))) FROM master.recipe_ingredient AS x JOIN master.ingredient as z on x.ingredient = z.item JOIN executive.consumption_Entry as p ON x.recid = p.recid JOIN executive.consumption_total_ingredient AS y ON p.cid = y.cid

and the output is:

10/09/2010    REG    MAIDA    491.37600000
10/09/2010    REG    MAIDA    1152.89800000
10/09/2010    REG    MAIDA    169.44000000
10/09/2010    REG    MAIDA    1306.10000000
10/09/2010    REG    MAIDA    3400.44900000
10/09/2010    REG    MAIDA    1742.40800000
10/09/2010    REG    MAIDA    1553.20000000
10/09/2010    REG    MAIDA    3812.40000000
10/09/2010    REG    MAIDA    1629.44800000
10/09/2010    REG    MAIDA    29652.00000000

10/09/2010    REG    SUGAR &nbs

joining 3 tables and using join to only include records not found in other table



I have 3 tables. I would like to get the number of records in table A that do not have cooresponding co_asset_ids in TABLE C but also need
to be be found in TABLE B and where the asset_id = 3. Said another way: If TABLE A record is in TABLE B and does not find a co_asset_id of 3 as found in table C then show these records.

assetno 4 and 5 are irrelevant but added to fill out the data more.

I know I could do this getting the co_asset_id column into TABLE A and then do some joins between TABLE A and TABLE C and leave TABLE B out, which I have done in the past. I would like to finally know if this can be done by using the 3 tables together in one elegant query.

The result of this query for this example data  would be:

Table A

Table B
assetno    co_asset_id
1              11
2              22
3              33
4              44
5              55


Create new sql table at runtime VB.Net


I want to create a new table to sql database at runtime.  The column names would be "Name", "Date", "Event".  I would also like to have one more column name "Image" but the problem is that since the amount of images will vary, I want the application to count the amount of files in the Multiple fileuploader and then add a image column to the table for the respected amount of files. 

For example if i am uploading 3 files in the multiple fileuploader, the names should be "Name", "Date", "Event", "Image1", "image2", Image3"

I also would like that table name to be name dynamically from the textbox Name when the user enters the information.

I have the following code to make a new table, but I dont know how to name the fields at runtime like previously asked please help!!!

Dim objConn As New SqlConnection("Server=<servername>;uid=<userid>;pwd=<password>;database=master")


Create/add table from code to database



I create table:

                  DataSet data=new DataSet();

                  DataTable myTable = new DataTable("NewTable");

                   DataColumn[] keys = new DataColumn[1];

                   // create column
                   keys[0] = new DataColumn();
                   keys[0].DataType = System.Type.GetType("System.String");
                   keys[0].ColumnName = "PointID";
                   myTable.Columns.Add(keys[0]);    &nb

Create table in the database using management studio


 I have database already on the server. However, when I created new table, the new created table Schema is not dbo. it is my user name. How to solve this?

It shows the new created table Schema is my username but not dbo.

Even though I rename it, it couldn't change. I can see this in table property.



generate create script of table using c# and SQL server 7.0, can anyone help me?


I want to generate create table script using c#.net, I want to connect sql server 7.0 and generate table create script. Its urgent kindly help me urgently.

How to create scrolling table for columns in a SharePoint Designer Data View


I have a fairly complex SharePoint Designer 2007 Data View they have added two more requirements.

1. Have the columns on the right side be able to scroll left to right as there is a large number of columns, while the first 3 columns on the left side stay locked in place.  This is only one list not two, and because of some other requirements I have it must remain to be just one list.

2. Then they also need to have scrolling up and down of the entire list with the headers locked in place.

The first requirement is more important than the second, but preferrably I need to do both.

I am new to XSL so not familar with the syntax. Is this something I can do on a custom Data View? Does anyone have any examples of how?

I have a drawing that show how it needs to look but I don't see an option here to upload a graphic.

So here is a link to my graphic example uploaded to my blog area: http://lindachapman.blogspot.com/  or just the image here: http://4.bp.blogspot.com/_aB01ue__NvQ/TGL42MOvrGI/AAAAAAAAAA4/c3xP4uCy4BU/s1600/Scrolling.png

I failed to mention that 3/4 of the columns are HTML Calculated fields wh

Can't perform Create, Update or Delete operations on 'Table(outbox)' because it has no primary ke



I'm a beginner in asp.net and I have some problem inserting data into sql server 2005 with linq.


my code is : 


        Dim Result As New SMS.SendResult
        If Result.status = 0 Then
            Label1.Text = "Your Message Sent Successfuly"
            Dim db As New IGSSDataContext
            Dim out As New outbox With {.ClientID = intRandomNumber, .From = User.Identity.Name, .TO = TextBox1.TabIndex, _
            .Time = Now, .Message = Message}
            out.ID = (ID)
            TextBox1.Text = ""

Want to Create Table Dynamically With text Control in WinForm Csharp

Hello To All... from Many Days i m tryng to create a simple table using csharp in WinForm.Which will include textbox in each cell according the Passed No of Col & Row. but not getting the proper result. Actually i want to trace each cell data and want to save each cell data to data base : eg. in cell 11(1col & 1row) i m having Combobox which having some item list and when the user select the list item the data will save to the database. Regards, Praveen    What is the Key of Life ... Its Sharing Your Thought & Knowledge ...

Create Table from MDX query

Hi,I want create a table from the result of the MDX query. When I finish the wizard of import data in SQL Server Environment. at the last stage, the error is : Pre-execute (Error) Messages Information 0x4004300b: Data Flow Task 1: "component "Destination - Query" (31)" wrote 0 rows.(SQL Server Import and Export Wizard) please guid  me.Thank you a lot.Naeimeh

DTA doesn't find referenced table form stored procedure with SP_executesql

Using the SQL2008R2 profiler and DTA connected to a SQL2000 server. I created a tuning tracefile with profiler for a certain db on the sql2000 server because I need to tune 1 table in the database referenced by 3 parameterized stored procedures which built a query depending on the parameters and is executed with an sp_executesql statement. When I run the DTA loading the tracefile I see the exec stored procedure events with it's parameters and the message Event does not reference any tables. And that's it. The DTA generates no Recommendatioin at all. How can I get the exact sql statements in the tracefile so the DTA can do what he needs to do? Regards

sql server 2005 express - don't allow to create table

i have downloaded sql server 2005 Express version from the web and use domainname\SQLEXPRESS as server name but now i can't create table seems every create by sa user, what is the password of this user or how to grand right to my window login user so that i can modify/create something in database.

select max record to join another table sybase

select a.pono,(select (user) from user where userid=a.userid having date=max(date)) as user from a inner join b on a.no=b.no  in the result , i have selected the same id and retrieve two records every thing are same except the date how can i select the record out of two record which date is max date as the where Clasuse to select correct user poid    date                name 1        12/08/2010      Mary 1        20/08/2010      Peter   now i would like to select name which id=1 and date is max and then use the name to join another table because name is foreign key  

Problem to create assembly which depends on other dll's from SQL Server

I am developing an application in .Net but I need to access to some procedures in ASP.Net since SQL Server.I have got it but i have problems to assembly the dll if it has dependencies of another dll's.I have read that if a dll need other dll it call it automatically but when I try to assembly my class in .NET there is the next error: Create failed for SqlAssembly 'ClaseEjemplo'. Assembly 'csyt.dal, version=, culture=neutral, publickeytoken=null.' was not found in the SQL catalog. (.Net SqlClient Data Provider) Nevertheless if I the class hasn't another dependencies it works.The class I am trying to assembly is ClaseEjemplo.If I try to use the procedure of other class "'csyt.dal" I have problems.If ClaseEjemplo hasn't dependencies it works perfectly!My class ClaseEjemplo is: using System;using System.Collections.Generic;using System.Text;using Csyt.DAL;using Csyt.Info; namespace ClaseEjemplo{    public class Class1    {        public static long devolucion()        {            AIAlarmaDB.TipoCondicion aux1 = new AIAlarmaDB.TipoCondicion();                        AIAlarmaDB aux = new AIAlarmaDB();            aux1=aux.GetTipoCondicionById(1);            return aux1.Id;        }     }}Thanks in advance

How to create a view in my database from a table in another server/database

Is there a way to create a view in my database referencing a table in another server/database if I have the connection information.  I know how to do it in Oracle, simply create a database link, but have never done so in sqlserver.

problem with inner join

Hi, I'm trying to create an sql query with multiple inner joins, but I can't manage this. I have 3 tables (bookings, accountplan, costs), where the data come from. In accountplan are hold the accounts, in costs the cost codes and their names, in bookings the booked events. Based on a booking event, I need the account name, the name of the cost code and all data from bookings (I need the cred account name too, but I can't figure out yet, how I can get this) I tried this: "SELECT bookings.*, accountplan.accountname, costs.costname FROM bookings INNER JOIN accountplan ON bookings.debaccount=accountplan.account INNER JOIN costs ON bookings.costcode=costs.costcode" It is here a where clause too, but I tested it, it's ok. The above query results no rows. If I remove the costs table and the inner join with it, it works. What am I missing?

unable to create new table in sql server 2000 enterprise manager on windows7(Operating system)

i have successfuly installed sql server 2000 on windows7 and i am able to browse all the tables and edit aswell(on enterprise manager) but i have a problem while creating new tables it says ' MMC has detected an error in a snap-in' and provides options to either continue with the error or send a report to microsoft ,   Please help regarding this compatibility issue   Thanks in advance !
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend