Help for report(rsInvalidItemPath)

Hi
I have beening trying to build a report with 2 parameters. When I run it
under VS.net, it all went well, but after I deploy it to the server, I got
the message as following: **Reporting Services Error--The path of the item ''
is not valid. The full path must be less than 260 characters long, must start
with slash; other restrictions apply. Check the documentation for complete
set of restrictions. (rsInvalidItemPath) Get Online Help **
what went wrong, please help.
the URL as following:
http://localhost/reportserver?%2fRev_Stats%2fRev_Stats&rs:Command=Render
TIAThe url looks correct. Did you get this url by navigating to the
reportserver virtual directory? Is there anything more in the
reportserver_<timestamp>.log file?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"wd1153" <wd1153@.discussions.microsoft.com> wrote in message
news:2B5DBCB5-B943-4675-997B-145DF0ECDF17@.microsoft.com...
> Hi
> I have beening trying to build a report with 2 parameters. When I run it
> under VS.net, it all went well, but after I deploy it to the server, I got
> the message as following: **Reporting Services Error--The path of the item
> ''
> is not valid. The full path must be less than 260 characters long, must
> start
> with slash; other restrictions apply. Check the documentation for complete
> set of restrictions. (rsInvalidItemPath) Get Online Help **
> what went wrong, please help.
> the URL as following:
> http://localhost/reportserver?%2fRev_Stats%2fRev_Stats&rs:Command=Render
> TIA
>sql

Help Designing a DB

I am trying to build something similar to www.alienware.com where it lets you build your own computer. I was wondering if some one could help me design sturcture to do it on my own. I am zero in DB and know little asp. I am trying to do it for my own site.
ThanksCan you list all of the things you need to know about?

Make a list...

Modem, Modem speed, Modem Type, CPU, CPU Speed, Memroy, Memory Amount...

Once your list is complete, group like itemas in to containers/Entities

Mak sure when you make a list to collect what type of data it is, number date, char, and it's size..

Make a definition for each as well...this will be helpful...

Then devlop rules...

a computer may hav 0 to many hard drives...

So you may have a computer table and a hard drive...

Now you know you need a ComputerHardrive table to show which Computer has which Harddrive...

what'dya think?

Oh, and google up data modeling...

Once that's done we can discuss Physical implementation...|||How you design the database involves more than just determining what you want to store. You also have to consider performance, usage, functionality, et al.

But a simple layout would be:

Table: CPU
CPUName-varchar(50)
CPUSn-varchar(25)
CPUManu-varchar(50)

Table: Monitor
MonModel-varchar(50)
MonSerial-varchar(25)
MonDesc-varchar(150)

Table: Storage
Type-varchar(50)
Desc-varchar(100)

And so on..

Basically, tables for the different options, a customer table, order table, and a table to hold what the basics are for your clients. Of course, a better way would be tables to hold the various peripherals joined to whatever index (key) system you want and so on.

If you have MS Access, monkey around with it, get the layout you want then upsize it to SQL Server.|||Originally posted by acral
You also have to consider performance, usage, functionality, et al.


Yeah, That's the physical part...

A sound, solid, logical model is THE place to start...

Help creating a SELECT statement for "today"

Hello,

I am attempting to build a MS SQL query that will return data from
"today"; today being current day 8:00AM-10:00PM today. My goal is to
return the data from a table that is written to throughout the day, the
query will provide the current grade of service in our call center.

I am having difficulty defining my where clause:

- I can accomplish my goal my statically defining my 'date between' as
the actual date and time (not ideal)

- I can accomplish the second part of my date using CURRENT_TIMESTAMP;
but I am unable to define the starting point

Here is where I am thus far:

/* We are going to count the total calls into each queue from start of
business today (8:00AM) to now */

select COUNT(Result) as "Total Sales Calls Offered" from
dbo.QueueEncounter
where Direction='0'
and
QueueID='1631'
and
/* This is where I get lost */
Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())

Clearly the last line returns zero as there are no calls between the
same date range. How can I add to that line, or write this to work?

Any thoughts?

Thanks for the help.

-ChrisOn 25 Jan 2006 10:03:53 -0800, Chris wrote:

>Hello,
>I am attempting to build a MS SQL query that will return data from
>"today"; today being current day 8:00AM-10:00PM today. My goal is to
>return the data from a table that is written to throughout the day, the
>query will provide the current grade of service in our call center.
>I am having difficulty defining my where clause:
>- I can accomplish my goal my statically defining my 'date between' as
>the actual date and time (not ideal)
>- I can accomplish the second part of my date using CURRENT_TIMESTAMP;
>but I am unable to define the starting point
>Here is where I am thus far:
>/* We are going to count the total calls into each queue from start of
>business today (8:00AM) to now */
>select COUNT(Result) as "Total Sales Calls Offered" from
>dbo.QueueEncounter
>where Direction='0'
>and
>QueueID='1631'
>and
>/* This is where I get lost */
>Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())
>Clearly the last line returns zero as there are no calls between the
>same date range. How can I add to that line, or write this to work?
>Any thoughts?
>Thanks for the help.
>-Chris

Hi Chris,

You say you want rows for today, 8:00AM-10:00PM. Does this imply that
the table also contains rows outside the 8:00AM-10:00PM time frame that
you don't want to include?

AND Time BETWEEN DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP),
'8:00AM')
AND DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP),
'10:00PM')

Note that this will include a row with time exactly equal to 10 PM, but
exclude a row with time 3 milliseconds after 10PM.

If you want all rows for the whole day (0:00 - 24:00), use
AND Time >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
AND Time < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1)

--
Hugo Kornelis, SQL Server MVP|||Awesome Hugo, thanks so much for the help - My query is now nearly
complete; with one last problem....

declare @.today datetime,
@.tomorrow datetime,
@.offered smallint,
@.answeredin120 smallint,
@.GOS smallint

set @.today = convert(char(8), GETDATE ( ), 112)
set @.tomorrow = @.today + 1

-- Find total calls offered
set @.offered = (select COUNT(Result) from dbo.QueueEncounter
where Direction='0' and QueueID='1438' and Time >= @.today and Time <
@.tomorrow)

-- Find total calls answered in 120 seconds
set @.answeredin120 =(select COUNT(Result) from dbo.QueueEncounter
where Direction='0' and QueueID='1438' and Time >= @.today and Time <
@.tomorrow and WaitTime <= 120)

-- Divide the total calls offered by the total calls answered in X
multiplied by 100 to find current GOS ??

set @.GOS = (@.offered)/(@.answeredin120)*100

select @.GOS

The problem is my GOS is being returned as 100 when it is really apprx
77%.
Where did I go wrong?

-Thanks!|||On 25 Jan 2006 14:12:48 -0800, Chris wrote:

(snip)
>-- Divide the total calls offered by the total calls answered in X
>multiplied by 100 to find current GOS ??
>set @.GOS = (@.offered)/(@.answeredin120)*100
>select @.GOS
>The problem is my GOS is being returned as 100 when it is really apprx
>77%.
>Where did I go wrong?

Hi Chris,

Integer division: divide two integers, the result is integer too.

SELECT 1/3
SELECT 1.0/3
SELECT 1/3.0
SELECT 1.0/3.0

The above show that forcing at least one operand to non-integer suffices
to get a result with fraction. In your case, one possible way would be

SET @.GOS = CAST(@.offered AS decimal(10,2)) / @.answeredin120 * 100

Or even

SET @.GOS = 100.0 * @.offerec / @.answeredin120

--
Hugo Kornelis, SQL Server MVP|||Hugo, Again many thanks... I will try this at the office tomorrow.

Cheers.

Help cannot connect to SQL Server 2000

I'm trying to build a simple form using SQL Server 2000 and am having trouble establishing a connection to my database. I was able to build and successfully test my connection in Server Explorer. But when I try to open the connection in my app I keep getting a "Login failed for user xxx" error message. I'm using standard Windows NT security. Would appreciate any help.If you are using Windows security, when you are testing in Server Explorer, you are using your Security Context. The ASP.NET page is useing the ASPNET user's security context, so if you want to use Windows security, you must add the ASPNET Windows user to SQL Server. If you have Enterprise Magager, expand out the database and right click on users, and then New database User. The dialog from there is mostly self explanitory. Let us know if you have any other questions.|||Thanks Douglas that did the trick!

Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
Greg
On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:

>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
> (very big snip)
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Greg

Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:

>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>
> (very big snip)
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Gregsql

Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
> >Hello,
> >
> >I need help in finding the best way to build a query for the following.
> (very big snip)
> >What I would like assistance on is how you would recommend doing this...
> >what do you believe would be the best method. I run into this type of
> >need several times a year and I really would like find a solution I can
> >use in situations as this.
> >
> >Thank you for your time and willingness to help.
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Greg

Help building a #temp table

I have the following query:

--build query for #temp table
select 'L' as RECTYPE, identity(int, 1, 1) as JELINENO, max(i.jeno) + 1 as JENO, 'AJE' as JECODE,
--build the fiscal year, when month = October, November or December add 1 to actual year
FY = case
when datepart(month, GetDate()) = '10' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '11' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '12' then datepart(year, GetDate()) + 1
else datepart(year, GetDate())
end,
-- The PD starts in April and increases by 1 each month
PD = case
when datepart(month, GetDate()) = '1' then '10'
when datepart(month, GetDate()) = '2' then '11'
when datepart(month, GetDate()) = '3' then '12'
when datepart(month, GetDate()) = '4' then '1'
when datepart(month, GetDate()) = '5' then '2'
when datepart(month, GetDate()) = '6' then '3'
when datepart(month, GetDate()) = '7' then '4'
when datepart(month, GetDate()) = '8' then '5'
when datepart(month, GetDate()) = '9' then '6'
when datepart(month, GetDate()) = '10' then '7'
when datepart(month, GetDate()) = '11' then '8'
when datepart(month, GetDate()) = '12' then '9'
else 'error'
end,
'1' as SUBPD, 'N' as REVFLAG, 'Journal Entry from MAXIMO' as HEADDESC,
--If the issuetype is a return the AMOUNT is a negative value, multiply by -1
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
go
select * from #temp
drop table #temp

When I run this query, I get the following error:

Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#temp'.

I can find no reason for this error. If I run the query without the AMOUNT = case statement, it works fine. Can anyone help?

Thanks,

Dave

The mistake only arises in the AMOUNT=CASE statement since it's the only one (except from the MAX(i.jeno) column) that's accesing a real column in the database. The rest of the columns are calculated.

So, as you already have an aggregate function (the MAX) the rest of the columns from real tables must either have an aggregate function or the SELECT clause must contain a GROUP BY clause.

You can't form a SELECT with only one MAX column and the rest of them normal columns without telling SQL how it should group the results from where to choose the MAX value from.

If you really want to achieve that you have to calculate the MAX column either before in a variable or in the same SELECT with a sub-SELECT query.

IE: SELECT var1, var2, var3, (SELECT MAX(othervar) FROM table)

Hope i was clear.
Good luck,
Alan.

|||

First of all I would compress the code and rewrite it as following.

Please post DDLs as it's not clear what do you want to do. the problem is clear as you have a reference to the column m.linecost that is not in a group by list. Just to get rid of the error you can add a group by abs(m.linecost), but it doesn't look like what you want to do.

select
'L' as RECTYPE,
identity(int, 1, 1) as JELINENO,
max(i.jeno) + 1 as JENO,
'AJE' as JECODE,

--build the fiscal year, when month = October, November or December add 1 to actual year

FY = datepart(year, GetDate()) + (datepart(month, GetDate()) + 2) / 12,

-- The PD starts in April and increases by 1 each month

PD = (datepart(month, GetDate()) + 8) % 12 + 1,

'1' as SUBPD,

'N' as REVFLAG,

'Journal Entry from MAXIMO' as HEADDESC,

--If the issuetype is a return the AMOUNT is a negative value, multiply by -1

AMOUNT = abs(m.linecost)

into #temp

from matusetrans m, issuesreturns i

|||Dave -

You can't use an Aggregate Function on only one column without using a Group By for the other columns

So you either need to group by m.linecost OR put an Agrregate around it - like max() or min().

To simpify your query

select
max(i.jeno) + 1 as JENO, -- YOU USED MAX HERE
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
-- SO YOU EITHER NEED TO USE AN AGRREGRATE HERE OR
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
-- OR GROUP BY m.linecost HERE

Hope that helps

AWAL

HELP - insert record SQLExpress database

Hi! i ask you some help..I should build a simple INSERT FORM in a SQLExpress database..

but clicking ADD I have this error :

Incorrect syntax near '='.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near '='.

Source Error:

Line 38: conDatabase.Open()Line 39:Line 40: cmdInsert.ExecuteNonQuery()Line 41: Line 42: conDatabase.Close()

some solution?

the source code:

<%

@.PageLanguage="VB"Debug="true"%>

<%

@.ImportNamespace="System.Data"%>

<%

@.ImportNamespace="System.Data.SqlClient"%>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">Sub Button_clic(ByVal sAsObject,ByVal eAs EventArgs)Dim conDatabaseAs SqlConnectionDim strInsertAsStringDim cmdInsertAs SqlCommandDim myExecuteQueryAsStringDim myExecuteCmdAs SqlCommand

conDatabase =

New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\tesi\App_Data\database.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")

strInsert =

"Insert pubblicazioni (Nome, Cognome, Titolo) Values = (@.Nome, @.Cognome, @.Titolo) "

cmdInsert =

New SqlCommand(strInsert, conDatabase)

cmdInsert.Parameters.Add(

"@.Nome", txtNome.Text)

cmdInsert.Parameters.Add(

"@.Cognome", txtCognome.Text)

cmdInsert.Parameters.Add(

"@.Titolo", txtTitolo.Text)

conDatabase.Open()

cmdInsert.ExecuteNonQuery()

conDatabase.Close()

Response.Redirect(

"success.html")EndSub

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headid="Head1"runat="server"><title>iNSERIMENTO</title><LINKhref="mauro.css"rel=stylesheet>

<

scripttype="text/javascript"language="javascript">

<!--

function

popopen(){

window.open(

"upload/upload.aspx","name"," toolbar=no,directories=no,menubar=no,width=300,height=300,top=100,left=150,resizable=no,scrollbars=yes");

}

// -->

</

script>

</

head>

<

bodybgcolor="#DFE5F2"style="font-size: 12pt"><formid="form1"runat="server"><div> <asp:LabelID="Label1"runat="server"BackColor="#8080FF"BorderColor="Black"ForeColor="Black"Height="29px"Text="FORM INSERIMENTO DOCUMENTO"Width="371px"Font-Bold="True"Font-Names="Verdana"Font-Size="14pt"Font-Underline="True"></asp:Label><br/><br/><br/><B><spanstyle="font-family: Verdana">NOME </span></B><asp:TextBoxID="txtNome"runat="server"></asp:TextBox> <B><spanstyle="font-family: Verdana">COGNOME </span></B><asp:TextBoxID="txtCognome"runat="server"></asp:TextBox><br/><br/><B><spanstyle="font-family: Verdana">TITOLO </span></B><asp:TextBoxID="txtTitolo"runat="server"></asp:TextBox><br/><br/>

<ahref="javascript:popopen()">CARICA DOCUMENTO</a><br/><br/>

<asp:ButtonID="Button1"runat="server"OnClick="Button_Clic"Text="ADD"Font-Bold="True"Font-Names="Verdana"Font-Size="12pt"Width="160px"/></div><br><br></form>

</

body>

</

html>Remove the "=" sign in the INSERT statement.|||

ndinakar:

Remove the "=" sign in the INSERT statement.

damn!ehehehe ; ) thanks you....

Help - Formulas with Functions across Databases

I'm trying to build a table that uses a formula with a function in another database on the same server group. Enterprise Manager validates the formula, then when I save the table, an ODBC error pops up stating that the function is an invalid object name. I'm calling the function in the formula as follows: ([OtherDatabase].[dbo].[FunctionName](parameters)). Any ideas?

-- JakeAre you running at least sp2 ?

-PatP|||Yes, I'm running SP2.

-- Jake|||I know that certain situations do force you to use cross-database DML. But if you're creating a calculated field (is that what you're trying to do?) with a function in another database this means that both databases, AND THEIR NAMES (!!!) must go together, right? Why don't you move the function to the same database as the table? And if the function does reference objects in another database, then it would be transparent for the table. If you want to apply "reusability" and you happen to have a table(-s) in another database that has a calculated field that utilizes the same function, then you can just reference that function within the function wrapper.|||The Database I'm working on is a template that will be replicated for numerous individual projects, so I'm trying to reduce the number of locations the generalized Function code will be stored in. Otherwise, I was curious why I can put ([OtherDatabase].[dbo].[FunctionName](parameters)) in a View (for example) and get the desired result, yet have it not recognize the object when said code is placed in the Formula field (even after it supposedly validates the code). If I'm missing something syntax-wise, please let me know.