|
i Have Self join Table in Sql server Database(accounting database)
this is table columns(Accounts Table)
AccountID
ParentAccountID
AccountNo
AccountName
and i have another table (Accounts Balance Table)
AccountBalanceID
AccountID
FirstBalanceCredit
FirstBalanceDebit
TotalCredit
TotalDebit
in accountbalance i add balance for accounts in last level which is not parent for any other accounts
so my question is how to get sum of debit or credit in any level
for example if i have accountx which is parent so it doesn't have any rows in accountbalance because it's not last level i need to get total debit for that account which is the total accounts for all accounts under him which may not be the last level thanks for any help
md_refay
|
|
|
|
|
with Account(AccountID)
As
(
select AccountID from Accounts A where AccountID = #AcountNumber#
union all
select A.AccountID from Accounts A, Account B
where A.ParentAccountID = B.AccountID
)
SELECT sum(totaldebit) TotalDebit FROM Account, AccountsBalance where
Account.AccountID = AccountsBalance.AccountID
replace #AcountNumber# with your account number (probably use variable/parameter).
Hope this will help you
|
|
|
|
|
In the stored procedure,
I have a UDT table which have one column.
and also a UDF(multi valued) that returns five columns , this UDF have one parameter of string type
How can i call that UDF(multi valued) in the select statement ?
like following mentioned.
Select from tablename T
Inner Join UDF(column) F on F.id = T.ID
The ID is common column in both tables and also i want to create join between them
|
|
|
|
|
Hi,
My requirement is, i have to update a column with replace funtionality
eg., column1
-------
this is columnA
columnA is here
after update,
column1
-------
this is columnB
columnB is here
i have to update only A with B...
Any idea or code samples will be very much appreciated.
|
|
|
|
|
I think I may have misunderstood (esp. due to reference to ColumnB), but replace will do it:
SQL> select C from dave;
C
--------------------------------------------------------------------------------
this is columnA
columnA is here
SQL> update dave set c = replace(C, 'columnA', 'NewText');
2 rows updated.
SQL> select C from dave;
C
--------------------------------------------------------------------------------
this is NewText
NewText is here
SQL>
Dave
----
Data Exploration and Data Profiling Tools[^]
|
|
|
|
|
padmanabhan N wrote: i have to update only A with B...
If that`s the case then simply update columnA with columnB
eg:
update mytable set mytable.columnA=mytable.columnB where .....
When you fail to plan, you are planning to fail.
|
|
|
|
|
I have a simple problem with a not-so-simple query (for me) to write.
I have table A with has a single related table B.
The relationship between A and B is a 1 : many
I need a query the selects all rows in A that have nothing in B. I tried doing a query using Count(1) from B but I just cannot figure out the correct syntax to get a working query.
Appreciate it if the SQL Gurus can help.
Thanks
|
|
|
|
|
Not sure exactly what you're trying to do but perhaps something of this form could help?
SELECT * FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE A.x=B.y)
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Your query did the job. Thank you.
As to "what I'm trying to do" I have one customer group creating the entries in table A.
I have a second ops group out on the floor that creates the entries in table B.
The second group needs to know all setups that have not been setup on the floor. Thus the query.
|
|
|
|
|
Glad it worked. I've done similar queries to find out where data, etc. is missing.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
See this[^] thread from yesterday.
|
|
|
|
|
|
Hi,
I'm using SQL Server 2005.
I have the following SQL which is supposed to pad an integer value with leading zeros. The problem is that it prints 0 instead of 001
DECLARE @UserID INT
SET @UserID = 1
DECLARE @UtopiaOrderNumber NVARCHAR
SET @UtopiaOrderNumber = (SELECT RIGHT(REPLICATE('0', 3) + CONVERT(NVARCHAR(3), @UserID), 3))
PRINT @UtopiaOrderNumber -- prints 0 instead of 001
The following SQL does select the correct value:
DECLARE @UserID INT
SET @UserID = 1
SELECT RIGHT(REPLICATE('0', 3) + CONVERT(NVARCHAR(3), @UserID), 3) -- selects 001 as expected
Can anyone let me know what I'm doing wrong?
Thanks,
dlarkin77
|
|
|
|
|
You used a default for NVARCHAR the command should be
DECLARE @UtopiaOrderNumber NVARCHAR(3)
That will give you the output you expect.
|
|
|
|
|
That sorted it out alright. Thanks very much.
|
|
|
|
|
Hi all
I'm experiencing troubles with the code from this site.
Which parameter is exspected instead of:"master..sysdatabases" in the following sql-command:Dim mySQL As String = _
"IF EXISTS (" & _
"SELECT * " & _
"FROM master..sysdatabases " & _
"WHERE Name = 'MyDatabase')" & vbCrLf & _
"DROP DATABASE MyDatabase " & vbCrLf & _
"CREATE DATABASE MyDatabase "
Thanks,
Lucchingolo
|
|
|
|
|
What database engine are you using?
|
|
|
|
|
I'm using Microsoft SQL Server 2008
|
|
|
|
|
Okay, by right clicking on a database in SSMS you can select "Script Database as" then select "DROP to" you can then find that you are looking for sys.databases
As this looks like .NET code you may not have access to SSMS so here is an T-SQL example:
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDatabase')
DROP DATABASE [MyDatabase]
|
|
|
|
|
Sorry, still get an error.
Maybe i misundertood you.
Fact is that i still get an error.
What i did is using your sql-command as string for my SQL-command.
What I mean is this:
In the Form1_Load-Event of my Application(project).
MyConnection = "Server=localhost;" & "DataBase=;" & "Integrated Security=SSPI"
'Your line of code
Dim mySQL As String = "IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDatabase')DROP DATABASE [MyDatabase]"
Try
Dim conn As New SqlConnection(MyConnection)
Dim myCmd As New SqlCommand(mySQL, conn)
conn.Open()
myCmd.ExecuteNonQuery()
conn.Close()
.
.
.
Last but not least, don't forget that the point is not only to "drop" a Database but i'm trying so hard to create a SQL-Database.
thank you,
Lucchingolo
|
|
|
|
|
You will need a semicolon before the drop. If you are not dropping a database I would suggest not even coding the if - drop.
I have not programmed this at the .NET end. You may need to go to the VS forum.
|
|
|
|
|
Thank your, I will give it a try in the Visual Basic forum.
I could not find a VS-forum. Guess you meant the VB-forum.
|
|
|
|
|
Hi,
I am using LINQ to SQL and MVVM pattern in my application where i am retrieving
my data by the following query:
internal ObservableCollection<INVCategory> GetCategoryList()
{
DataLoadOptions dataLoadOptions = new DataLoadOptions();
dataLoadOptions.LoadWith<INVCategory>(t => t.INVSubCategories);
this.Context.LoadOptions = dataLoadOptions;
var categories = from category in this.Context.INVCategories
orderby category.CatgeoryId descending
select category;
return new ObservableCollection<INVCategory>(categories.ToList());
}
And my XAML code for parent(Category) combo is:
<ComboBox Grid.Column="1" Grid.Row="0" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Margin="0,2,10,2" TabIndex="0" x:Name="categoryComboBox" ItemsSource="{Binding CategoryList}" IsEditable="True" DisplayMemberPath="CategoryName" SelectedValuePath="CatgeoryId" SelectedItem="{Binding CategoryList, Mode=TwoWay}" SelectedValue="{Binding Path=CurrentEntity.CategoryId, Mode=TwoWay}">
</ComboBox>
for child(Subcategory) combo i am using:
<ComboBox Grid.Column="1" Grid.Row="1" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Margin="0,2,10,2" TabIndex="1" ItemsSource="{Binding SelectedItem, ElementName=categoryComboBox, Mode=OneWay}" DisplayMemberPath="SubCategoryName" SelectedValuePath="SubCategoryId" SelectedItem="{Binding INVSubCategories, Mode=TwoWay}" >
</ComboBox>
But my child combo items is not populated during form loading as well as parent combo's
selection changed though my parent combo items are populated.
i can't figure out why my child combo is not functioning based on parent combo's
selected item ,please help me.
|
|
|
|
|
I think you are more likely to get an answer posting this on the WPF forum instead of the SQL forum.
But I experienced basically the same thing and finally wrote my own code the old-fashioned way to populate the child table. Though, looking at your XAML you've pretty much setup a logical binding between the two.
Good luck.
|
|
|
|
|
I was sitting out on my porch having lunch when I realized what your problem may be.
I remember there was a property (don't know it right now .. you'll have to search for the obvious in your XAML) which was basically binding to an event to tell the control when to rebind it's data. You need to find that property and bind it to the combobox SelectionChanged event.
|
|
|
|