|
You don't use a stored procedure like a table. You execute it rather than select from it. the stored procedure itself may return some records (but doesn't have to, some only create or modify data or structure), but the SP is not itself a valid target for the 'FROM' clause of a select statement.
Your SQL is not valid in ay case, as only one FROM would be expected (possibly multiple tables after the "From" )
You're trying to select results from combinations of results...that won't work.
|
|
|
|
|
I want to change following sql statement to using inner join but I dont know how to join on two column on the two table. Thanks for helping.
select *
from Persons A, Banks B
where B.Student_ID = A.Student_ID
AND B.class_NO = A.claa_NO
when I change to the following statement, I get an error on the syntax. Incorrect syntax near the keyword 'ON'.
select *
from Persons A
INNER JOIN B
ON B.Student_ID = A.Student_ID
ON B.class_NO = A.claa_NO
Please kindly help. Thanks
|
|
|
|
|
Try
SELECT *
FROM PERSONS A
INNER JOIN BANK B
ON B.STUDENT_ID = A.STUDENT_ID
AND B.CLASS_NO = A.CLASS_NO
|
|
|
|
|
It works!!! Thank you very much
|
|
|
|
|
You are not defining B as an alias.
|
|
|
|
|
Hi I am having a problem returning a value from a stored procedure...It keeps returning Nothing...here is the stored procedure:
'===============================================
ALTER PROCEDURE dbo.spAddUser(@Name VARCHAR(50),
@LoginName VARCHAR(50),
@LoginPass VARCHAR(50),
@SecLvl INT)
AS
SET NOCOUNT ON
INSERT INTO dbo.tblUser([Name],
LoginName,
LoginPass,
SecLvl)
VALUES(@Name,
@LoginName,
@LoginPass,
@SecLvl)
RETURN @@IDENTITY
'===============================================
And here is the code I am using to call it:
'===============================================
Public Shared Function ExecuteScalarStoredProc(ByVal conStr As String, ByVal procName As String, ByVal procParams As String) As Object
Dim ret As Object
With New SqlClient.SqlConnection(conStr).CreateCommand
.CommandText = "EXEC " & procName & " " & procParams
.Connection.Open()
ret = .ExecuteScalar
.Connection.Close()
End With
Return ret
End Function
'===============================================
Any sugestions?
Pablo
Sometimes I think there's no reason to get out of bed . . . then I feel wet, and I realize there is.
|
|
|
|
|
hi there
well I'm not the greatest in these things but I think the problem lies with ur procedure bcz u dont return values from it... I think u should use OUTPUT parameters in this. then atthe time u call the procedure u dont provide any value for those but u receive the value from it after the stored proc is called. u need to chg the definition of ur stored proc and declare an output variable @Identity as
@Identity INT OUT[PUT]
and in the end dont use RETURN use set @Identity = @@Identity
Go ahead and try it out, I'm sure it'll solve ur problem though but do let me know
Imran Akram.
|
|
|
|
|
Thanks...this is what I ended up doing to get it to work:
ALTER PROCEDURE dbo.spAddUser(@Name VARCHAR(50),
@LoginName VARCHAR(50),
@LoginPass VARCHAR(50),
@SecLvl INT)
AS
SET NOCOUNT ON
INSERT INTO dbo.tblUser([Name],
LoginName,
LoginPass,
SecLvl)
VALUES(@Name,
@LoginName,
@LoginPass,
@SecLvl)
SELECT IDENT_CURRENT('tblUser')
Pablo
Sometimes I think there's no reason to get out of bed . . . then I feel wet, and I realize there is.
|
|
|
|
|
|
I think my brain fell on the flore for a few minutes LOL, thanks for the help! 
Pablo
Sometimes I think there's no reason to get out of bed . . . then I feel wet, and I realize there is.
|
|
|
|
|
I want to do a query by Select Statement on date BUT
the problem that I want to make this query at 5 days before the date in the table.
For Ex,
I have date 6-1-2006 {with format(dd-MM-yyyy)} THEN,
I want when the query works give me a message before 5 days from this date, THAT MEANS
the query will give me the message at 1-1-2006.
I realy wanna do that,
and maney thanks for help...........
soly
|
|
|
|
|
well I dontknow exactly what ur trying to do but
think abt using this
now()// the function for todays date
if now()+5 = <the date="" from="" database="">print (or whatever) "hi there"
|
|
|
|
|
If you tell us what this is being used for then it might help with the answer. I can think of 2 or 3 ways, but they all have limitations which are acceptable in different scenarios.
|
|
|
|
|
Good day everyone,
I need to update a record into the database Access via table adapter in VB.Net 2005.
The dataset is updated, but the record in the database Access is not.
Could anyone know how to fix this problem?
Please help.I'd appreciate it so much.
Thanks
(Student_Details:table name)
MyDataSet.Student_Details.Rows(2).Item(3) = "new password"
MyDataSet.GetChanges(DataRowState.Modified)
Student_DetailsTableAdapter1.Update(MyDataSet)
MyDataSet.AcceptChanges()
Me.Validate()
Me.Student_DetailsBindingSource.EndEdit()
Me.Student_DetailsTableAdapter1.Update(Me.MyDataSet.Student_Details)
-- modified at 11:11 Saturday 14th October, 2006
eric
|
|
|
|
|
i have DB record named details and excel sheetwhich contains various column(water press,sensitiv press,etc)with values(float with various decimal length..1.12,1.123,1.1232...) that should not be changed as they r standards
.i tried to transform using data transform ,when i transformed the values got added or rounded off as i mentioned float datatype...so how to transform datas
Dhatchinamoorthy.R
9894824838
|
|
|
|
|
Hi,
does anybody knows how to gather the tables and columns of tables in oracle database by SQL Query?
in MySQL, I can gather that informations just to pass a SQL query like "show tables" and get the list of the tables in the certain database as a result.
or I can pass a Query "show databases" and as a result, I get the list of the databases.
but how can I do it in oracle system?
cheers...
|
|
|
|
|
u can try out
select * from tab;
rahul
|
|
|
|
|
thanks, it works!!
maybe you know also how to get the name of the columns in the table?
i try with "describe" command, but seems that oracle doesn't work the way like the MySQL
cheers...
|
|
|
|
|
u can try
select desc from table name;
rahul
|
|
|
|
|
u can try
desc table_name
|
|
|
|
|
I found this:
select table_name, column_name from all_tab_columns where column_name like '%X%'
I am sure there are system tables in Oracle to use to get this done.
SimpleMusings from a Simple Girl
|
|
|
|
|
Hi there!
I have a question regarding the structure of a database. I'm currently designing af table that will end up with around 50 columns of different kinds. Now my question is: Would it be better to split the table into smaller tables?
I have tried this but I keep running into integrity problems, because the database server will have to delete rows in other tables as well.
To clear things up I will give an example:
Consider a factory. The factory pollutes. In order to keep track of the pollution, the factory owner has to fill in some 20 values of different kinds. Normally you would place these values on the factory table, but since there are so many of them, you might be able to put the pollution data in another table called PollutionData. But somehow I can't get this to work properbly. I have the factory referencing the PollutionId in the PollutionData table.
A cascade delete will only work the other way around! Whenever someone deletes the pollution data, the factory is deleted. Which of course is wrong. I want the pollution data deleted if the factory is deleted. And I want the database server to produce an error if someone tries to delete pollution data referenced by a factory.
As I see it - the only way to solve this is to put the pollution columns into the Factories table, but I have this kind "oh noes - this is wrong"-feeling - and that's just because there are so many columns.
Any suggestions would be greatly appreciated!
Best regards
Soeren
|
|
|
|
|
Bad Robot wrote: Would it be better to split the table into smaller tables?
Does the data suggest you should do that?
Bad Robot wrote: Consider a factory. The factory pollutes. In order to keep track of the pollution, the factory owner has to fill in some 20 values of different kinds. Normally you would place these values on the factory table, but since there are so many of them, you might be able to put the pollution data in another table called PollutionData. But somehow I can't get this to work properbly. I have the factory referencing the PollutionId in the PollutionData table.
Is your polution data of the kind like:
COPollution1
CO2Pollution1
SO2Pollution1
COPollution2
CO2Pollution2
SO2Pollution2 etc.
If so then that is an obvious candidate for a one-to-many join as there are repeating fields (with an index marker in the name)
If you have 20 non-repeating columns, are they (as a block) optional? In otherwords: Can a factory exist without these values?
From what you've said already, regardless of the details of how the data relates, it is obvious that the Factory is the parent. A factory pollutes, without the factory you don't need the pollution data. Therefore, the PollutionData should reference the Factory table. The PollutionData table should have a FactoryId column.
Bad Robot wrote: A cascade delete will only work the other way around!
Well, put it the other way around. From your description, it seems that is the correct way to go.
Bad Robot wrote: And I want the database server to produce an error if someone tries to delete pollution data referenced by a factory.
Difficult one this. If you forbid this, then you cannot delete a factory either. I'd say you enforce this by using only stored procedures to access your data. The stored procs can enforce that rule.
Bad Robot wrote: As I see it - the only way to solve this is to put the pollution columns into the Factories table, but I have this kind "oh noes - this is wrong"-feeling - and that's just because there are so many columns.
If there are no repeating columns, and the data must always exist with a factory then it is not wrong. You can have upto 1024 columns in a table in SQL Server. The limit is that high because there are valid reasons to have that many columns.
Does this help?
|
|
|
|
|
Colin Angus Mackay wrote: If you have 20 non-repeating columns, are they (as a block) optional? In otherwords: Can a factory exist without these values?
There are no repeating columns in pollution data. Factories must come up with values for some of the columns. Some factories needs to fill in all the columns and others do not. It's Depending on the type of pollution and size of the factory.
Colin Angus Mackay wrote: From what you've said already, regardless of the details of how the data relates, it is obvious that the Factory is the parent. A factory pollutes, without the factory you don't need the pollution data. Therefore, the PollutionData should reference the Factory table. The PollutionData table should have a FactoryId column.
If I give the PollutionData table a FactoryId this would give me the opportunity to create more than one row of PollutionData referencing the same factory. This is not good since it could give the clients some trouble finding out which one to use.
Best regards
Soeren
|
|
|
|
|
Bad Robot wrote: If I give the PollutionData table a FactoryId this would give me the opportunity to create more than one row of PollutionData referencing the same factory.
Not if you also make FactoryId in the PollutionData table the primary key. Primary keys must be unique - therefore it will only permit you to insert one PollutionData row per Factory.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
