|
I have the same database on different computers, with different raw data of any department, but structures and tables are the same. i have no network, i wanna bring these data toghether for reporting. for example I have these DB's:
database 1 : person1 , persson2
database 2 : person3 , persson4
database 3 : person5 , persson6
and I need this for reporting:
General Database: person1 , persson2, person3 , persson4, person5 , persson6
|
|
|
|
|
Same applies; if you had a field called "database", you could drop them all in the same structure.
Small example;
EmplyeeTable
Id Name OtherStuff
1 John Bla
becomes;
EmplyeeTable
Id Name OtherStuff Database
1 John Bla Customer1
That way you could select everything from that customer, even if there's more than one customers data.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Hi,
Can the following be used in a stored procedure?
convert(float, (columnname))like @x
I've tried it. It works.
Wanted to know if it's a good way of programming?
Cheers
Berba
|
|
|
|
|
As you will be converting the value of each item in columnname to float, this is a more expensive operation than converting the value in @x into the appropriate type.
|
|
|
|
|
And then they're converted to string, which is worse. 
|
|
|
|
|
berba wrote: if it's a good
Most likely not; all those conversions will kill you. What are you trying to do? Perhaps you want BETWEEN?
As mentioned, converting the parameter value (once) is much better* than converting all the values in the column.
* Anecdote: I once inherited maintenance of a batch program that took forty minutes to run; after fixing the conversions it took only ten minutes.
|
|
|
|
|
Hi,
Can any one tell me how to run a job in oracle which take all the data from one table(for ex: name,address,lastlogintime,etc) and generate output in excel format and save in one common folder everyday.
If you have a sample code, could you share it please, it would be helpfull in understanding the concept better.
|
|
|
|
|
What part of the concept is troubling you?
Write a console-app that executes a query on your Oracle-database. There's quite some examples on the internet that show you how to achieve that part. Next, iterate through the results, and write them out to a file. Excel will accept almost everything, from CSV to XML. We got some nice articles on CP that explain how to write to Excel in various ways.
Not much to concept there. If you have the first part of the assignment, we'll talk about writing those results to Excel.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Basic Steps
1. Create/find scheduler
2. Create SQL that gets the data
3. Create code the outputs the data
4. Put 1-3 together.
Steps 1-3 are independent. They do NOT have anything to do with each other.
Step 3 is the only one specific to Excel.
|
|
|
|
|
Hi I have a problem writing an query with select parameter as input paramter in the stored procedure.
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID int
AS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where ID = @ID
COMMIT TRANSACTION
Is it possible something like this with select paramter also included in input paramter to the stored procedure?
Thanks in advance,
Dhyanga
|
|
|
|
|
Why don't you try along these lines:
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@FieldName string,
@FieldValue int
AS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where @FieldName = @FieldValue
COMMIT TRANSACTION
[EDIT: this doesn't solve it. The message by Michael Potter holds the answer.[/EDIT]
modified 29-Jun-12 9:41am.
|
|
|
|
|
Thanks for the reply but it didn't work out. I am restating my problem with following table.
I have one table named ItemList like this:
ID Name Price($)
1 Bag 20
2 Fabrics 35
3 Tools 100
now my query was
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID int
AS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where ID = @ID
COMMIT TRANSACTION
I need output something like this. If I have @ID = 1 and @ColName = 'Name', then my output should be
Name
-----
Bag
I tried my way that i posted earlier and your way, but it didn't work out. It gave output like this instead.
Column1
--------
Name
Name
Is there any other way or am I doing any wrong in my query?
Please help.
suchita
|
|
|
|
|
You'd need to introduce the output-keyword to one of the parameters, as described in the documentation[^].
Your sproc is a wrapper around a simple statement, adding complexity without any benefits. The design, the approach, it's wrong. There's nothing to "commit" to the database, and a simple select-statement (with parameters) would be sufficient.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy, I will take that commit out from my query. I mistakenly forgot to take that out. But my question is still the same. I don't know whelther we can send fieldname as input parameter to get the value of that field.I know it looks very weird for that small table but I have to use the same concept for my huge database system. I thought that table would be easy for me to explain what my output should look like. I am going through the documentation you sent me but at glance, i couldn't see what I am looking for but I am going through it line by line. Thanks for your time .
suchita
|
|
|
|
|
Dhyanga wrote: I don't know whelther we can send fieldname as input parameter to get the value of that field.
You could join on the system-tables, but to what use? You'll be concatenating constants in a way that can hardly be considered helpfull. If writing Sql is that much of a problem, consider a ORM-framework.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I had got the required output simply writing the sql query but I wanted it on stored procedure. I know i can do it something like this in my query.
string colname;
int ID;
string query;
.
.
.
query = "select " + colname + " from ItemList where ID = " + ID ;
SqlCommand cmd = new SqlCommand(query, sqlconn);
.
.
.
.
This had solved my problem but I wanted it on stored procedure. and I was stuck giving the column name itself as input parameter.
suchita
|
|
|
|
|
I can only point to jschells' comment 
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy for your time and views.
suchita
|
|
|
|
|
Dhyanga wrote: I don't know whelther we can send fieldname as input parameter
You can't.
Your choices are
1. Create the SQL dynamically in C# and then execute it.
2. Create the SQL dynamically in SQL and then execute it in SQL.
3. Use a fixed set of fixed SQL statements and select one based on the data passed in.
|
|
|
|
|
Thanks Jschell.. Just curious if that can be possible. If not, i can just execute it without using stored procedure. I was trying to use better way than that if I could...
suchita
|
|
|
|
|
Dhyanga wrote: i can just execute it without using stored procedure
My solutions work with or without stored procs.
|
|
|
|
|
Hi,
try like this..
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID int
AS
BEGIN TRANSACTION
SELECT @ColName = ColumnName FROM ItemList where ID = @ID
COMMIT TRANSACTION
Karthik Harve
|
|
|
|
|
Am I missing something, you need dynamic SQL to meet this requirement, you solution does not work!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You have to use sp_executesql and construct the query string yourself. Do realize that you can open yourself up to SQL injection attacks if you don't properly test the variables.
CREATE PROCEDURE SelectItemList
(
@ColName string,
@ID int
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql = N'SELECT ' + @ColName + N' FROM ItemList where ID = ' + CAST(@ID AS VARCHAR(10))
EXEC sp_executesql @sql
|
|
|
|
|
Thank you Micheal. That's what I wanted but we need to give the @ColName nvarchar size too. It worked fantastic.
suchita
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
