|
Hi All
How can i get list of database in oracle(Like mysql "Show databases" command)?Plz help me
|
|
|
|
|
Did you think to ask Google[^]before posting, it should be one of your first resources!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Oracle is quite different to MSSql in that aspect. Databases in Oracle are really quite separated from each other.
So I suggest that you read up on schemas and tablespaces, and eventually database links in Oracle.
|
|
|
|
|
Hello,
I want to write a stored procedure in SQL wich returns last 12 weeks list (including current week) with start date.
e.g.
01: 04 Jan 10
53: 28 Dec 09
52: 21 Dec 09
51: 14 Dec 09
:
:
P.S. My first day of week is Monday so first week of year 2010 will start from 4 Jan 2010 and week starting from 28 Dec will be wk 53.
I am doing something like this
ALTER PROCEDURE [dbo].[GetWeekNumberList]
@IncludeCurrentWeek BIT = NULL
AS
BEGIN
SET DATEFIRST 1;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE #tempWeekList(weekDate DATETIME, weekLabel VARCHAR(25))
DECLARE @Counter INT,
@StartDate DATETIME,
@EndDate DATETIME
IF @IncludeCurrentWeek IS NOT NULL AND @IncludeCurrentWeek = 1
BEGIN
SET @StartDate = DATEADD(dd, (DATEPART(dw, GETDATE()) * -1) + 1, GETDATE())
END
ELSE
BEGIN
SET @StartDate = DATEADD(dd, (DATEPART(dw, DATEADD(dd, -7, GETDATE())) * -1) + 1, DATEADD(dd, -7, GETDATE()))
END
SET @EndDate = DATEADD(dd, 4, @StartDate)
SET @Counter = 1
WHILE @Counter <= 12
BEGIN
INSERT INTO #tempWeekList
VALUES (@EndDate, CAST(DATEPART(wk, @EndDate) AS VARCHAR) + ': ' + CONVERT(VARCHAR, @StartDate, 6))
SET @StartDate = DATEADD(dd, -7, @StartDate)
SET @EndDate = DATEADD(dd, 4, @StartDate)
SET @Counter = @Counter + 1
END
SELECT * FROM #tempWeekList
DROP TABLE #tempWeekList
END
I am not able to get the 53 as last week. How to do it?
Any idea or help will be very useful to me.
Thanks.
|
|
|
|
|
Not all years have a week 53, only in the 1-1 in that weeks is thursday or later
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Hi, I have a table with 1000 stock tickers and their historical prices. Today I was trying to write a simple query to retrieve all the stocks with 11/20/2009 prices greater than their 11/17/2009 prices. However my query returned an error saying, "Subquery returned more than 1 value, this is not permitted when the subquery follows .....".
I think I know what the problem is, however I am not sure how to fix this. Is there an alternative query I can write to get around this?
Thanks!!
Below's my current query:
modified on Monday, January 11, 2010 10:52 AM
|
|
|
|
|
You might be having something like this:
select columns from tablename where somecolumn = (select someColumn1 from sometableone)
Now, that subquery is returning multiple values and hence "=" won't work. Replace "=" by "in" or check the sub query if it should return only one value.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Thanks Danish, I've posted in a reply thread below. The operator I am using is a ">" instead of a "=", so using "IN" probably won't work for me. Could you please let me know how I can get around this problem?
I'm not really sure why subquery would still return more than 1 result, since I've specified that Table.Ticker = t2.Ticker and Date = a specific date in the subquery, thus it should only return the price on that specific date for that specific stock....
Thanks in advance!
|
|
|
|
|
Show us what you have and someone will help.
Bob
Ashfield Consultants Ltd
Proud to be a Code Project MVP
|
|
|
|
|
Last night I had some trouble pasting the codes properly in the forum, fortunately the problem is fixed now. Yes I currently have:
SELECT Ticker FROM Table WHERE
Date = '11/20/2009' AND
Price > (Select Price from Table WHERE Date = '11/17/2009')
|
|
|
|
|
You can use a join similar to this one:
select a.ticker from table as a inner join table as b
on a.ticker=b.ticker and a.price>b.price
where a.date='11/20/2009' and b.date='11/17/2009'
group by a.ticker;
|
|
|
|
|
Thank you Dxlee, not only that it works, I learned a new way of using inner join table! I used to think inner join is only for joining two different tables, but now I see that you can use it to link to the same table filtered for some criterias. This is going to change the way I write queries from now on
Could you please recommend a good online source where I can learn how to use SQL more effectively? Using INNER JOIN in this manner (seems creative and ingenious to me) was not covered in W3Schools nor in most SQL tutorial websites. Many thanks again!
|
|
|
|
|
I think d@nish's solution is better for your case, even though you found my inner join to be new and helpful to you.
I am sorry I don't have a book or website to recommend to you on SQL. I learned something about SQL by reading an Oracle manual on SQL*Plus (which has lots of very interesting examples) about 20 years ago (in 1990) when I was working on a project for a friend. I moved a few times (to different countries) ever since and lost that manual. It was for an old version of Oracle anyway (which ran on VMS with no graphical user interface.)
The new Oracle manuals seem to have nothing similar to the one I read. Your best bet is to find a good book on SQL.
|
|
|
|
|
You must be having multiple prices for that date. If you need the top record only, use order by and top 1 in you statement.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Thank you D@nish for the tip, yes indeed there are multiple prices on same dates belonging to a ticker, though that shouldn't happen. Bad data, may need to redownload.
Top 1 solves this for now. Many thanks again.
|
|
|
|
|
You can always you TOP 1.
SELECT TOP 1 mycolumn FROM mytable
This probably will not work for your case but I thought I would throw it in.
|
|
|
|
|
Thank you this also works djj55
|
|
|
|
|
Hi,
What is the query to get the list of table names for MS Access.
In Sql Server :
USE [MyDatabase]
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
In MS Access ?
Thanks in advance.
|
|
|
|
|
SELECT
Table_Name = Name,
Row_Count = DCount("*",[MSysObjects].[Name])
FROM
MSysObjects
WHERE
(Left([Name],1)<>"~")
AND (Left([Name],4) <> "MSys")
AND ([Type] In (1, 4, 6))
ORDER BY
Name
Source can be found here.
I are Troll
|
|
|
|
|
Hi Eddy,
Thanks for your response.
I got one more alternative for that...
OleDbConnection con = new OleDbConnection("Provider=microsoft.jet.oledb.4.0; data source="+Server.MapPath("~/App_Data/Sample.mdb"));
con.Open();
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "TABLE"});
con.Close();
|
|
|
|
|
sekhar.k wrote: I got one more alternative for that...
The .NET version would be preferable over an SQL-statement; it's obvious what the code does if you read your version. It's not that obvious what the query on the MSys tables does.
Well done
I are Troll
|
|
|
|
|
Hi All, I am trying to create a report for every hour based on a selected day.
I am trying to use sql statement. For example, I have dateTimePicker and I select a dy of a month
then it should get all the data that took place from 12am to 11pm for every hour.
FYI : my database sqlite. coding in C#
thanks for any help in advance.
|
|
|
|
|
And what is the problem?
|
|
|
|
|
Thanks Dmitry, I am not an expert in sql. therefore, I don't know how to do that.
FYI: TableOne has Column Sell, quantity, emID,orderDate,shipDate.
I want to know how many sell was made on a given day. And how many sell was made per hour in a gaven day(24 hours).
|
|
|
|
|
You should create a query with condition, like this:
select count(*) from TaleOne where shipDate between <start_date> and <end_date>
You can google and find how to use queries in sqlite, there are plenty of such info.
|
|
|
|