|
I would as a default try and avoid cursors, they really are a last resort tool.
Have you tried UNION or UNION ALL on the 2 queries.
Select This, null That
from SomeTable
Union
Select This, That
from SomeTable
left join ThatTable on ...
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear you try.......
you can reduce your null selection by is null clause.
you can use union or union all are two method combine multiple results set.
select 101
union
select null
union
select 102
union
select null
union all result will be as...
null,
null
101
102
101
102
create table temp table
insert into @temptable(ID)
select 101
union
select null
union
select 102
union
select null
select * from @temptable where id is not null
.........................
try this......
|
|
|
|
|
Hello,
I am trying to return the values of the column names from a table in an Oracle 10g database. I have Googled this "ad-nausium" and have found some code which seems to be most preferred:
select column_name from all_tab_columns where table_name = 'TABLE_NAME' order by column_id"
However, this returns the Column Header Stamp "Column Name" for me instead, and I have seen others reply with this same issue; I have not yet found anyone that can explain or follow this up with a correction. I certainly can not figure it out. The complete context code is below as follows:
listViewColumns.Items.Clear();
try
{
OleDbDataAdapter da = new OleDbDataAdapter(Sql, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (object col in dt.Columns)
{
listViewColumns.Items.Add(col.ToString());
}
}
For background, the program is a C# 4.0 Framwork application and I am using an OLEDB provider for Oracle. Some assistance would be greatly appreciated. Thank You, Pat
|
|
|
|
|
Hi,
I don't know for Oracle, however I did similar things with OLEDB for Access, basically it consisted of:
- opening a connection
- creating an OleDbCommand with the appropriate SQL, probably SELECT * FROM tableName
- getting a OleDbDataReader by executing OleDbCommand.ExecuteReader (CommandBehavior.SchemaOnly should suffice);
- getting the table's schema from OleDbDataReader.GetSchemaTable.
- cleaning up.
The schema itself is a DataTable that holds all the field information. You'll have to work out the details and try it on Oracle.
|
|
|
|
|
Thank you for your input Luc. I recall that you have supplied me with some other solutions in times past and I appreciate your input. I will try your scripting later or tomorrow as time permits and post it here if it works. Thanks again...Pat
|
|
|
|
|
You're welcome.
In the mean time I found a matching code snippet:
using (OleDbConnection con=new OleDbConnection(connectionString)) {
con.Open();
using (OleDbCommand cmd=new OleDbCommand("SELECT * FROM ["+tableName+"]", con)) {
DbDataReader rdr=cmd.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable schema=rdr.GetSchemaTable();
foreach (DataColumn col in schema.Columns) log("colName="+col.ColumnName);
}
}
|
|
|
|
|
Luc,
A minute of my time to thank you for your time. I received this solution (below) from Mr Holmes that is short and sweet. I am sure that if I had a better understanding of the "schema" calls, this would have worked as well, but when I ran it, it placed all the schema headings into the listview. I am sure there is some small argument that would fix it, and I should look nto it as this might help with some other calls. Thank you for your help...I hope that you will answer my 'other issues' as they come up. I appreciate your time and skill...Pat
|
|
|
|
|
I have the script in the office that gets the table names of tables and views - if you don't have an answer I'll post it tomorrow.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank You RAH,
I am going to try Luc's Schema solution later and see if it works, but I would still like to have a look at your proven solution. A post tomorrow, as well as your reply today, is much appreciated. Thank You, Pat
|
|
|
|
|
Not sure if this is relevant anymore but here is the code that gets the tables and views from an oracle database. DBOpsO creates a valid oracle connection and executes a sql string to return a datatable.
StringBuilder sSQL = new StringBuilder();
TableDB oTableDB;
sSQL.AppendLine("select T.owner,T.table_name, NVL (V.HasView, 0)HasView");
sSQL.AppendLine("From SYS.ALL_TABLES T ");
sSQL.AppendLine("left join (select owner,view_name, 1 HasView from SYS.ALL_VIEWS where owner in ('SchemaName1','SchemaName2')) V on upper(V.View_Name) = 'VW' || T.table_name ");
sSQL.AppendFormat("and V.owner = T.owner").AppendLine();
sSQL.AppendFormat("where T.owner in ({0})", sSchema.ToString()).AppendLine();
sSQL.AppendLine("order by table_name");
if (oDBOpsO == null || oDBOpsO.Creds.Database != oDB.DatabaseName)
{
oDBOpsO = clsMain.GetDBOpsO(oDB.Server.ServerName, oDB.DatabaseName);
}
DataTable dtData = oDBOpsO.GetTableSQL(sSQL.ToString());
TableDB oTable = new TableDB();
List<TableDB> lTbl = new List<TableDB>();
foreach (DataRow orow in dtData.Rows)
Note I have a convention that all views are prefixed with 'vw'
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Another trouble: if there are more than one table visible with thesame name (in other schemas for instance) you got mixed output from those tables. Try use user_tab_columns instead or add next condition to WHERE clause
AND owner = 'myowner'
where 'myowner' is owner name.
---
AW
|
|
|
|
|
I think I found out your problem. Your select statement is correct and should return the names of all columns in your table. You can verify this by running the query in a client tool like SQL * Plus or TOAD.
It's how you bind the data table to the ListView that makes the difference. Instead of querying the DataTable's columns, you should query its rows because the column information is returned as rows.
Try this:
foreach (DataRow row dt.Rows) {
listViewColumns.Items.Add(row[0].ToString());
}
modified on Sunday, August 28, 2011 6:22 AM
|
|
|
|
|
YES YES YES...Thank you, that did it! I really need to have a better understanding of what is really going on in these ADO calls. Now I see the column information better since you explained that the columns are returned as rows. I have posted my final code below for your perusal and for the many others that I have accidentally found while trying to resolve this. Thanks again...Pat
string Sql = "select column_name from all_tab_columns where table_name = '" + sSelectedTable + "' order by column_id";
OleDbCommand Comm = new OleDbCommand(Sql, Conn);
listViewColumns.Items.Clear();
try
{
OleDbDataAdapter da = new OleDbDataAdapter(Sql, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
listViewColumns.Items.Add(row[0].ToString());
}
}
|
|
|
|
|
As Luc said, use a DataReader; it always* works, no matter the database system**, and you can use it for queries as well as tables.
Plus it can tell you the proper .net datatype that maps to the table column.
* Well, I haven't yet seen a situation in which it doesn't.
** I haven't tried it with XML, but I don't consider that a database system.
I suppose, if this doesn't work, then the target must not be a database system.
|
|
|
|
|
Thank you...I have worked with Luc before and he has helped me a lot, but the solution that worked perfectly this time is posted above by Mr Holmes. I thought you might want to note it for future use. Thank you for your input..Best Regards, Pat
|
|
|
|
|
PDTUM wrote: Mr Holmes Shameel FTFY
|
|
|
|
|
Mr Shameel,
PLEASE accept my apology...sometimes I write before I think. MANY thanks for the great and simple solution. I hope that you will want to respond to me the next time (and I am sure there will be one)...Best Regards, Pat
|
|
|
|
|
PDTUM wrote: PLEASE accept my apology...
no problem.
PDTUM wrote: I hope that you will want to respond to me the next time
Sure, why not?
|
|
|
|
|
No, I most certainly do not; I very much prefer a database-agnostic solution when one is available and especially when such is better (more flexible, more informative) than the database-specific one.
|
|
|
|
|
Hi I have table Table1 contains SerialNumber, Name, City ,State
SerialNumber, Name, City , State
1 ABC Dallas TX
2 BCD VCDFV NY
3 ABC Dallas TX
Here I want to get the max of the serialnumber with if same combination of Name, City ,State is repeated , here 1 and 3 repeted , Please suggest how can I do this
Thanks,
|
|
|
|
|
select max(serialnumber), Name, City, State
from YourTable
group by Name, City, State
That's a start.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Building on Chris's post:
If you need only max by state you might try something like:
select yourtable.serialnumber, yourtable.name, yourtable.city, yourtable.state
from yourtable
Inner join (
select Max(serialnumber), state
from yourtable
group by state) as sub1
on yourtable.serialnumber = sub1.serialnumber
(NOT TESTED)
|
|
|
|
|
I currently have a table in SQL 2005 with two columns, one for Date, and one for Datetime. These are stored as nominal values, and is defined as the number of days since 12/30/1899, i.e. NOM_DATE 0 = 12/30/1899. Moment is defined as the number of minutes since 12/30/1899 12:00am (GMT), i.e. MOMENT 0 = 12/30/1899 12:00am.
My question is how would I go ahead and convert these into actual dates like dd/mm/yy,using a stored procedure?
Answer Found -
Using DATEADD(days, Date, '18991230') worked.
modified on Thursday, August 25, 2011 10:24 AM
|
|
|
|
|
Since you have already found the solution, why not post it as an answer and mark it as answered?
|
|
|
|
|
You can only do that on the QA forums and not the general programming forums
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|