|
You need this[^].
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]
|
|
|
|
|
Hello,
i am at the beginning of learning SQL, and i cannot find the solution to my problem:
I have got two tables, WORKCENTER and CALENDAR. WORKCENTER contains columns ID and NR_OF_DAYS, CALENDAR Contains columns DAY and IS_WORKING_DAY.
DAY contains the numeric value of a day (for example 14824 for today, 2010-08-03), and IS_WORKING_DAY is CHAR Type and indicates with 'y/n' if it is a working day or not.
I want for each Work Center to get the day from today + NR_OF_DAYS from table CALENDAR, excluding weekends and holidays.
The result should be like this:
ID MAX_CALENDAR_DAY
1 14833
2 14840
...
Explanation:
Work Center 1 has NR_OF_DAYS = 7, today is 14824, + 7 = 14831, but there is a weekend in between, so + 2 = 14833.
Work Center 2 has NR_OF_DAYS = 12, today is 14824, + 12 = 14836, but there are two weekends in between, so + 4 = 14840.
This is my sql query (Oracle):
SELECT WORKCENTER.ID AS WORKCENTER_ID, MAX_CALENDAR_DAY
FROM WORKCENTER,
(SELECT MAX(DAY) AS MAX_CALENDAR_DAY
FROM (SELECT DAY
FROM CALENDAR
WHERE DAY > TRUNC(TO_NUMBER(SYSDATE - TO_DATE('01.01.1970', 'DD.MM.YYYY')))
WHERE CALENDAR.IS_WORKING_DAY= 'y'
ORDER BY CALENDAR.DAY
)
WHERE ROWNUM <= WORKCENTER.NR_OF_DAYS
)
WHERE WORKCENTER.ID IN ( 1, 2, 6, 7, 9)
Oracle SQL Developer shows an error, WORKCENTER.NR_OF_DAYS is unknown. This is obvious, if inner queries are handled first (is this correct?).
Could you tell me how i have to build this query?
I hope you see what i am trying to do...
Greetings
|
|
|
|
|
In your first sub-select, you have MAX(DAY) as MAX_CALENDAR_DAY and you get that data from a further sub-select. Fine and good.
However, on your where clause, you reference, WORKCENTER.NR_OF_DAYS.
The WORKCENTER table was never referenced.
Tim
|
|
|
|
|
Start with a query telling you CALENDAR.DAY for every NR_OF_DAYS and limit that to the maximum number found in the WORKCENTER table, maybe
SELECT ROWNUM as NR_OF_DAYS, DAY AS MAX_CALENDAR_DAY
FROM (SELECT DAY
FROM CALENDAR
WHERE DAY > TRUNC(TO_NUMBER(SYSDATE - TO_DATE('01.01.1970', 'DD.MM.YYYY')))
AND CALENDAR.IS_WORKING_DAY= 'y'
ORDER BY CALENDAR.DAY
)
WHERE ROWNUM <= (SELECT MAX(NR_OF_DAYS) FROM WORKCENTER WHERE WORKCENTER.ID IN ( 1, 2, 6, 7, 9))
and then INNER JOIN that to the WORKCENTER table ON NR_OF_DAYS.
|
|
|
|
|
This is my query now:
SELECT ID,
DAY
FROM (SELECT ROWNUM as CAL_ROWNUM,
DAY AS MAX_CALENDAR_DAY
FROM (SELECT DAY
FROM CALENDAR
WHERE DAY > TRUNC(TO_NUMBER(SYSDATE - TO_DATE('01.01.1970', 'DD.MM.YYYY')))
AND CALENDAR.IS_WORKING_DAY= 'y'
ORDER BY CALENDAR.DAY)
WHERE ROWNUM <= (SELECT MAX(NR_OF_DAYS)
FROM WORKCENTER
WHERE ID IN (1, 2, 6, 7, 9))
)
INNER JOIN WORKCENTER
ON NR_OF_DAYS = CAL_ROWNUM
WHERE ID IN (1, 2, 6, 7, 9)
And it works, yeah! Thanks guys.
Greetings
|
|
|
|
|
Hi all,
I'm running SQL CE on Wince 5.0, and am finding that about every 40 seconds or so that the whole system freezes then carries on. This happens at precisely the time when the timestamp on the .SDF file changes, so I am assuming that it is freezing when SQL is updating the database file. Is there any way to prevent this or specify when it happens as it's interfering with other operations on the board?
TIA!
Edit - Fixed: on-board Flash is far, far too slow.
modified on Tuesday, August 3, 2010 7:56 AM
|
|
|
|
|
I have a table with 5000 rows
I want to get the first 1000 rows in a single select query
and the next 1001 to 5000 rows in another select query.
A help from somebody will be deeply appreciated.
modified on Tuesday, August 3, 2010 11:34 PM
|
|
|
|
|
Which database server and version?
|
|
|
|
|
|
The first one is easy enough.
SELECT TOP 1000 * FROM MYTABLE
The other one could probably be done like this assuming you have a Primary Key MYTABLEID (or some other unique key):
SELECT *
FROM MYTABLE
WHERE MYTABLEID IN (SELECT TOP 5000 MYTABLEID FROM MYTABLE)
AND MYTABLEID NOT IN (SELECT TOP 1000 MYTABLEID FROM MYTABLE)
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Thank you for the valuable information. Yes it worked
|
|
|
|
|
|
Although this is how it used to be done pre 2005, in SQL Server 2005 there was the ROW_NUMBER function which enabled much more efficient paging queries to be written.
|
|
|
|
|
Quite similar to Oracle then, which still lacks the TOP function.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
I Have two Tables like Code and Details...
In Code(SrNo,Name) Table Records Like A,B,C etc
In Details(SrNo,CodeName)Table looks like
SrNo A B C
1 0 1 0
2 0 0 0
3 1 0 0
4 0 0 0
5 0 0 1
6 1 1 1
7 0 0 0
If I add another Name in Code Table Like 'D'. Select query for Details table also contain column 'D'.
So Select query like this
DECLARE @Code NVARCHAR(MAX)
SET @Code = (SELECT ('CONVERT(VARCHAR,'+Name+ ')'''+Name+''',') FROM Code ORDER BY SrNo FOR XML PATH(''))
SET @Code = SUBSTRING(@Code,0,LEN(@Code))
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT '+@Code+' FROM Details'
EXEC (@SQL)
Output:I need to check any '1' in columns and srno in order by without mentioning column names. It has to be like below. How can I do in SqlServer2005
SrNo A B C
6 1 1 1
1 0 1 0
3 1 0 0
5 0 0 1
2 0 0 0
4 0 0 0
7 0 0 0
|
|
|
|
|
You can refer to columns by ordinal as select foo from bar order by 1, 2.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
you mean to say by Srno. If so its not my requirement.
|
|
|
|
|
So elucidate as it did appear that's what you asked for.
In any case you can use select foo from bar order by 2 or 3 or 1000
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
modified on Monday, August 2, 2010 6:26 AM
|
|
|
|
|
Has anyone used this product? Is there anything out there free and better?
I did some research but chose this product because;
1) It was free
2) Fairly lightweight
3) C# API
4) GNU General Public License which if I understand correctly allows me to just about anything with it. Any gotchas there if I go commercial with my product?
5) Integrates with VS2005 and VS2008
6) Ease of use (Seems to be drop-dead easy?)
Guess I answered my own question unless there is something that just absolutely sucks about it?
Thanks,
Mike
|
|
|
|
|
Never heard of it; got a link?
|
|
|
|
|
|
I just read through their Wikipedia page. It looks like it only persists objects, it seems it doesn't support SQL, it might not allow you to use queries to create different subsets of data.
Looking at their site now...
|
|
|
|
|
PIEBALDconsult wrote: it seems it doesn't support SQL
That's one of the things I didn't like about it, but they have a nice Native Query alternative that looks to be pretty sophisticated.
I've only been working with it for a couple of hours and have found a couple of things I don't like about it but can live with.
Also the documentation is decent so that helps a lot, at least for me.
|
|
|
|
|
If it does what you need, then use it.
|
|
|
|
|
PIEBALDconsult wrote: If it does what you need, then use it.
It looks like it will do what I want but I haven't used many embedded DB's so the only reason I posted was so if I got 3/4 of the way down the line and started finding problems, restrictions, etc. then maybe someone with more experience could save me the frustration and point me to a better EDB.
Anyway thanks for your input
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
