Hi guys!
In my report model project Im trying to use the attributes of an entity.
This entity has (a number of) subroles coupled to it, which all are available
in the ReportBuilder-app once I drag any entity ground-level attribute on to
the canvas.
Unfortunately Im not able to put out the attributes in the subroles even
though they are visible among the ReportBuilder entity-props so they are not
hidden or anything.
I can drag these role-attribs out, but the mousepointer changes to that
forbidden-sign-thingie when I drag them out. For some reaseon RB does not
want to cooperate here, and Im not able to figure out why.
Anybody recognize this and care to comment?
DavidI found the reason; the cardinality was set to optional-many in both
directions of the role.
changing it to be optional-many/optional-one resolved this.
Hope this helps someone else!
David
"David Sundström" <DavidSundstrm@.discussions.microsoft.com> wrote in message
news:83DE441D-E013-4280-9427-4A7557BB1FB1@.microsoft.com...
> Hi guys!
> In my report model project Im trying to use the attributes of an entity.
> This entity has (a number of) subroles coupled to it, which all are
> available
> in the ReportBuilder-app once I drag any entity ground-level attribute on
> to
> the canvas.
> Unfortunately Im not able to put out the attributes in the subroles even
> though they are visible among the ReportBuilder entity-props so they are
> not
> hidden or anything.
> I can drag these role-attribs out, but the mousepointer changes to that
> forbidden-sign-thingie when I drag them out. For some reaseon RB does not
> want to cooperate here, and Im not able to figure out why.
> Anybody recognize this and care to comment?
> David
Showing posts with label number. Show all posts
Showing posts with label number. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Mod function in SQL 2000
I need to use the mod function on a numeric field with 2digits decimal.
I was told that SQL server does not support mod on a decimal number.
Is it correct? If yes, is there any way around this?
Thanks in advanceWhat does MOD do?|||Modulus, right?|||Originally posted by Brett Kaiser
What does MOD do?
I meant MODULO. (Provides the remainder of one number divided by another.)
Thanks|||modulo isn't designed to work with decimals regardless if it's SQL or any other language.
You can must CAST or CONVERT the decimal to INT.
regards,
hmscott|||You can multiply both numbers by 100 and cast them as INT, like this:
select cast(4.32 * 100 as int) & cast(5.87 * 100 as int)|||Or, you can have this function:
if object_id('dbo.fn_Modulo42Decimals') is not null
drop function dbo.fn_Modulo42Decimals
go
create function dbo.fn_Modulo42Decimals (
@.First decimal(18, 2),
@.Second decimal(18, 2) ) returns int
as begin
return (
cast(@.First * 100 as int) & cast(@.Second * 100 as int)
)
end
go
select dbo.fn_Modulo42Decimals(5995.32, 154.67)
Not sure if it actually works right, always hated math :D|||How about
DECLARE @.x decimal(15,2), @.y decimal(15,2)
SELECT @.x = 12345.67, @.y = 2.15
SELECT FLOOR(@.x/@.y)-@.x/@.y|||But this is my favorite (courtesy of Kaiser's Bar & Grill - franchise inquiries are welcome):
DECLARE @.Weekend datetime
SELECT @.Weekend =
CONVERT(datetime,
CONVERT(varchar(10),GetDate(),120) + ' 17:00:00')
SELECT
DATEDIFF(mi, GetDate(), @.Weekend)/60.00 As Hours_till_Margarittaville|||and how about when I have a value of 48 digits long? I declared a float(50),
but when I perform a division it keeps displaying an error message about the maximum precision of a numeric being 38. How can I do this or work around?|||38 digits is the limit for numeric calculations in SQL Server. I don't know of any way around that limit within SQL.
You've piqued my curiouisity though... Why on earth would you care ?!?! What the heck would you store with fifty digits of precision ?
-PatP|||well, it's a barcode consisting of an employee's company number, department, dates, ... It's in total 50 digits long, the first 48 containing data and the last 2 are check digits (modulo 97). I need to check if the data is correctly recognized by our scanning software and if not, present that record to the operator that has to manually correct it.|||Hmmm... The only thing that comes to my mind would be to treat the barcode as two NUMERIC(30,0) columns.
-PatP|||And how would you do that? I don't think I can just cust the value in 2 parts and perform some calculations on it?|||Company number?
Departement number?
These are not really numbers, they are codes. If you don't add it, subtract it, or multiply it, then it is a string, not a number. Store your barcode as a 50 character string.|||they are already stored as varchar, but I have to perform a check on the recognized number, so: (first 48 characters) modulo 97 = (last 2 characters)|||Applause, please...
create Function BigStringModulo(@.BigNumString varchar(500), @.Divisor int)
returns int
as
--Function BigStringModulo
--blindman, 1/18/2005
--Returns the Modulo value of a large number expressed as a string value.
--Does not verify that the string is a valid number!
begin
declare @.WorkingString as varchar(500)
declare @.CalcString as varchar(8)
set @.WorkingString = @.BigNumString
set @.CalcString = left(@.WorkingString, 8)
while @.CalcString > @.Divisor
begin
set @.WorkingString = right(@.WorkingString, len(@.WorkingString) - len(@.CalcString))
set @.WorkingString = cast((@.CalcString % @.Divisor) as varchar) + @.WorkingString
set @.CalcString = left(@.WorkingString, 8)
end
return cast(@.CalcString as int)
end|||This may take some experimentation, but if I remember right
n * m mod p = ((n mod p) * (m mod p)) mod p
and
n + m mod p = ((n mod p) + (m mod p)) mod p
In that case, you can break your number into two pieces
(substing (number, 1, 25) * 10^25 + substring(number 26, 22)) mod 97. Break down the individual sections, and you have an ugly but workable solution.|||I'll applaud when I figure out how it was done, but it looks like it works. Don't you ever think inside the box?
Oh, and my 10^25 should probably be 10^22, or whatever exponent is correct for breaking the number into two halves.|||Found a bug. Have to handle the case where there are multiple consecutive zeros:
drop Function BigStringModulo
go
create Function BigStringModulo(@.BigNumString varchar(500), @.Divisor int)
returns int
as
--Function BigStringModulo
--blindman, 1/18/2005
--Returns the Modulo value of a large number expressed as a string value.
--Does not verify that the string is a valid number!
--TestVariables
-- declare @.BigNumString varchar(500)
-- declare @.Divisor int
-- set @.BigNumString = '97000000000001'
-- set @.Divisor = 2
begin
declare @.WorkingString as varchar(500)
declare @.CalcString as varchar(7)
set @.WorkingString = @.BigNumString
set @.CalcString = left(@.WorkingString, 7)
while (@.CalcString > @.Divisor) or len(@.WorkingString) > len(@.Divisor)
begin
set @.WorkingString = right(@.WorkingString, len(@.WorkingString) - len(@.CalcString))
set @.WorkingString = cast((@.CalcString % @.Divisor) as varchar) + @.WorkingString
set @.CalcString = left(@.WorkingString, 7)
end
--select cast(@.CalcString as int)
return cast(@.CalcString as int)
end|||Thanks for your replies. I will take a look at it see if it works tommorow, as I am far past my working hours, so I'll be off home now :D|||I had to change
"while @.CalcString >= @.divisor"
in case the divisor and working string were the same, but it looks like you found the same bug, and solved it a different way.
I think I understand what you have done, now. Your solution is similar to mine, but you are tagging on the upper string at the beginning of the lower string, and effectively getting the power of ten included that way. Nice job, Blindman.|||I liked the algorithm, but as you found out the code was not heavily tested. It is tough to verify the results when there are no other functions that will duplicate it! Please let me know if you see any more bugs in it.|||Well, it works ... Thanks blindman and MCrowley|||That was a fun challenge! Thanks for posting it.sql
I was told that SQL server does not support mod on a decimal number.
Is it correct? If yes, is there any way around this?
Thanks in advanceWhat does MOD do?|||Modulus, right?|||Originally posted by Brett Kaiser
What does MOD do?
I meant MODULO. (Provides the remainder of one number divided by another.)
Thanks|||modulo isn't designed to work with decimals regardless if it's SQL or any other language.
You can must CAST or CONVERT the decimal to INT.
regards,
hmscott|||You can multiply both numbers by 100 and cast them as INT, like this:
select cast(4.32 * 100 as int) & cast(5.87 * 100 as int)|||Or, you can have this function:
if object_id('dbo.fn_Modulo42Decimals') is not null
drop function dbo.fn_Modulo42Decimals
go
create function dbo.fn_Modulo42Decimals (
@.First decimal(18, 2),
@.Second decimal(18, 2) ) returns int
as begin
return (
cast(@.First * 100 as int) & cast(@.Second * 100 as int)
)
end
go
select dbo.fn_Modulo42Decimals(5995.32, 154.67)
Not sure if it actually works right, always hated math :D|||How about
DECLARE @.x decimal(15,2), @.y decimal(15,2)
SELECT @.x = 12345.67, @.y = 2.15
SELECT FLOOR(@.x/@.y)-@.x/@.y|||But this is my favorite (courtesy of Kaiser's Bar & Grill - franchise inquiries are welcome):
DECLARE @.Weekend datetime
SELECT @.Weekend =
CONVERT(datetime,
CONVERT(varchar(10),GetDate(),120) + ' 17:00:00')
SELECT
DATEDIFF(mi, GetDate(), @.Weekend)/60.00 As Hours_till_Margarittaville|||and how about when I have a value of 48 digits long? I declared a float(50),
but when I perform a division it keeps displaying an error message about the maximum precision of a numeric being 38. How can I do this or work around?|||38 digits is the limit for numeric calculations in SQL Server. I don't know of any way around that limit within SQL.
You've piqued my curiouisity though... Why on earth would you care ?!?! What the heck would you store with fifty digits of precision ?
-PatP|||well, it's a barcode consisting of an employee's company number, department, dates, ... It's in total 50 digits long, the first 48 containing data and the last 2 are check digits (modulo 97). I need to check if the data is correctly recognized by our scanning software and if not, present that record to the operator that has to manually correct it.|||Hmmm... The only thing that comes to my mind would be to treat the barcode as two NUMERIC(30,0) columns.
-PatP|||And how would you do that? I don't think I can just cust the value in 2 parts and perform some calculations on it?|||Company number?
Departement number?
These are not really numbers, they are codes. If you don't add it, subtract it, or multiply it, then it is a string, not a number. Store your barcode as a 50 character string.|||they are already stored as varchar, but I have to perform a check on the recognized number, so: (first 48 characters) modulo 97 = (last 2 characters)|||Applause, please...
create Function BigStringModulo(@.BigNumString varchar(500), @.Divisor int)
returns int
as
--Function BigStringModulo
--blindman, 1/18/2005
--Returns the Modulo value of a large number expressed as a string value.
--Does not verify that the string is a valid number!
begin
declare @.WorkingString as varchar(500)
declare @.CalcString as varchar(8)
set @.WorkingString = @.BigNumString
set @.CalcString = left(@.WorkingString, 8)
while @.CalcString > @.Divisor
begin
set @.WorkingString = right(@.WorkingString, len(@.WorkingString) - len(@.CalcString))
set @.WorkingString = cast((@.CalcString % @.Divisor) as varchar) + @.WorkingString
set @.CalcString = left(@.WorkingString, 8)
end
return cast(@.CalcString as int)
end|||This may take some experimentation, but if I remember right
n * m mod p = ((n mod p) * (m mod p)) mod p
and
n + m mod p = ((n mod p) + (m mod p)) mod p
In that case, you can break your number into two pieces
(substing (number, 1, 25) * 10^25 + substring(number 26, 22)) mod 97. Break down the individual sections, and you have an ugly but workable solution.|||I'll applaud when I figure out how it was done, but it looks like it works. Don't you ever think inside the box?
Oh, and my 10^25 should probably be 10^22, or whatever exponent is correct for breaking the number into two halves.|||Found a bug. Have to handle the case where there are multiple consecutive zeros:
drop Function BigStringModulo
go
create Function BigStringModulo(@.BigNumString varchar(500), @.Divisor int)
returns int
as
--Function BigStringModulo
--blindman, 1/18/2005
--Returns the Modulo value of a large number expressed as a string value.
--Does not verify that the string is a valid number!
--TestVariables
-- declare @.BigNumString varchar(500)
-- declare @.Divisor int
-- set @.BigNumString = '97000000000001'
-- set @.Divisor = 2
begin
declare @.WorkingString as varchar(500)
declare @.CalcString as varchar(7)
set @.WorkingString = @.BigNumString
set @.CalcString = left(@.WorkingString, 7)
while (@.CalcString > @.Divisor) or len(@.WorkingString) > len(@.Divisor)
begin
set @.WorkingString = right(@.WorkingString, len(@.WorkingString) - len(@.CalcString))
set @.WorkingString = cast((@.CalcString % @.Divisor) as varchar) + @.WorkingString
set @.CalcString = left(@.WorkingString, 7)
end
--select cast(@.CalcString as int)
return cast(@.CalcString as int)
end|||Thanks for your replies. I will take a look at it see if it works tommorow, as I am far past my working hours, so I'll be off home now :D|||I had to change
"while @.CalcString >= @.divisor"
in case the divisor and working string were the same, but it looks like you found the same bug, and solved it a different way.
I think I understand what you have done, now. Your solution is similar to mine, but you are tagging on the upper string at the beginning of the lower string, and effectively getting the power of ten included that way. Nice job, Blindman.|||I liked the algorithm, but as you found out the code was not heavily tested. It is tough to verify the results when there are no other functions that will duplicate it! Please let me know if you see any more bugs in it.|||Well, it works ... Thanks blindman and MCrowley|||That was a fun challenge! Thanks for posting it.sql
Monday, March 19, 2012
Missing zeros
I'm trying to report on the number of tickets created by day for 21 days, but
when there are no tickets logged on a day, i need a 0. I have been trying to
get my query to return 0's for every day of the 21 days, but that hasn't
worked. I have tried to get the Matrix to show 21 days, but that hasn't
worked either. To add complexity, i need to graph this matrix, so i cannot
simply use a sub-report. (otherwise it would be done.)
A subset of the results from the query are as follows:
1/5/2006 4
1/6/2006 5
1/8/2006 3
1/20/2006 1
What i need is:
1/4/2006 0
1/5/2006 4
1/6/2006 5
1/7/2006 0
1/8/2006 3
1/9/2006 0
1/20/2006 1
Back in my access days, i just ran the maketable query, then used VB to
'pad' in as many zero values as needed, and then ran the report off the table.
Any suggestions?Where do you get the data from? Where does the numbers come from - are you
summing in your query or is that done automagically somewhere in the
database? And where are the dates and the numbers connected?
My guess is that you have to make your SQL query return all 21 lines. If you
use an outer join for dates and numbers, and do a Case test for the numbers,
you should get all your rows.
Kaisa M. Lindahl
"Brent Maloney" <BrentMaloney@.discussions.microsoft.com> wrote in message
news:7C5F489A-68D9-4681-88B9-324B77104AC7@.microsoft.com...
> I'm trying to report on the number of tickets created by day for 21 days,
> but
> when there are no tickets logged on a day, i need a 0. I have been trying
> to
> get my query to return 0's for every day of the 21 days, but that hasn't
> worked. I have tried to get the Matrix to show 21 days, but that hasn't
> worked either. To add complexity, i need to graph this matrix, so i
> cannot
> simply use a sub-report. (otherwise it would be done.)
> A subset of the results from the query are as follows:
> 1/5/2006 4
> 1/6/2006 5
> 1/8/2006 3
> 1/20/2006 1
> What i need is:
> 1/4/2006 0
> 1/5/2006 4
> 1/6/2006 5
> 1/7/2006 0
> 1/8/2006 3
> 1/9/2006 0
> 1/20/2006 1
> Back in my access days, i just ran the maketable query, then used VB to
> 'pad' in as many zero values as needed, and then ran the report off the
> table.
> Any suggestions?|||If there are no rows for days with no tickets and you are using a query like..
select date, sum(ticketsales) from table group by date
you will get no rows for dates with no tickets... One way to do this would
be to create another table with one for each date /... Then your query could
be
select dt.date, sum(isnull(ticketsales,0)) from table inner join dttable dt
on dt.date = table.dt group by dt.date
You will get a row for each date...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Kaisa M. Lindahl" wrote:
> Where do you get the data from? Where does the numbers come from - are you
> summing in your query or is that done automagically somewhere in the
> database? And where are the dates and the numbers connected?
> My guess is that you have to make your SQL query return all 21 lines. If you
> use an outer join for dates and numbers, and do a Case test for the numbers,
> you should get all your rows.
> Kaisa M. Lindahl
> "Brent Maloney" <BrentMaloney@.discussions.microsoft.com> wrote in message
> news:7C5F489A-68D9-4681-88B9-324B77104AC7@.microsoft.com...
> > I'm trying to report on the number of tickets created by day for 21 days,
> > but
> > when there are no tickets logged on a day, i need a 0. I have been trying
> > to
> > get my query to return 0's for every day of the 21 days, but that hasn't
> > worked. I have tried to get the Matrix to show 21 days, but that hasn't
> > worked either. To add complexity, i need to graph this matrix, so i
> > cannot
> > simply use a sub-report. (otherwise it would be done.)
> >
> > A subset of the results from the query are as follows:
> > 1/5/2006 4
> > 1/6/2006 5
> > 1/8/2006 3
> > 1/20/2006 1
> >
> > What i need is:
> > 1/4/2006 0
> > 1/5/2006 4
> > 1/6/2006 5
> > 1/7/2006 0
> > 1/8/2006 3
> > 1/9/2006 0
> > 1/20/2006 1
> >
> > Back in my access days, i just ran the maketable query, then used VB to
> > 'pad' in as many zero values as needed, and then ran the report off the
> > table.
> >
> > Any suggestions?
>
>
when there are no tickets logged on a day, i need a 0. I have been trying to
get my query to return 0's for every day of the 21 days, but that hasn't
worked. I have tried to get the Matrix to show 21 days, but that hasn't
worked either. To add complexity, i need to graph this matrix, so i cannot
simply use a sub-report. (otherwise it would be done.)
A subset of the results from the query are as follows:
1/5/2006 4
1/6/2006 5
1/8/2006 3
1/20/2006 1
What i need is:
1/4/2006 0
1/5/2006 4
1/6/2006 5
1/7/2006 0
1/8/2006 3
1/9/2006 0
1/20/2006 1
Back in my access days, i just ran the maketable query, then used VB to
'pad' in as many zero values as needed, and then ran the report off the table.
Any suggestions?Where do you get the data from? Where does the numbers come from - are you
summing in your query or is that done automagically somewhere in the
database? And where are the dates and the numbers connected?
My guess is that you have to make your SQL query return all 21 lines. If you
use an outer join for dates and numbers, and do a Case test for the numbers,
you should get all your rows.
Kaisa M. Lindahl
"Brent Maloney" <BrentMaloney@.discussions.microsoft.com> wrote in message
news:7C5F489A-68D9-4681-88B9-324B77104AC7@.microsoft.com...
> I'm trying to report on the number of tickets created by day for 21 days,
> but
> when there are no tickets logged on a day, i need a 0. I have been trying
> to
> get my query to return 0's for every day of the 21 days, but that hasn't
> worked. I have tried to get the Matrix to show 21 days, but that hasn't
> worked either. To add complexity, i need to graph this matrix, so i
> cannot
> simply use a sub-report. (otherwise it would be done.)
> A subset of the results from the query are as follows:
> 1/5/2006 4
> 1/6/2006 5
> 1/8/2006 3
> 1/20/2006 1
> What i need is:
> 1/4/2006 0
> 1/5/2006 4
> 1/6/2006 5
> 1/7/2006 0
> 1/8/2006 3
> 1/9/2006 0
> 1/20/2006 1
> Back in my access days, i just ran the maketable query, then used VB to
> 'pad' in as many zero values as needed, and then ran the report off the
> table.
> Any suggestions?|||If there are no rows for days with no tickets and you are using a query like..
select date, sum(ticketsales) from table group by date
you will get no rows for dates with no tickets... One way to do this would
be to create another table with one for each date /... Then your query could
be
select dt.date, sum(isnull(ticketsales,0)) from table inner join dttable dt
on dt.date = table.dt group by dt.date
You will get a row for each date...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Kaisa M. Lindahl" wrote:
> Where do you get the data from? Where does the numbers come from - are you
> summing in your query or is that done automagically somewhere in the
> database? And where are the dates and the numbers connected?
> My guess is that you have to make your SQL query return all 21 lines. If you
> use an outer join for dates and numbers, and do a Case test for the numbers,
> you should get all your rows.
> Kaisa M. Lindahl
> "Brent Maloney" <BrentMaloney@.discussions.microsoft.com> wrote in message
> news:7C5F489A-68D9-4681-88B9-324B77104AC7@.microsoft.com...
> > I'm trying to report on the number of tickets created by day for 21 days,
> > but
> > when there are no tickets logged on a day, i need a 0. I have been trying
> > to
> > get my query to return 0's for every day of the 21 days, but that hasn't
> > worked. I have tried to get the Matrix to show 21 days, but that hasn't
> > worked either. To add complexity, i need to graph this matrix, so i
> > cannot
> > simply use a sub-report. (otherwise it would be done.)
> >
> > A subset of the results from the query are as follows:
> > 1/5/2006 4
> > 1/6/2006 5
> > 1/8/2006 3
> > 1/20/2006 1
> >
> > What i need is:
> > 1/4/2006 0
> > 1/5/2006 4
> > 1/6/2006 5
> > 1/7/2006 0
> > 1/8/2006 3
> > 1/9/2006 0
> > 1/20/2006 1
> >
> > Back in my access days, i just ran the maketable query, then used VB to
> > 'pad' in as many zero values as needed, and then ran the report off the
> > table.
> >
> > Any suggestions?
>
>
Friday, March 9, 2012
Missing SET keyword.
Using the following update command I get the following message
UPDATE Last, First, [Card Number], [Phone Number], IDKey
FROM dbo.LibUserS
error:
Missing SET keyword.
Unable to parse query text.
incorrect syntax near ','
thats correct. The update statement requires the fields to update as well as on which record you want to update. Example:
UPDATE [TableName]
SET [FieldName] = someNewValue
WHERE [fieldName] = SomeValue
typically:
UPDATE [dbo.LibUserS]
SET Last = @.p1,
SET First = @.p2,
SET [Card Number] = @.p3,
SET [Phone Number] = @.p4
WHERE IDKey = @.IDValue
the @.parameter is the parameter you supply in the query on the command object (OleDbCommand or SqlCommand, whichever database connection/driver you are using) so it can take those values and replace them with the @.parameter "placeholder" if you like.
|||Helped, perfect, Thanks!
Wednesday, March 7, 2012
missing or invalid option
here is my code:
SET SERVEROUTPUT ON
DECLARE
numbrows NUMBER (2) := '&numbrows';
name s_dept.name%TYPE;
CURSOR c_emp IS
SELECT last_name,dept_id
FROM s_emp;
TYPE last_dept_table_type IS TABLE OF
c_emp%ROWTYPE
INDEX BY BINARY_INTEGER;
last_dept_table last_dept_table_type;
CURSOR c_dept IS
SELECT name,id
FROM s_dept;
TYPE dept_table_type IS TABLE OF
c_dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_table dept_table_type;
BEGIN
OPEN c_dept;
OPEN c_emp;
FOR i IN 1..numbrows LOOP
FETCH c_emp INTO last_dept_table(i);
EXIT WHEN c_emp%NOTFOUND;
FETCH c_dept INTO dept_table(i);
EXIT WHEN c_emp%NOTFOUND;
IF last_dept_table(i).dept_id = dept_table(i).id THEN
DBMS_OUPUT.PUT_LINE (last_dept_table(i).last_name||' '||last_dept_table(i).dept_id||' '||dept_table(i).name);
END IF;
END LOOP;
CLOSE c_emp;
CLOSE c_dept;
END;
/
and this is my problem:
Enter value for numbrows: 5
old 3: numbrows NUMBER (2) := '&numbrows
new 3: numbrows NUMBER (2) := '5';
SET SERVEROUTPUT ON
*
ERROR at line 1:
ORA-00922: missing or invalid option
Can anybody help?1) Are you running this in SQL Plus, or some other tool? SET SERVEROUTPUT ON is a SQL Plus command.
2) If you are using SQL Plus, what version of SQL Plus, e.g.:
SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003
When I run your code I get:
Enter value for numbrows:
old 2: numbrows NUMBER (2) := '&numbrows';
new 2: numbrows NUMBER (2) := '';
Then of course I get lots of errors, as I don't have the right tables. But note that the &numbrows substitution happens on line 2 not 3, which is correct because SET SERVEROUTPUT ON is not part of the PL/SQL being run.|||I am running this in SQL Plus oracle 9i
Do you know why I am getting this error?
Originally posted by andrewst
1) Are you running this in SQL Plus, or some other tool? SET SERVEROUTPUT ON is a SQL Plus command.
2) If you are using SQL Plus, what version of SQL Plus, e.g.:
SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003
When I run your code I get:
Enter value for numbrows:
old 2: numbrows NUMBER (2) := '&numbrows';
new 2: numbrows NUMBER (2) := '';
Then of course I get lots of errors, as I don't have the right tables. But note that the &numbrows substitution happens on line 2 not 3, which is correct because SET SERVEROUTPUT ON is not part of the PL/SQL being run.|||Originally posted by bbk
I am running this in SQL Plus oracle 9i
Do you know why I am getting this error?
No, I don't. As I said, I DON'T get the error when I run the same script in SQL Plus 8.0.5.0.0
NOTE: I'm talking about the SQL PLUS version here, not the Oracle version. I mean the very top banner:
SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Try leaving a blank line between the SET SERVEROUTPUT ON command and the DECLARE. At the moment your SQL Plus seems to think the SET command is an invalid part of the PL/SQL block, whereas my version manages to recognise it as a separate SET command.|||Sorry about that :
Release 9.2.0.1.0 - Production on Fri Feb 21 12:53:24 2003
(c) 1982, 2002, Oracle Corporation. All rights reserved.
I tried leaving a blank line and now i get the following msg:
Enter value for numbrows: 5
old 2: numbrows NUMBER (2) := '&numbrows';
new 2: numbrows NUMBER (2) := '5';
DBMS_OUPUT.PUT_LINE (last_dept_table(i).last_name||' '||last_dept_table(i).dept_id||' '||dept_
*
ERROR at line 27:
ORA-06550: line 27, column 7:
PLS-00201: identifier 'DBMS_OUPUT.PUT_LINE' must be declared
ORA-06550: line 27, column 7:
PL/SQL: Statement ignored
Originally posted by andrewst
No, I don't. As I said, I DON'T get the error when I run the same script in SQL Plus 8.0.5.0.0
NOTE: I'm talking about the SQL PLUS version here, not the Oracle version. I mean the very top banner:
SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Try leaving a blank line between the SET SERVEROUTPUT ON command and the DECLARE. At the moment your SQL Plus seems to think the SET command is an invalid part of the PL/SQL block, whereas my version manages to recognise it as a separate SET command.|||Yes, well DBMS_OUPUT is a typo, isn't it!
SET SERVEROUTPUT ON
DECLARE
numbrows NUMBER (2) := '&numbrows';
name s_dept.name%TYPE;
CURSOR c_emp IS
SELECT last_name,dept_id
FROM s_emp;
TYPE last_dept_table_type IS TABLE OF
c_emp%ROWTYPE
INDEX BY BINARY_INTEGER;
last_dept_table last_dept_table_type;
CURSOR c_dept IS
SELECT name,id
FROM s_dept;
TYPE dept_table_type IS TABLE OF
c_dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_table dept_table_type;
BEGIN
OPEN c_dept;
OPEN c_emp;
FOR i IN 1..numbrows LOOP
FETCH c_emp INTO last_dept_table(i);
EXIT WHEN c_emp%NOTFOUND;
FETCH c_dept INTO dept_table(i);
EXIT WHEN c_emp%NOTFOUND;
IF last_dept_table(i).dept_id = dept_table(i).id THEN
DBMS_OUPUT.PUT_LINE (last_dept_table(i).last_name||' '||last_dept_table(i).dept_id||' '||dept_table(i).name);
END IF;
END LOOP;
CLOSE c_emp;
CLOSE c_dept;
END;
/
and this is my problem:
Enter value for numbrows: 5
old 3: numbrows NUMBER (2) := '&numbrows
new 3: numbrows NUMBER (2) := '5';
SET SERVEROUTPUT ON
*
ERROR at line 1:
ORA-00922: missing or invalid option
Can anybody help?1) Are you running this in SQL Plus, or some other tool? SET SERVEROUTPUT ON is a SQL Plus command.
2) If you are using SQL Plus, what version of SQL Plus, e.g.:
SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003
When I run your code I get:
Enter value for numbrows:
old 2: numbrows NUMBER (2) := '&numbrows';
new 2: numbrows NUMBER (2) := '';
Then of course I get lots of errors, as I don't have the right tables. But note that the &numbrows substitution happens on line 2 not 3, which is correct because SET SERVEROUTPUT ON is not part of the PL/SQL being run.|||I am running this in SQL Plus oracle 9i
Do you know why I am getting this error?
Originally posted by andrewst
1) Are you running this in SQL Plus, or some other tool? SET SERVEROUTPUT ON is a SQL Plus command.
2) If you are using SQL Plus, what version of SQL Plus, e.g.:
SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003
When I run your code I get:
Enter value for numbrows:
old 2: numbrows NUMBER (2) := '&numbrows';
new 2: numbrows NUMBER (2) := '';
Then of course I get lots of errors, as I don't have the right tables. But note that the &numbrows substitution happens on line 2 not 3, which is correct because SET SERVEROUTPUT ON is not part of the PL/SQL being run.|||Originally posted by bbk
I am running this in SQL Plus oracle 9i
Do you know why I am getting this error?
No, I don't. As I said, I DON'T get the error when I run the same script in SQL Plus 8.0.5.0.0
NOTE: I'm talking about the SQL PLUS version here, not the Oracle version. I mean the very top banner:
SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Try leaving a blank line between the SET SERVEROUTPUT ON command and the DECLARE. At the moment your SQL Plus seems to think the SET command is an invalid part of the PL/SQL block, whereas my version manages to recognise it as a separate SET command.|||Sorry about that :
Release 9.2.0.1.0 - Production on Fri Feb 21 12:53:24 2003
(c) 1982, 2002, Oracle Corporation. All rights reserved.
I tried leaving a blank line and now i get the following msg:
Enter value for numbrows: 5
old 2: numbrows NUMBER (2) := '&numbrows';
new 2: numbrows NUMBER (2) := '5';
DBMS_OUPUT.PUT_LINE (last_dept_table(i).last_name||' '||last_dept_table(i).dept_id||' '||dept_
*
ERROR at line 27:
ORA-06550: line 27, column 7:
PLS-00201: identifier 'DBMS_OUPUT.PUT_LINE' must be declared
ORA-06550: line 27, column 7:
PL/SQL: Statement ignored
Originally posted by andrewst
No, I don't. As I said, I DON'T get the error when I run the same script in SQL Plus 8.0.5.0.0
NOTE: I'm talking about the SQL PLUS version here, not the Oracle version. I mean the very top banner:
SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Try leaving a blank line between the SET SERVEROUTPUT ON command and the DECLARE. At the moment your SQL Plus seems to think the SET command is an invalid part of the PL/SQL block, whereas my version manages to recognise it as a separate SET command.|||Yes, well DBMS_OUPUT is a typo, isn't it!
Missing Operators Error
I am getting the following error when trying to post data into SQL:
Description: Syntax error (missing operator) in query
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine
I am using a custom query in FrontPage:
INSERT INTO Results (Name, Email, Comments, File) VALUES
('::Name::', '::Email::', '::Comments::', '::File::')
It looks ok to me but not sure why I am getting the above error. Any
ideas?The error message doesn't come from SQL Server. If you are using SQL Server as a back-end, you might
want to use Profiler to see what SQL is submitted to the database engine. In any event, you should
check this out in a group focused on either Jet or FrontPage (since these are the applications using
SQL Server in this case).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<bjorgenson@.charter.net> wrote in message
news:1142880890.534952.11320@.g10g2000cwb.googlegroups.com...
>I am getting the following error when trying to post data into SQL:
> Description: Syntax error (missing operator) in query
> Number: -2147217900 (0x80040E14)
> Source: Microsoft JET Database Engine
> I am using a custom query in FrontPage:
> INSERT INTO Results (Name, Email, Comments, File) VALUES
> ('::Name::', '::Email::', '::Comments::', '::File::')
> It looks ok to me but not sure why I am getting the above error. Any
> ideas?
>
Description: Syntax error (missing operator) in query
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine
I am using a custom query in FrontPage:
INSERT INTO Results (Name, Email, Comments, File) VALUES
('::Name::', '::Email::', '::Comments::', '::File::')
It looks ok to me but not sure why I am getting the above error. Any
ideas?The error message doesn't come from SQL Server. If you are using SQL Server as a back-end, you might
want to use Profiler to see what SQL is submitted to the database engine. In any event, you should
check this out in a group focused on either Jet or FrontPage (since these are the applications using
SQL Server in this case).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<bjorgenson@.charter.net> wrote in message
news:1142880890.534952.11320@.g10g2000cwb.googlegroups.com...
>I am getting the following error when trying to post data into SQL:
> Description: Syntax error (missing operator) in query
> Number: -2147217900 (0x80040E14)
> Source: Microsoft JET Database Engine
> I am using a custom query in FrontPage:
> INSERT INTO Results (Name, Email, Comments, File) VALUES
> ('::Name::', '::Email::', '::Comments::', '::File::')
> It looks ok to me but not sure why I am getting the above error. Any
> ideas?
>
Saturday, February 25, 2012
Missing Header Footer in
Hey all-
We have a number of reports that have headers and footers in HTML and PDF, but when exporting to Excel the headers and footers disappear.
Has anyone else seen this happen? Any ideas on how to re-enable the headers?
Thanks in advance,
Tristan
Tristan, did you find a way to do this? I am in a very similar position where the footers are not displayed.Thanks,
AJ
Subscribe to:
Posts (Atom)