|
hi all,
this is my oracle StoredProcedure
PROCEDURE PEAP_SelectActiveYear
(
Cur_RCT1 OUT GLOBALPKG.RCT1
)
AS
BEGIN
OPEN Cur_RCT1 FOR
SELECT ActiveYear
FROM PeapParameters;
RETURN;
END;
in the code am trying to fetch the value using ExecuteScalar()
but not retrieving
Anything need to be changed to the procedure?
|
|
|
|
|
I don't use Oracle but try removing the RETURN, this should then get the result set back instead of trying to RETURN any output parameters (of which there are NONE).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello brothers
Just i m designing for helpdesk product.
jsut think about . i have one department
Department(Admin)
.
.
...... SupportTeams ( Support Teams having Support Users)
.
........ SupportUser1
.
........ SupportUser2 and so....
User Team1(Means developer)
,
......User1
.
..... User2
User Team2(Means developer)
,
......User1
.
..... User2
Now my question is i want to give security for this tree structure.
..> Department Admin can access to all Like Support Teams and USer teams.
..> Support Teams can Access to Only Userteams Member.
..> Userteams Member can aceess only his profiles.
Now can u tell me how could i create table for these flow tree structure.
Some Thing need 2 do NEW
|
|
|
|
|
What best you can do is to create one table like
ID DepartMent DepartMentData
and make DepartMentData column as xml type where you will store data in
the hierarchal way.
For eg.
There is an admin department so the DepartMentData column may have values like
<Department Name = "Admin">
------<SupportTeams>
----------<SupportUser Name ="SupportUser1" ID="1">
--------------<DeveloperTeam1>
------------------<User Name ="Dev1User1" ID="1"/>
------------------<User Name ="Dev1User2" ID="2"/>
--------------</DeveloperTeam1>
----------</SupportUser>
----------<SupportUser Name ="SupportUser2" ID="2"/>
--------------<DeveloperTeam2>;
------------------<User Name ="Dev2User1" ID="1"/>
------------------<User Name ="Dev2User2" ID="2"/>
---------------</DeveloperTeam2>
-----------</SupportUser>
------</SupportTeams>
</Department >
Now in C# code you could pass the element you want to read Like for eg. you want to read all the users below SupportTeams then you just need to
call:
XmlNodeList ChilNodes = doc.GetElementsByTagName("SupportTeams")
This will return all the child nodes including SupportTeams node.
Now you can iterate through for each node
foreach (XmlNode cNode in ChilNodes )
{
}
In the loop check that each node has further any child node by calling:
if(cNode.ChilNodes!= null)
This way you could ensure that you have not gone to above in the tree hierarchy.
This will always go to below in the tree hierarchy.
|
|
|
|
|
If you are using SQL 2008 (you don't mention the database) look into the hierarchy ID and it's methods.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi I have created tables with their primary keys as integers. How can I write a statement to Alter the primary key and set the identity (autonumber) on it in SQL Server?
Thanks
|
|
|
|
|
ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (100,1);
Education is not a way to escape poverty — it is a way of fighting it.
|
|
|
|
|
hi all,
ID NAME
1 aaa
2 bbb
3
4 NULL
From this table how to retrieve the records with name <> null or empty using oracle query?
i should get the result like this :-
1 aaa
2 bbb
how can i do this?
|
|
|
|
|
Select ID, NAME from [table name]
where NAME IS NOT NULL
AND NAME != ""
Education is not a way to escape poverty — it is a way of fighting it.
|
|
|
|
|
giving error:-
PL/SQL: ORA-01741: illegal zero-length identifier
|
|
|
|
|
That error because of the following statement
AND NAME != ""
If space is there in the column you can check like this,
Name != " ";
and oracle treats the empty string ('') as null.
refer here[^]
Education is not a way to escape poverty — it is a way of fighting it.
|
|
|
|
|
thanks for ur reply..
its exactly bcz of the space...i gave like <>' ' it worked...
|
|
|
|
|
gt this table address with fields name,housename,post,genderid.i'm taking genderid from another table-gender.Now how to insert something to address table?
|
|
|
|
|
Do you want to insert genderid from gendertable into table address?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
If you are using stored procedure then inside the procedure...
Insert into dbo.tblAddress
Select @name,@housename,@post,genderid from dbo.tblGender where [some condition to get the specific gender] --@name,@housename,@post can be input paramaters to the stored procedure
Alternatively, you can query the gender table to get the gender and store in a variable and then use the same to insert data in the address table.
However the first approach is better...
|
|
|
|
|
Hi,
I need some experienced advise on a situation that i am facing. Please provide help.
I have a products excel sheet. currently 20,000 products but they keep on increasing. i am asked to develop the sql tables. the products can be divided into 2 types. One type have about 30% rows whereas other will have 70%.
In database implementation, i can have one table with type field which separte the 2 types also I can have 2 different tables for both types.
My question is :
Between the two, for searching purposes which proves the best one.
ie. on average 1 table search provides better performance or 2 tables provide better performance.
thankyou
|
|
|
|
|
Hard to say whether you will get better performance with searching 1 large table or 2 smaller tables without knowing the details, but with the number of records you are talking about (less than 100,000) you will be amazed at how fast SQL server can return results from a query compared to searching in Excel.
My suggestion is to store the data in one table, with a "type" idicator. Creating the proper indexes will also improve performance depending on the nature of the query.
Question: Do all of the columns for each product "type" apply ? Another way of asking this question is that if you created 2 tables; one for each product type, would they have the same columns ? If so, then for sure, go with 1 table and an idicator field.
BTW: What type of application will be accessing this data ? ASP.NET or a Windows client application ?
|
|
|
|
|
Thankyou for responding
Do all of the columns for each product "type" apply?
theoretically , both should have same detail. but does the number of columns matter. What i mean is that if product has 12 columns and i split it into two tables.
1. productname
2. productdetail
would select statement make a difference? to my knowledge, its the same thing.
the application would be used by asp.net. Also if u don't mind, it would be nice if u mention whether its wise to use mysql db or sql server db. is sql server n asp.net better, or is sql server or php better, or asp.net n mysql or other way round.
Thanks again.
|
|
|
|
|
Better go with a single table with a bit field for product type.
Maintaining 2 different table for the same identity is not a good dB design practice.
The performance wont be an issue with the size of record that you are saying.
Implement indexes on the columns that you will use in the query conditions.
Now regarding the selection of database, for ASP.Net, better go for MS-SQL Server. As .Net framework provides native driver for MS-SQL Server. This is the managed driver and performance is better.
However, the hosting cost for MS-SQL Server is higher, if budget is a constraint, you can go for mySQL.
|
|
|
|
|
|
You actually need 2 tables
Product
ProductType
for when they add a 3rd product type. As said by others create the product table with all the details and the a foreign key to the product type table.
100k rows is nothing to a database, add 2-3 zeros and you MAY have performance issues to address.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I need to query and do a comparison on a versioned data table with a separate table dedicated to the version specific information.
The versioned data is related to pricing and the comparison is based on the date of service for the product sold. I need to find the correct price based on the version "effective date". However, there may be multiple entries for the selected product that are within the Effective date range. In this case, I need to use the record with the highest (or most recent) Version Date.
My product billing information is housed in one table. My referential pricing data is housed in another table and the version ids and dates are located in another table.
Any ideas?
|
|
|
|
|
We had the same situation with effective pay rates for employees.
I believe the structure of the table was something like this:
EmpID, Rate, EffFromDate, EffToDate
The query would be something like:
Select rate
from ratetable
where EmpId=ID and targetDate => EffFromDate and targetdate <= effTodate
Example, new employee:
123,$10.00,1/1/2000,12/31/2999
Example, pay increase on May 1, 2009
123,$10.00,1/1/2000,4/30/2008
123,$12.25,5/1/2008,12/31/2999
Notice that the most recent record should always have the artificially high "EffTodate" of 12/31/2999.
You could also make the oldest record equal to the employee's hire date instead of some artificial low date of 1/1/2000.
Best of luck.
David
|
|
|
|