I am working in migrating my cr application to 10th ver from the existing 8.5 where we have to explicitly make a query in query builder and then attach it. I was using dsn in ms odbc for oracle driver to connect the DB. Now in 10.0 i have removed the query file created using 8.5 query builder and trying to write directly to command editor. the report as stand alone is working fine. but when i try to generate the pdf using my vb application then on .export option it is giving error as "Logon failed.
Details: 01S00:[Microsoft][ODBC driver for Oracle]Invalid connection string attribute". i think the problem might be with cddb_oracle.dll or crdb_oracle.dll file as i am using a evaluation version of crystal report 10. as input to dsn i am providing
1.dsn
2.usid
3.database
4.password
what else is missing . plz help....The next is code I use in VB6 to display in CRViewer10 a CR10 report that read data thru ODBC
Set CRReport = CRApplication.OpenReport("Report.rpt", 1)
For MEnt2 = 1 To CRReport.Database.Tables.Count
Set CRTabla = CRReport.Database.Tables(MEnt2)
Set CRConProp = CRTabla.ConnectionProperties("Provider")
CRConProp.value = "MSDASQL"
Set CRConProp = CRTabla.ConnectionProperties("Data Source")
CRConProp.value = "DSNname"
Set CRConProp = CRTabla.ConnectionProperties("Initial Catalog")
CRConProp.value = "Datapath"
Set CRConProp = CRTabla.ConnectionProperties("User ID")
CRConProp.value = ""
Set CRConProp = CRTabla.ConnectionProperties("Locale Identifier")
CRConProp.value = "2058"
Set CRConProp = CRTabla.ConnectionProperties("OLE DB Services")
CRConProp.value = "-5"
Next MEnt2
frmReport.Show vbModal
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Friday, March 30, 2012
Migrating from Access to SQL Server with a web front end
Afternoon all,
Apologies for cross-posting but as my query covers both Access and SQL
Server I thought I'd send it both!
I have inherited a project to migrate a fairly complex series of
Access databases into a single proper SQL database with a web front
end.
Its quite a nasty job as people are working on a variety of data sets
at several Universities around the world and the data has got very
messy; hence the requirement to put it all on one live web enabled
database server and provide a web-based front end (particularly as
some users insist on using Macs so can't run Access as a front end
anyway).
If anyone could give me hints on how to perform such a migration or if
anyone knows of any good books or other documents on this I'd be
grateful for assistance.
Many thanks
Rich May
Museum of LondonFirst off, congratulations - this is a great move to make.
As far as converting the tables, use DTS (Data Transformation Services) in
sql server. It is pretty much wizard driven and works really well. As for
the queries, you would do best to forget you even have them in Access and
rewrite everything using Stored Procedures. My favorite book for "stepping"
between access and sql server is "Microsoft Access Developer's Guide to SQL
Server" by Chipman and Baron. It's wonderful.
As for your web front end... that is where the headache starts. You can
pretty much choose two paths if you want to stay on the Microsoft path. You
can do it using ASP or ASP.NET. Both will run on IIS (.net needs framework
installed).
If you are serving to Macs, you may deliver your app better using ASP
because you will be able to better control the rendered output (very good
for Netscape). I use Visual Basic to build "ActiveX DLL" components to
alleviate the complexity of the ASP pages so you would want to be proficient
in Visual Basic and instantiating COM components. Next, you need to take
ADO (ActiveX Data Objects) head on and learn it well, that is what you will
use to connect to sql, execute queries, and work with data.
If you are using IE on all clients and have control over them, you will find
that Visual Studio.NET does a great job at deploying web applications in
ease and speed, once you pass the high learning curve. I VERY highly
recommend "Programming Microsoft Visual Basic .Net" by Francesco Balena
(1600 pages).
If it all sounds complex, don't get me wrong... it is. If you need help,
let me know, we do this for a living.
--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
"Rich May" <rmay@.museumoflondon.org.uk> wrote in message
news:e12c70e0.0311140943.722ed0ba@.posting.google.c om...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of London|||several choices, depending on what you're comfy with ..
1) sql server, IIS & ASP
2) mySQL, apache, php
here's a RAD tool that I found the other day to create web pages for
either option (I haven't tried it yet)
http://www.citybusinesslogic.com/apwab.php
and somewhere I've got vba code to convert ms-access tables to mysql
format
rmay@.museumoflondon.org.uk (Rich May) wrote in message news:<e12c70e0.0311140943.722ed0ba@.posting.google.com>...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of London|||Hi Rich,
I think there are 3 options. a) Access' Upsizing Migration Wizard
b)Sql Server's DTS and c) setting Access as a linked server and
programmatically importing the data. I believe the main problem
you'll run into is that Access' datatypes are not 100% compatible with
Sql server's. - Louis|||louisducnguyen@.hotmail.com (louis nguyen) wrote in message news:<b0e9d53.0311142220.41dff853@.posting.google.com>...
> Hi Rich,
> I think there are 3 options. a) Access' Upsizing Migration Wizard
> b)Sql Server's DTS and c) setting Access as a linked server and
> programmatically importing the data. I believe the main problem
> you'll run into is that Access' datatypes are not 100% compatible with
> Sql server's. - Louis
Another problem I have seen with the upsize wizard is it makes all
columns with the type nvarchar i.e. unicode which doubles the space
requirements.
Duncan|||"duncan" wrote
> Another problem I have seen with
> the upsize wizard is it makes all
> columns with the type nvarchar i.e.
> unicode which doubles the space
> requirements.
First, I don't think that is true -- all _text_ columns, perhaps, but
certainly not numeric and date/time columns.
Secondly, were you under the impression that Access does NOT use unicode? It
has since Access 2000. There is an option for "unicode compression" to
reduce the impact; I don't know if there is such an option in SQL Server,
but would think that it would have a similar feature.
Larry Linson
Microsoft Access MVP|||Is Cold Fusion a viable alternative to ASP in this kind of situation?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||In Ms SQL...
Char is fixed length and pads with spaces
NChar is fixed length and pads with spaces, plus is unicode
VarChar is variable character (with max length spec)
NVarChar is variable (maxlength), plus is unicode
Since I don't deploy to any other language and don't plan to, I never use
the N' datatypes.
Also in SQL, the "SmallDateTime" field type is 4 bytes and the "DateTime" is
8 bytes). If it's 4 bytes in Access, then the equiv is "SmallDateTime".
In SQL Numeric DT's, there is tinyint, smallint, int, real, bigint, float,
money, smallmoney, and decimal. All with their mix of capacity.
--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
"Larry Linson" <bouncer@.localhost.not> wrote in message
news:bpytb.58064$p9.53071@.nwrddc02.gnilink.net...
> "duncan" wrote
> > Another problem I have seen with
> > the upsize wizard is it makes all
> > columns with the type nvarchar i.e.
> > unicode which doubles the space
> > requirements.
> First, I don't think that is true -- all _text_ columns, perhaps, but
> certainly not numeric and date/time columns.
> Secondly, were you under the impression that Access does NOT use unicode?
It
> has since Access 2000. There is an option for "unicode compression" to
> reduce the impact; I don't know if there is such an option in SQL Server,
> but would think that it would have a similar feature.
> Larry Linson
> Microsoft Access MVP|||Sure, I looked into it some time ago when I was a beginner, but then I
discovered the unimaginable amount of documentation and books from multiple
authors in Microsoft products and I didn't even give it a second thought.
To me, no product is satisfactory if I have to spend days looking for
answers, and especially when they aren't there to be found.
--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
"John Callaghan" <jackit2@.yahoo.com> wrote in message
news:3fb88be3$0$194$75868355@.news.frii.net...
> Is Cold Fusion a viable alternative to ASP in this kind of situation?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||In my experience, the data conversion part of a project like this is almost
trivial compared to the front-end application challenge.
Access forms and reports can be incredibly complex, with subforms, simple
data binding, no-code dynamic behavior (record navigation, filtering,
sorting, finding, column-reordering, all those are built in to Access, not
at all in asp), and the list goes on and on. If your Access front-end
application is complex, it could be impossibly time-consuming to do in asp,
slightly less so in asp.net.
You would need some other tool for reporting (Crystal or others), I've made
crude html reports in asp and it is nearly impossible to do even the easy
stuff Access reports can do.
As a example, I wrote a full maintenance application for a 30 table database
(with lots of RI between tables), with multiple subforms, dropdown
navigation, and lots of other features, all in one day of work.. ASP would
have taken weeks to do even close to the same functionality.
So overall, my suggestion is if the Access forms are complex, plan for 1000
to 2000% more development time over Access forms. You will definitely need a
better reporting tool than html/asp. Hope this helps,
Steve Nyberg
"Rich May" <rmay@.museumoflondon.org.uk> wrote in message
news:e12c70e0.0311140943.722ed0ba@.posting.google.c om...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of Londonsql
Migrating from Access to SQL
Hi guys, its been awhile. I inherited a database thats an MDB and I need to get it on to sql server but I am having problem with this query, can anyone help please. Its suppose to give you the month and year when you click on it, you just put that in and up come the results on a query or report, now I have something similar to this but the code is very different.
SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and the Year]));Which bit are you struggling with? How far have you got?|||The format$ is the part thats giving me fits sQL server doesnt recognize that function|||try this --SELECT DATEPART(year,[Main Table].[Date]) AS [Date By Year]
, DATEPART(month,[Main Table].[Date]) AS [Date By Month]
, [Main Table].[Action Type]
, Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type]
INNER
JOIN [Main Table]
ON [Action Type].ID = [Main Table].[Action Type]
WHERE [Main Table].[Date] >= '2007-03-01' /* first day of this month */
AND [Main Table].[Date] < '2007-03-01' /* first day of next month */
GROUP
BY DATEPART(year,[Main Table].[Date])
, DATEPART(month,[Main Table].[Date])
, [Main Table].[Action Type]note you could concatenate those two datepart expressions if you need to
also note the index-friendly method of extracting rows for a certain month (anything else will involve table scans)
and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVING|||and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVINGUh? How can it not know the difference? Are you confusing access and access developers?|||no, access itself generates that crap|||Only if you don't add a second column into your query designer and make that a "Where".
But yes - Access does not encourage thoughtful & efficient query design.|||Thanks guys its just that the query he created gives you a dialog box "like the edit parameters" box and you can put the month and year in yourself and it generates a report. Now I have one for searching my subforms last name field ALTER FUNCTION dbo.SearchLName
(@.Enter_SubjLastName nvarchar(100))
RETURNS TABLE
AS
RETURN ( SELECT dbo.Report.[Incident Report No], dbo.Subjects.SubjFirstName, dbo.Subjects.SubjLastName, dbo.Report.TypeOfIncident
FROM dbo.Report INNER JOIN
dbo.Subjects ON dbo.Report.[Incident Report No] = dbo.Subjects.[Incident number]
WHERE (dbo.Subjects.SubjLastName = @.Enter_SubjLastName) )
This is a function and you enter in the last name and it generate a report for you, now from what I see hes doing the same thing but with Month and Year, does that make sense??|||Here is what I have so far but I need to do this by month too they get a pretty detailed report
SELECT DATEPART(year, dbo.[Main Table].Date) AS [Date By Year], DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
dbo.[Main Table].[Action Type], SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type] INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
GROUP BY DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]
HAVING (DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)|||Create an MS-Access form with two date controls, an Ok and a Cancel button on it When the user clicks Ok, validate the data in the controls, then use them to call the stored procedure.
-PatP|||The results would have to show in a report, I'm so frustrated, this is what I got so far but its spitting out an error message "Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'Main Table'."
SELECT Year(3/01/2001)([Main Table].[Date],'yyyy') AS [Date By Year], [Main Table].[Violation Type], Sum([Main Table].[Loss])
AS [Sum Of Loss], Count([Main Table].[Violation Type])
AS [CountOfViolation Type]
FROM [Main Table]
GROUP BY Year(3/01/2001)([Main Table].Date,'yyyy'), [Main Table].[Violation Type], Year([Main Table].[Date])
HAVING ((Year()([Main Table].Date)=[Please Enter the Year]))
GO|||You'll have to excuse me, but can we go back a few posts to that first query. Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...|||SELECT
DATEPART(year, dbo.[Main Table].Date) AS [Date By Year],
DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type]
INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
WHERE
(DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)
GROUP BY
DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]
Incidently i'm not seeing a need for the inner join above...|||Quick guess :
SELECT
([Main Table].[Date],'yyyy') AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I inherited this database it was an MDB and I'm trying to convert it to an ADP I ran that select statement in the query analyzer and this is the error message i'm gettting
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.|||Hold on, i need some clarity here for piece of mind, to make sure that i'm working with the right thing :
Are you running these queries in MSSQL or MSAccess ?|||Found my error :
SELECT
DATEPART(year,[Main Table].[Date]) AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I am running them in MSSQL, I imported the tables over from Access 2003 onto SQL server. The user set up his reports a certain way and since Jet SQL and SQL Server dont exactly speak the same language I have to redo allot of the queries. Does that make sense??|||Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...the necessity for doing an inner join is not always predicated on returning something from the joined table
sometimes it is just those rows which have a match that you are interested in
example: say you are the HR department and you have an employees table
suppose you needed to store the email address of their parole officer (let's say to email them confirmation of the employee's attendance)
not every employee has a parole officer, check? so you don't keep the parole officer's email in the employees table (it would need to be NULL for most employees), you keep it in a separate table (incidentally, this is another example of proper normalization, it's 4nf i believe)
now you want to produce a list of employee names and departments for all employees who have a parole officer, but you don't actually need the parole officer's email address
what does your query look like?
that's right, an inner join, selecting only from the left table
:)|||See this the query he set up in Access, if you go into the [Main table] down where the field properties are, he has made one of the fields a combo box and the rowsource is that table [Action Type], and Violation Type is another table that also linked to it. Does that make sense??
SELECT DISTINCTROW Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Violation Type], Sum([Main Table].Loss) AS [Sum Of Loss], Count([Main Table].[Violation Type]) AS [CountOfViolation Type], [Action Type].[Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Violation Type], [Action Type].[Action Type], Year([Main Table].Date)*12+DatePart('m',[Main Table].Date)-1
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and year]));
SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and the Year]));Which bit are you struggling with? How far have you got?|||The format$ is the part thats giving me fits sQL server doesnt recognize that function|||try this --SELECT DATEPART(year,[Main Table].[Date]) AS [Date By Year]
, DATEPART(month,[Main Table].[Date]) AS [Date By Month]
, [Main Table].[Action Type]
, Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type]
INNER
JOIN [Main Table]
ON [Action Type].ID = [Main Table].[Action Type]
WHERE [Main Table].[Date] >= '2007-03-01' /* first day of this month */
AND [Main Table].[Date] < '2007-03-01' /* first day of next month */
GROUP
BY DATEPART(year,[Main Table].[Date])
, DATEPART(month,[Main Table].[Date])
, [Main Table].[Action Type]note you could concatenate those two datepart expressions if you need to
also note the index-friendly method of extracting rows for a certain month (anything else will involve table scans)
and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVING|||and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVINGUh? How can it not know the difference? Are you confusing access and access developers?|||no, access itself generates that crap|||Only if you don't add a second column into your query designer and make that a "Where".
But yes - Access does not encourage thoughtful & efficient query design.|||Thanks guys its just that the query he created gives you a dialog box "like the edit parameters" box and you can put the month and year in yourself and it generates a report. Now I have one for searching my subforms last name field ALTER FUNCTION dbo.SearchLName
(@.Enter_SubjLastName nvarchar(100))
RETURNS TABLE
AS
RETURN ( SELECT dbo.Report.[Incident Report No], dbo.Subjects.SubjFirstName, dbo.Subjects.SubjLastName, dbo.Report.TypeOfIncident
FROM dbo.Report INNER JOIN
dbo.Subjects ON dbo.Report.[Incident Report No] = dbo.Subjects.[Incident number]
WHERE (dbo.Subjects.SubjLastName = @.Enter_SubjLastName) )
This is a function and you enter in the last name and it generate a report for you, now from what I see hes doing the same thing but with Month and Year, does that make sense??|||Here is what I have so far but I need to do this by month too they get a pretty detailed report
SELECT DATEPART(year, dbo.[Main Table].Date) AS [Date By Year], DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
dbo.[Main Table].[Action Type], SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type] INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
GROUP BY DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]
HAVING (DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)|||Create an MS-Access form with two date controls, an Ok and a Cancel button on it When the user clicks Ok, validate the data in the controls, then use them to call the stored procedure.
-PatP|||The results would have to show in a report, I'm so frustrated, this is what I got so far but its spitting out an error message "Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'Main Table'."
SELECT Year(3/01/2001)([Main Table].[Date],'yyyy') AS [Date By Year], [Main Table].[Violation Type], Sum([Main Table].[Loss])
AS [Sum Of Loss], Count([Main Table].[Violation Type])
AS [CountOfViolation Type]
FROM [Main Table]
GROUP BY Year(3/01/2001)([Main Table].Date,'yyyy'), [Main Table].[Violation Type], Year([Main Table].[Date])
HAVING ((Year()([Main Table].Date)=[Please Enter the Year]))
GO|||You'll have to excuse me, but can we go back a few posts to that first query. Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...|||SELECT
DATEPART(year, dbo.[Main Table].Date) AS [Date By Year],
DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type]
INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
WHERE
(DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)
GROUP BY
DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]
Incidently i'm not seeing a need for the inner join above...|||Quick guess :
SELECT
([Main Table].[Date],'yyyy') AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I inherited this database it was an MDB and I'm trying to convert it to an ADP I ran that select statement in the query analyzer and this is the error message i'm gettting
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.|||Hold on, i need some clarity here for piece of mind, to make sure that i'm working with the right thing :
Are you running these queries in MSSQL or MSAccess ?|||Found my error :
SELECT
DATEPART(year,[Main Table].[Date]) AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I am running them in MSSQL, I imported the tables over from Access 2003 onto SQL server. The user set up his reports a certain way and since Jet SQL and SQL Server dont exactly speak the same language I have to redo allot of the queries. Does that make sense??|||Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...the necessity for doing an inner join is not always predicated on returning something from the joined table
sometimes it is just those rows which have a match that you are interested in
example: say you are the HR department and you have an employees table
suppose you needed to store the email address of their parole officer (let's say to email them confirmation of the employee's attendance)
not every employee has a parole officer, check? so you don't keep the parole officer's email in the employees table (it would need to be NULL for most employees), you keep it in a separate table (incidentally, this is another example of proper normalization, it's 4nf i believe)
now you want to produce a list of employee names and departments for all employees who have a parole officer, but you don't actually need the parole officer's email address
what does your query look like?
that's right, an inner join, selecting only from the left table
:)|||See this the query he set up in Access, if you go into the [Main table] down where the field properties are, he has made one of the fields a combo box and the rowsource is that table [Action Type], and Violation Type is another table that also linked to it. Does that make sense??
SELECT DISTINCTROW Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Violation Type], Sum([Main Table].Loss) AS [Sum Of Loss], Count([Main Table].[Violation Type]) AS [CountOfViolation Type], [Action Type].[Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Violation Type], [Action Type].[Action Type], Year([Main Table].Date)*12+DatePart('m',[Main Table].Date)-1
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and year]));
Monday, March 26, 2012
Migrating Access Query to SQL
I have complicated queries in access I want to migrate to SQL. They're
"complicated" only in the sense that I use the results of one calculated
field in an imbedded if (iif) of another calculated field, which in turn is
used in another imbedded if in another calculated field.
I replaced the iif with a CASE statement, and it worked for the first few
fields. I replace the field name with the (case statement) query since (as
far as I know) you can't use a calculated field as part of another field
like you can in access. This is okay until I get out a few fields and the
calculations become too large to be readable.
Here is an example query from access. (No problems until I get to variable
E, then it gets ridiculous)
A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
B: Len(A)
C: Len(myField2)
D: B + C - 10
E: IIF(B-C < 4, IIF(B<4,B,4), B-C)--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
E:
CASE WHEN B-C < 4
THEN CASE WHEN B < 4
THEN B
ELSE 4
END
ELSE B-C
END
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQf/y64echKqOuFEgEQIWuACfbHICFPTl5Pg8QaBDsD/NmtEVNmUAn0MD
gmxaj6LDDbQ5H8by4jsPyu+C
=7xKC
--END PGP SIGNATURE--
Jed Perlowin wrote:
> I have complicated queries in access I want to migrate to SQL. They're
> "complicated" only in the sense that I use the results of one calculated
> field in an imbedded if (iif) of another calculated field, which in turn i
s
> used in another imbedded if in another calculated field.
> I replaced the iif with a CASE statement, and it worked for the first few
> fields. I replace the field name with the (case statement) query since (a
s
> far as I know) you can't use a calculated field as part of another field
> like you can in access. This is okay until I get out a few fields and the
> calculations become too large to be readable.
> Here is an example query from access. (No problems until I get to variabl
e
> E, then it gets ridiculous)
> A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
> B: Len(A)
> C: Len(myField2)
> D: B + C - 10
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
>
>|||Try,
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
case when (b - c) < 4 then case when b < 4 then b else 4 end else b - c end
AMB
"Jed Perlowin" wrote:
> I have complicated queries in access I want to migrate to SQL. They're
> "complicated" only in the sense that I use the results of one calculated
> field in an imbedded if (iif) of another calculated field, which in turn i
s
> used in another imbedded if in another calculated field.
> I replaced the iif with a CASE statement, and it worked for the first few
> fields. I replace the field name with the (case statement) query since (a
s
> far as I know) you can't use a calculated field as part of another field
> like you can in access. This is okay until I get out a few fields and the
> calculations become too large to be readable.
> Here is an example query from access. (No problems until I get to variabl
e
> E, then it gets ridiculous)
> A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
> B: Len(A)
> C: Len(myField2)
> D: B + C - 10
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
>
>|||Thank you for your reply. However, the logic isn't my problem -- but the
fact that you can use B, C, and D in the query (as you have below) in
Access, but (as far as I know -- and I'm hoping I'm wrong) you cannot use
them directly in SQL (and must expand each calculated field with it's
query).
"MGFoster" <me@.privacy.com> wrote in message
news:RpSLd.4078$cl1.1991@.newsread3.news.pas.earthlink.net...
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> E:
> CASE WHEN B-C < 4
> THEN CASE WHEN B < 4
> THEN B
> ELSE 4
> END
> ELSE B-C
> END
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/AwUBQf/y64echKqOuFEgEQIWuACfbHICFPTl5Pg8QaBDsD/NmtEVNmUAn0MD
> gmxaj6LDDbQ5H8by4jsPyu+C
> =7xKC
> --END PGP SIGNATURE--
>
> Jed Perlowin wrote:
is
few
(as
the
variable|||"Jed Perlowin" <jed@.perlowin.com> wrote in message
news:ObcPAMKCFHA.1424@.TK2MSFTNGP09.phx.gbl...
>I have complicated queries in access I want to migrate to SQL. They're
> "complicated" only in the sense that I use the results of one calculated
> field in an imbedded if (iif) of another calculated field, which in turn
> is
> used in another imbedded if in another calculated field.
> I replaced the iif with a CASE statement, and it worked for the first few
> fields. I replace the field name with the (case statement) query since
> (as
> far as I know) you can't use a calculated field as part of another field
> like you can in access. This is okay until I get out a few fields and the
> calculations become too large to be readable.
> Here is an example query from access. (No problems until I get to
> variable
> E, then it gets ridiculous)
> A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
> B: Len(A)
> C: Len(myField2)
> D: B + C - 10
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
>
Use nested selects to refer to the results of computed columns by alias.
You must add a nested select for each level of recursion in your query.
SELECT A,B,C,B+C-10 D,
CASE WHEN B-C<2 THEN
CASE WHEN B<4 THEN B ELSE 4 END
ELSE B-C END E
FROM
(
SELECT A, Len(A) B, Len(MyField2) C
FROM
(
SELECT
IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField) A,
MyField2
FROM T
) DT
)DT2
David|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Yes, you're correct: you can't use aliases in calculations. IOW,
Access SQL:
(col1 + col2)/ col3 As A, A + 25 As B
SQL'r syntax:
(col1 + col2) / col3 As A,
((col1 + col2) / col3) + 25 As B
David Browne has a interesting solution that, unfortunately, doesn't
reduce the complexity of the query, but does allow you to use aliases in
calculations. In essence, he used tabular subqueries to alias the
calculations.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQf/ 4R4echKqOuFEgEQJOXQCgx5V7nCjfwSmy2uVFzQG
lMlXuxm8AoLfz
OWxitX3IfYip6FasKyI2I88+
=mIEC
--END PGP SIGNATURE--
Jed Perlowin wrote:
> Thank you for your reply. However, the logic isn't my problem -- but the
> fact that you can use B, C, and D in the query (as you have below) in
> Access, but (as far as I know -- and I'm hoping I'm wrong) you cannot use
> them directly in SQL (and must expand each calculated field with it's
> query).
>
"complicated" only in the sense that I use the results of one calculated
field in an imbedded if (iif) of another calculated field, which in turn is
used in another imbedded if in another calculated field.
I replaced the iif with a CASE statement, and it worked for the first few
fields. I replace the field name with the (case statement) query since (as
far as I know) you can't use a calculated field as part of another field
like you can in access. This is okay until I get out a few fields and the
calculations become too large to be readable.
Here is an example query from access. (No problems until I get to variable
E, then it gets ridiculous)
A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
B: Len(A)
C: Len(myField2)
D: B + C - 10
E: IIF(B-C < 4, IIF(B<4,B,4), B-C)--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
E:
CASE WHEN B-C < 4
THEN CASE WHEN B < 4
THEN B
ELSE 4
END
ELSE B-C
END
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQf/y64echKqOuFEgEQIWuACfbHICFPTl5Pg8QaBDsD/NmtEVNmUAn0MD
gmxaj6LDDbQ5H8by4jsPyu+C
=7xKC
--END PGP SIGNATURE--
Jed Perlowin wrote:
> I have complicated queries in access I want to migrate to SQL. They're
> "complicated" only in the sense that I use the results of one calculated
> field in an imbedded if (iif) of another calculated field, which in turn i
s
> used in another imbedded if in another calculated field.
> I replaced the iif with a CASE statement, and it worked for the first few
> fields. I replace the field name with the (case statement) query since (a
s
> far as I know) you can't use a calculated field as part of another field
> like you can in access. This is okay until I get out a few fields and the
> calculations become too large to be readable.
> Here is an example query from access. (No problems until I get to variabl
e
> E, then it gets ridiculous)
> A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
> B: Len(A)
> C: Len(myField2)
> D: B + C - 10
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
>
>|||Try,
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
case when (b - c) < 4 then case when b < 4 then b else 4 end else b - c end
AMB
"Jed Perlowin" wrote:
> I have complicated queries in access I want to migrate to SQL. They're
> "complicated" only in the sense that I use the results of one calculated
> field in an imbedded if (iif) of another calculated field, which in turn i
s
> used in another imbedded if in another calculated field.
> I replaced the iif with a CASE statement, and it worked for the first few
> fields. I replace the field name with the (case statement) query since (a
s
> far as I know) you can't use a calculated field as part of another field
> like you can in access. This is okay until I get out a few fields and the
> calculations become too large to be readable.
> Here is an example query from access. (No problems until I get to variabl
e
> E, then it gets ridiculous)
> A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
> B: Len(A)
> C: Len(myField2)
> D: B + C - 10
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
>
>|||Thank you for your reply. However, the logic isn't my problem -- but the
fact that you can use B, C, and D in the query (as you have below) in
Access, but (as far as I know -- and I'm hoping I'm wrong) you cannot use
them directly in SQL (and must expand each calculated field with it's
query).
"MGFoster" <me@.privacy.com> wrote in message
news:RpSLd.4078$cl1.1991@.newsread3.news.pas.earthlink.net...
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> E:
> CASE WHEN B-C < 4
> THEN CASE WHEN B < 4
> THEN B
> ELSE 4
> END
> ELSE B-C
> END
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/AwUBQf/y64echKqOuFEgEQIWuACfbHICFPTl5Pg8QaBDsD/NmtEVNmUAn0MD
> gmxaj6LDDbQ5H8by4jsPyu+C
> =7xKC
> --END PGP SIGNATURE--
>
> Jed Perlowin wrote:
is
few
(as
the
variable|||"Jed Perlowin" <jed@.perlowin.com> wrote in message
news:ObcPAMKCFHA.1424@.TK2MSFTNGP09.phx.gbl...
>I have complicated queries in access I want to migrate to SQL. They're
> "complicated" only in the sense that I use the results of one calculated
> field in an imbedded if (iif) of another calculated field, which in turn
> is
> used in another imbedded if in another calculated field.
> I replaced the iif with a CASE statement, and it worked for the first few
> fields. I replace the field name with the (case statement) query since
> (as
> far as I know) you can't use a calculated field as part of another field
> like you can in access. This is okay until I get out a few fields and the
> calculations become too large to be readable.
> Here is an example query from access. (No problems until I get to
> variable
> E, then it gets ridiculous)
> A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
> B: Len(A)
> C: Len(myField2)
> D: B + C - 10
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
>
Use nested selects to refer to the results of computed columns by alias.
You must add a nested select for each level of recursion in your query.
SELECT A,B,C,B+C-10 D,
CASE WHEN B-C<2 THEN
CASE WHEN B<4 THEN B ELSE 4 END
ELSE B-C END E
FROM
(
SELECT A, Len(A) B, Len(MyField2) C
FROM
(
SELECT
IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField) A,
MyField2
FROM T
) DT
)DT2
David|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Yes, you're correct: you can't use aliases in calculations. IOW,
Access SQL:
(col1 + col2)/ col3 As A, A + 25 As B
SQL'r syntax:
(col1 + col2) / col3 As A,
((col1 + col2) / col3) + 25 As B
David Browne has a interesting solution that, unfortunately, doesn't
reduce the complexity of the query, but does allow you to use aliases in
calculations. In essence, he used tabular subqueries to alias the
calculations.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQf/ 4R4echKqOuFEgEQJOXQCgx5V7nCjfwSmy2uVFzQG
lMlXuxm8AoLfz
OWxitX3IfYip6FasKyI2I88+
=mIEC
--END PGP SIGNATURE--
Jed Perlowin wrote:
> Thank you for your reply. However, the logic isn't my problem -- but the
> fact that you can use B, C, and D in the query (as you have below) in
> Access, but (as far as I know -- and I'm hoping I'm wrong) you cannot use
> them directly in SQL (and must expand each calculated field with it's
> query).
>
Monday, March 12, 2012
Migartion issue with ORDER BY Clause
Please help me how to write the following query in SQL Server 2005
SELECT 3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
CONVERT(char(10), fulldate, 126),
CONVERT(char(8), fulldate, 108),
flag = CASE Invalid WHEN 'Y' THEN 2 ELSE 1 END,
srk
FROM #raw
ORDER BY tag, parent, 'Value!3!date' asc
HUH?
What's the error?
And what's
Code Snippet
'Value!3!date' asc
|||Sorry. i had not copied full query.Actually i am migrating Database from SQL Server 2000 to SQL Server 2005.I used SQL Server 2005 upgrade advisor.It shows where i need to modify the query.
Error is:Upgrade Advisor detected the use of noninteger constants in the ORDER BY clause of one or more statements. Noninteger constants are allowed (and ignored) in the ORDER BY clause when the database compatibility mode is set to 80 and earlier. Noninteger constants in the ORDER BY clause will cause the statement to fail when the database compatibility mode is set to 90.Upgrade Advisor detected the use of noninteger constants in the ORDER BY clause of one or more statements. Noninteger constants are allowed (and ignored) in the ORDER BY clause when the database compatibility mode is set to 80 and earlier. Noninteger constants in the ORDER BY clause will cause the statement to fail when the database compatibility mode is set to 90.
SELECT 1 AS tag,
NULL AS parent,
ISNULL(wmeWWTWMeter.wmeEARefNo,'not set')
AS [Station!1!stationReference],
sitSite.sitDescription AS [Station!1!stationName],
NULL AS [SetofValues!2!parameter],
NULL AS [SetofValues!2!qualifier],
NULL AS [SetofValues!2!dataType],
NULL AS [SetofValues!2!period],
NULL AS [SetofValues!2!characteristic],
NULL AS [SetofValues!2!units],
NULL AS [SetofValues!2!startDate],
NULL AS [SetofValues!2!startTime],
NULL AS [SetofValues!2!endDate],
NULL AS [SetofValues!2!endTime],
NULL AS [SetofValues!2!dayOrigin],
NULL AS [SetofValues!2!valuesPerDay],
NULL AS [Value!3!date],
NULL AS [Value!3!time],
NULL AS [Value!3!flag1],
NULL AS [Value!3!!element]
FROM sitsite INNER JOIN wmewwtwmeter ON sitsite.sitIdentifier = wmewwtwmeter.sitidentifier
WHERE sitSite.sitIdentifier = @.sitID
UNION ALL
SELECT 2,1,NULL,NULL,parameter,qualifer,dataType,period,characteristic,units,
CONVERT(char(10), mindate, 126),CONVERT(char(8), mindate, 108),
CONVERT(char(10), maxdate, 126),CONVERT(char(8), maxdate, 108),
dayOrigin,valuesPerDay,NULL,NULL,NULL,NULL
FROM #huwj INNER JOIN #minmax ON dummyKey = 'EA'
UNION ALL
SELECT 3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
CONVERT(char(10), fulldate, 126),
CONVERT(char(8), fulldate, 108),
flag = CASE Invalid WHEN 'Y' THEN 2 ELSE 1 END,
Reading
FROM #raw
ORDER BY tag, parent, 'Value!3!date' asc
Could you please help in this.
Thank you,
Regards
Srikar
midian calculation
Hi guys,
I have a dilemma, I used the following query to find the Q-by-q median. It
uses a temporary table. I think it's because it has no index and foreign
key, it takes over 5 minutes to run. When we look at subquestions, there are
over 10,000 records and cross join takes forever. I don't know if I can
create indexes on temp table and wonder if the process will take long
anyway.
--Put the course marks in temp table
--DROP TABLE #QBYQ
CREATE TABLE #QBYQ (QUESTION_ID INT, QUESTION_POINT_MARK DECIMAL)
INSERT INTO #QBYQ
SELECT QM.QUESTION_ID, QM.QUESTION_POINT_MARK
from QUESTION_MARK QM, Question q, Enrollment e
where
e.ENROLLMENT_ID = qm.ENROLLMENT_ID
and q.QUESTION_ID = qm.QUESTION_ID
and e.COURSE_YEAR = 2005
and e.COURSE_SESSION = 1
and e.COURSE_CODE = 'FA4'
--and q.PARENT_QUESTION_ID is null
AND QM.QUESTION_POINT_MARK IS NOT NULL
and q.QUESTION_TYPE_CODE = 'EN'
ORDER BY QM.QUESTION_ID, QM.QUESTION_POINT_MARK
-- It is currently doing statistical median, the smaller of the two if even
rows
-- Get this working first before calculating the financial median (avg of
middle 2 rows)
SELECT Q.LABEL AS QUESTION,
QM1.QUESTION_POINT_MARK AS MEDIAN
from QUESTION Q,
#QBYQ QM1 CROSS JOIN #QBYQ QM2
WHERE QM1.QUESTION_ID = QM2.QUESTION_ID
AND Q.QUESTION_ID = QM1.QUESTION_ID
GROUP BY Q.LABEL, QM1.QUESTION_POINT_MARK
HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
ORDER BY Q.LABEL
I then try to put the select in-line but didn't get the correct results. Can
you tell what's wrong? Looks like HAVING is not doing what it is supposed to
do.
SELECT Q.LABEL AS QUESTION,
QM1.QUESTION_POINT_MARK AS MEDIAN
from QUESTION_MARK QM1, QUESTION_MARK QM2, ENROLLMENT E, QUESTION Q
WHERE
QM1.QUESTION_ID = QM2.QUESTION_ID
AND QM1.QUESTION_ID = Q.QUESTION_ID
AND QM1.ENROLLMENT_ID = E.ENROLLMENT_ID
AND QM2.QUESTION_ID = Q.QUESTION_ID
AND QM2.ENROLLMENT_ID = E.ENROLLMENT_ID
and e.COURSE_YEAR = 2005
and e.COURSE_SESSION = 1
and e.COURSE_CODE = 'FA4'
and q.PARENT_QUESTION_ID is null
and q.QUESTION_TYPE_CODE = 'EN'
GROUP BY
Q.LABEL,
QM1.QUESTION_POINT_MARK
HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
ORDER BY Q.LABEL, QM1.QUESTION_POINT_MARK
ThanksHi Ray5531,
Medians have been discussed many times in this newsgroup. You may want
to google past threads.
Maybe the following two threads help you out:
http://tinyurl.com/7ba4w
http://tinyurl.com/7fsjr
Hope this helps,
Gert-Jan
Ray5531 wrote:
> Hi guys,
> I have a dilemma, I used the following query to find the Q-by-q median. It
> uses a temporary table. I think it's because it has no index and foreign
> key, it takes over 5 minutes to run. When we look at subquestions, there a
re
> over 10,000 records and cross join takes forever. I don't know if I can
> create indexes on temp table and wonder if the process will take long
> anyway.
> --Put the course marks in temp table
> --DROP TABLE #QBYQ
> CREATE TABLE #QBYQ (QUESTION_ID INT, QUESTION_POINT_MARK DECIMAL)
> INSERT INTO #QBYQ
> SELECT QM.QUESTION_ID, QM.QUESTION_POINT_MARK
> from QUESTION_MARK QM, Question q, Enrollment e
> where
> e.ENROLLMENT_ID = qm.ENROLLMENT_ID
> and q.QUESTION_ID = qm.QUESTION_ID
> and e.COURSE_YEAR = 2005
> and e.COURSE_SESSION = 1
> and e.COURSE_CODE = 'FA4'
> --and q.PARENT_QUESTION_ID is null
> AND QM.QUESTION_POINT_MARK IS NOT NULL
> and q.QUESTION_TYPE_CODE = 'EN'
> ORDER BY QM.QUESTION_ID, QM.QUESTION_POINT_MARK
> -- It is currently doing statistical median, the smaller of the two if eve
n
> rows
> -- Get this working first before calculating the financial median (avg of
> middle 2 rows)
> SELECT Q.LABEL AS QUESTION,
> QM1.QUESTION_POINT_MARK AS MEDIAN
> from QUESTION Q,
> #QBYQ QM1 CROSS JOIN #QBYQ QM2
> WHERE QM1.QUESTION_ID = QM2.QUESTION_ID
> AND Q.QUESTION_ID = QM1.QUESTION_ID
> GROUP BY Q.LABEL, QM1.QUESTION_POINT_MARK
> HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> ORDER BY Q.LABEL
> I then try to put the select in-line but didn't get the correct results. C
an
> you tell what's wrong? Looks like HAVING is not doing what it is supposed
to
> do.
> SELECT Q.LABEL AS QUESTION,
> QM1.QUESTION_POINT_MARK AS MEDIAN
> from QUESTION_MARK QM1, QUESTION_MARK QM2, ENROLLMENT E, QUESTION Q
> WHERE
> QM1.QUESTION_ID = QM2.QUESTION_ID
> AND QM1.QUESTION_ID = Q.QUESTION_ID
> AND QM1.ENROLLMENT_ID = E.ENROLLMENT_ID
> AND QM2.QUESTION_ID = Q.QUESTION_ID
> AND QM2.ENROLLMENT_ID = E.ENROLLMENT_ID
> and e.COURSE_YEAR = 2005
> and e.COURSE_SESSION = 1
> and e.COURSE_CODE = 'FA4'
> and q.PARENT_QUESTION_ID is null
> and q.QUESTION_TYPE_CODE = 'EN'
> GROUP BY
> Q.LABEL,
> QM1.QUESTION_POINT_MARK
> HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> ORDER BY Q.LABEL, QM1.QUESTION_POINT_MARK
> Thanks|||On Mon, 25 Apr 2005 10:26:30 -0700, Ray5531 wrote:
(snip)
>I don't know if I can
>create indexes on temp table
Hi Ray,
You can. The best way is to use CREATE TABLE to create the table (with
PRIMARY KEY and UNIQUE constraints; accompanying indexes will be generated
automatically), then use CREATE INDEX to add extra (non-unique) indexes,
then use INSERT INTO ... SELECT to load the temp table with data. (This
order of creation minimizes the number of recompiles). OTOH, there might
be cases where definin g an index after loading the table with data might
be quicker - test all variation to find the quickest way in your scenario.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I have a dilemma, I used the following query to find the Q-by-q median. It
uses a temporary table. I think it's because it has no index and foreign
key, it takes over 5 minutes to run. When we look at subquestions, there are
over 10,000 records and cross join takes forever. I don't know if I can
create indexes on temp table and wonder if the process will take long
anyway.
--Put the course marks in temp table
--DROP TABLE #QBYQ
CREATE TABLE #QBYQ (QUESTION_ID INT, QUESTION_POINT_MARK DECIMAL)
INSERT INTO #QBYQ
SELECT QM.QUESTION_ID, QM.QUESTION_POINT_MARK
from QUESTION_MARK QM, Question q, Enrollment e
where
e.ENROLLMENT_ID = qm.ENROLLMENT_ID
and q.QUESTION_ID = qm.QUESTION_ID
and e.COURSE_YEAR = 2005
and e.COURSE_SESSION = 1
and e.COURSE_CODE = 'FA4'
--and q.PARENT_QUESTION_ID is null
AND QM.QUESTION_POINT_MARK IS NOT NULL
and q.QUESTION_TYPE_CODE = 'EN'
ORDER BY QM.QUESTION_ID, QM.QUESTION_POINT_MARK
-- It is currently doing statistical median, the smaller of the two if even
rows
-- Get this working first before calculating the financial median (avg of
middle 2 rows)
SELECT Q.LABEL AS QUESTION,
QM1.QUESTION_POINT_MARK AS MEDIAN
from QUESTION Q,
#QBYQ QM1 CROSS JOIN #QBYQ QM2
WHERE QM1.QUESTION_ID = QM2.QUESTION_ID
AND Q.QUESTION_ID = QM1.QUESTION_ID
GROUP BY Q.LABEL, QM1.QUESTION_POINT_MARK
HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
ORDER BY Q.LABEL
I then try to put the select in-line but didn't get the correct results. Can
you tell what's wrong? Looks like HAVING is not doing what it is supposed to
do.
SELECT Q.LABEL AS QUESTION,
QM1.QUESTION_POINT_MARK AS MEDIAN
from QUESTION_MARK QM1, QUESTION_MARK QM2, ENROLLMENT E, QUESTION Q
WHERE
QM1.QUESTION_ID = QM2.QUESTION_ID
AND QM1.QUESTION_ID = Q.QUESTION_ID
AND QM1.ENROLLMENT_ID = E.ENROLLMENT_ID
AND QM2.QUESTION_ID = Q.QUESTION_ID
AND QM2.ENROLLMENT_ID = E.ENROLLMENT_ID
and e.COURSE_YEAR = 2005
and e.COURSE_SESSION = 1
and e.COURSE_CODE = 'FA4'
and q.PARENT_QUESTION_ID is null
and q.QUESTION_TYPE_CODE = 'EN'
GROUP BY
Q.LABEL,
QM1.QUESTION_POINT_MARK
HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
ORDER BY Q.LABEL, QM1.QUESTION_POINT_MARK
ThanksHi Ray5531,
Medians have been discussed many times in this newsgroup. You may want
to google past threads.
Maybe the following two threads help you out:
http://tinyurl.com/7ba4w
http://tinyurl.com/7fsjr
Hope this helps,
Gert-Jan
Ray5531 wrote:
> Hi guys,
> I have a dilemma, I used the following query to find the Q-by-q median. It
> uses a temporary table. I think it's because it has no index and foreign
> key, it takes over 5 minutes to run. When we look at subquestions, there a
re
> over 10,000 records and cross join takes forever. I don't know if I can
> create indexes on temp table and wonder if the process will take long
> anyway.
> --Put the course marks in temp table
> --DROP TABLE #QBYQ
> CREATE TABLE #QBYQ (QUESTION_ID INT, QUESTION_POINT_MARK DECIMAL)
> INSERT INTO #QBYQ
> SELECT QM.QUESTION_ID, QM.QUESTION_POINT_MARK
> from QUESTION_MARK QM, Question q, Enrollment e
> where
> e.ENROLLMENT_ID = qm.ENROLLMENT_ID
> and q.QUESTION_ID = qm.QUESTION_ID
> and e.COURSE_YEAR = 2005
> and e.COURSE_SESSION = 1
> and e.COURSE_CODE = 'FA4'
> --and q.PARENT_QUESTION_ID is null
> AND QM.QUESTION_POINT_MARK IS NOT NULL
> and q.QUESTION_TYPE_CODE = 'EN'
> ORDER BY QM.QUESTION_ID, QM.QUESTION_POINT_MARK
> -- It is currently doing statistical median, the smaller of the two if eve
n
> rows
> -- Get this working first before calculating the financial median (avg of
> middle 2 rows)
> SELECT Q.LABEL AS QUESTION,
> QM1.QUESTION_POINT_MARK AS MEDIAN
> from QUESTION Q,
> #QBYQ QM1 CROSS JOIN #QBYQ QM2
> WHERE QM1.QUESTION_ID = QM2.QUESTION_ID
> AND Q.QUESTION_ID = QM1.QUESTION_ID
> GROUP BY Q.LABEL, QM1.QUESTION_POINT_MARK
> HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> ORDER BY Q.LABEL
> I then try to put the select in-line but didn't get the correct results. C
an
> you tell what's wrong? Looks like HAVING is not doing what it is supposed
to
> do.
> SELECT Q.LABEL AS QUESTION,
> QM1.QUESTION_POINT_MARK AS MEDIAN
> from QUESTION_MARK QM1, QUESTION_MARK QM2, ENROLLMENT E, QUESTION Q
> WHERE
> QM1.QUESTION_ID = QM2.QUESTION_ID
> AND QM1.QUESTION_ID = Q.QUESTION_ID
> AND QM1.ENROLLMENT_ID = E.ENROLLMENT_ID
> AND QM2.QUESTION_ID = Q.QUESTION_ID
> AND QM2.ENROLLMENT_ID = E.ENROLLMENT_ID
> and e.COURSE_YEAR = 2005
> and e.COURSE_SESSION = 1
> and e.COURSE_CODE = 'FA4'
> and q.PARENT_QUESTION_ID is null
> and q.QUESTION_TYPE_CODE = 'EN'
> GROUP BY
> Q.LABEL,
> QM1.QUESTION_POINT_MARK
> HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> ORDER BY Q.LABEL, QM1.QUESTION_POINT_MARK
> Thanks|||On Mon, 25 Apr 2005 10:26:30 -0700, Ray5531 wrote:
(snip)
>I don't know if I can
>create indexes on temp table
Hi Ray,
You can. The best way is to use CREATE TABLE to create the table (with
PRIMARY KEY and UNIQUE constraints; accompanying indexes will be generated
automatically), then use CREATE INDEX to add extra (non-unique) indexes,
then use INSERT INTO ... SELECT to load the temp table with data. (This
order of creation minimizes the number of recompiles). OTOH, there might
be cases where definin g an index after loading the table with data might
be quicker - test all variation to find the quickest way in your scenario.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, February 24, 2012
Microsoft SQL-DMO error 7405 on linked server query
I am attempting to execute a query on a linked oracle server. The link
is fine and I can execute the query
from query analyser but when I create the stored procedure I get this
error:
Microsoft SQL-DMO (ODBC SQL State:42000)
Error 7405: Hetergenous queries require the ANSI NULLS and ANSI
WARNINGS
options
to be set for the connection. This ensures consistent query semantics.
Enable
these options and then reissue your query.
I have looked through numerous posting on the subject and still
cannot resolve the issue.
here is the code
CREATE PROCEDURE [DBO].[TEST1] AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
select sid from ......
GO
After this I attempted to alter the procdure still with no effect and
finally I issued the sp_configure 'user options',
'32' do set the default and still no luck does anyone have any other
suggestions.Try removing the GO after setting the ANSI NULLS and WARNINGS.|||ehwood@.msn.com (Eric Wood) wrote in message news:<43c03fab.0402191320.27124b8e@.posting.g
oogle.com>...
> I am attempting to execute a query on a linked oracle server. The link
> is fine and I can execute the query
> from query analyser but when I create the stored procedure I get this
> error:
> Microsoft SQL-DMO (ODBC SQL State:42000)
> Error 7405: Hetergenous queries require the ANSI NULLS and ANSI
> WARNINGS
> options
> to be set for the connection. This ensures consistent query semantics.
> Enable
> these options and then reissue your query.
>
> I have looked through numerous posting on the subject and still
> cannot resolve the issue.
> here is the code
> CREATE PROCEDURE [DBO].[TEST1] AS
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> GO
> select sid from ......
> GO
> After this I attempted to alter the procdure still with no effect and
> finally I issued the sp_configure 'user options',
> '32' do set the default and still no luck does anyone have any other
> suggestions.
Well problem solved and lesson learned. The error I was receiving was
due to a bug in enterprize manager knowledge base 296769, create the
stored procedure using query analyiser and all is well.
Eric
is fine and I can execute the query
from query analyser but when I create the stored procedure I get this
error:
Microsoft SQL-DMO (ODBC SQL State:42000)
Error 7405: Hetergenous queries require the ANSI NULLS and ANSI
WARNINGS
options
to be set for the connection. This ensures consistent query semantics.
Enable
these options and then reissue your query.
I have looked through numerous posting on the subject and still
cannot resolve the issue.
here is the code
CREATE PROCEDURE [DBO].[TEST1] AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
select sid from ......
GO
After this I attempted to alter the procdure still with no effect and
finally I issued the sp_configure 'user options',
'32' do set the default and still no luck does anyone have any other
suggestions.Try removing the GO after setting the ANSI NULLS and WARNINGS.|||ehwood@.msn.com (Eric Wood) wrote in message news:<43c03fab.0402191320.27124b8e@.posting.g
oogle.com>...
> I am attempting to execute a query on a linked oracle server. The link
> is fine and I can execute the query
> from query analyser but when I create the stored procedure I get this
> error:
> Microsoft SQL-DMO (ODBC SQL State:42000)
> Error 7405: Hetergenous queries require the ANSI NULLS and ANSI
> WARNINGS
> options
> to be set for the connection. This ensures consistent query semantics.
> Enable
> these options and then reissue your query.
>
> I have looked through numerous posting on the subject and still
> cannot resolve the issue.
> here is the code
> CREATE PROCEDURE [DBO].[TEST1] AS
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> GO
> select sid from ......
> GO
> After this I attempted to alter the procdure still with no effect and
> finally I issued the sp_configure 'user options',
> '32' do set the default and still no luck does anyone have any other
> suggestions.
Well problem solved and lesson learned. The error I was receiving was
due to a bug in enterprize manager knowledge base 296769, create the
stored procedure using query analyiser and all is well.
Eric
Subscribe to:
Posts (Atom)