View Complete Post
I am joining three tables called "SNAP", "TABLE_A" and "TABLE_B" both of these 3 tables are partitioned by DAY_CODE column of type integer.
TABLE_A is about 10 million rows ( about 2 millions per day ), TABLE_B is like 60 million rows ( about 12 million per day ) and SNAP table is just a small table saying what days i have.
So, i am trying to use a query like this:
FROM SNAP S
INNER JOIN TABLE_A A
ON S.DAY_CODE = A.DAY_CODE
INNER JOIN TABLE_B B
ON S.DAY_CODE = B.DAY_CODE
AND A.ID = B.TABLE_A_ID
WHERE S.DAY_CODE = 20100923
Like you can see, i am just using the SNAP table to align the filtering on the partitioned column. The problem is that the partitioned is not selected and somehow all columns of TABLE_B at least are being scanned for what the explain plan tells me. ( It
runs for more than 12 hours and no results ).
If i change the query to:
I have a table "assignments" with records that are assigned
to multiple "users" by the "department" they belong to. Each user then can
have it's own "status" for each assignment.
When the user logs-in i need to show all his assignments and the status.(but status record doesn't exist yet)
When a new assignment is created I don't want to have to create a new record
relating the assignment to the user. I want the relationship to be by the
department the user belongs to and the department the assignment was assigned
Only thing i can think of is when the user logs in to load the assignments
that belong to the user's department then look thru all the records. For each record
loop thru the status table. If a record exist show the the status. if it
doesn't then make default value and when the users clicks to view the assignment a new status record will be added.
Only thing i don't like is looping thru the status records while looping
thru all the assignments.
How else can I do it? I don't see how could join the assignments table and
the status table if the status record doesn't exist yet.
Firstly,Ive created a table "StudentInfo" in Ms Sql .
Secondly,Ive a "ContactList.xls" in Excel 2007 which I had already imported it into MS SQL.
The problem is,how can i join the StudentInfo and ContactList into 1 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:
Hi! I'm creating an application that's supposed to first add a record to table1, and then get the ID from that record to use when adding a record to table2, to be able to associate these two records with eachother.
The user gets to type in some values that goes to table1, and some values that goes to table2, but before the insert statement for table2 is executed i need the ID from the recently added record in table1. Some dude told me to use a trigger for the autopopulate purpose, but does that really work when i also need to save some values that's user input, and when those values doesn't get saved in table1?
Are there any other way to do this or can i send values to a trigger? I'm new to triggers and stored procedures, i don't have any particular knowledge of this, any help is appreciated!
i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset
like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid as a compact data and combination of 3 tables from d single dataset.....
can some1 help me please.....
I have two tables
Trans with fields TransID, Date, CustomerID and some other stuff
Customer with fields CustomerID, Name, TaxId
On the screen the user only sees the fields Date and Customer Name. CustomerID is behind the scenes only.
I'm using SqlDataSource. Having no problems with SelectCommand. I don't know how to construct the UpdateCommand and InsertCommand.
Let's say the user changes the date, then I need to do an UPDATE.
UPDATE Trans SET Date = @Date, CustomerID = @CustomerID results in an error message and the record is not updated.
I get an error on the page that says "Sys.WebForms.PageRequestManagerServerErrorException: Input string was not in correct format".
I tried taking out the set for CustomerID and I still get the error on page.
Also, for inserting, the users will see a dropdownlist with Customer Names. I need to convert that to a CustomerID to be used in the new record being inserted in the database. I'm not sure how to do this.
Do I need to do something with Control Parameters?
Hi! I want to display a crystal report in my vb.net application. Suppose I have tables named student details, student
marks, student address, etc... Now
if I want to display all these details (fields of all tables)
in one crystal report (with page breaks if necessary) then how will I
achieve it. I will be providing a combo box in my application that contains list of student names.
How can I link this combo box with the cystal report to
dynamically display report for different student on selected index
change of combo box? Help me friends. An example would be appreciable.
It's been more than a year since my words last floated across the pages of MSDNÃÂ®Magazine. As readers of my blog probably know, my life's been interesting the past 12 months.
MSDN Magazine January 2005