| T O P I C R E V I E W |
| juliom |
Posted - 01/02/2006 : 15:54:01
Hi Igor...
I'm creating an SQL Report which has 22 filters.
Some of those filters (I'll refer to this kind of filter as Type A) are based on the table that has the main data for the report, so the SQL for those filters looks like this:
[code]
SELECT DISTINCT t.c432, t.c432
FROM T56 t
UNION
SELECT TOP 1 'TODOS', 'TODOS'
FROM T56
[/code]
The value returned by filters such as the one above is a string, I'm handling this as a varchar(20)
Other filters (Type B) are based on other tables and I'm selecting a code and a description to create the filter, those filters' code looks like this
[code]
SELECT f.FRE_CODIGO, f.FRE_DESCRIPCION
FROM FRE_FREGADO f
UNION
SELECT TOP 1 100, 'TODOS'
FROM FRE_FREGADO
[/code]
The value returned by filters such as the one above depends on the datatype of the first column selectd, I'm handling this according to its datatype, some are int, some are numeric and some are float.
I created an store procedure that receives the parameters (the results from the filters) and creates the WHERE part of the select and executes it.
The problem is with Type B filters when the index of the name is greater than 9 (so they have to be referred as ::10, ::11, etc...
I'm calling the store procedure and this, for example works fine:
[code]
EXEC dbo.[CONSULTA PERFILES PERSONAS] '::1','::2','::3',::4,::5,::6,::7,::8,::9,'::10',::9,'::12','::13',::9,'::15',::9,'::17',::9,'::19','::20',::9,'::22'
[/code]
(I'm using the defalult value for every filter which is either 'TODOS' OR 'TODAS' - this is ALL in spanish - so the store procedure doesn´t generate the WHERE part of the select)
As you can see the highest filter result, not between qotation marks is 9 (which appears several times) if I change any of those for ::11, ::14, ::16, ::18 or ::21 I get this error message:
[code]
Error converting data type nvarchar to numeric
[/code]
I can enclose the reference to the filter results in quotation marks but i get the same error.
I gess this is a bug...
Sorry.. there was an error in a code tag so some of the Post wasn't shown... I'm going to try an get the Sentence with the SQLProfiler any ways..
Thanks
Julio
|
| 8 L A T E S T R E P L I E S (Newest First) |
| admin |
Posted - 01/24/2006 : 00:15:20
Please try Build 98, Jan 22, 2006.
http://www.reportportal.com/forum/topic.asp?TOPIC_ID=247 |
| juliom |
Posted - 01/19/2006 : 13:30:19
Igor:
Any idea when next release will be out??? |
| juliom |
Posted - 01/10/2006 : 07:50:39
Thanks Igor... When do you think the next release will be out???
quote:
Originally posted by admin
Julio,
I understand the problem now. It happens when there more that 9 filter in a query. The problem is that filter ::1 interferes with ::10 ,::11, ::12 and so on.
The fix is quite simple (SqlReport.vb line 210) and will be available in the next release.
|
| admin |
Posted - 01/06/2006 : 17:48:27
Julio,
I understand the problem now. It happens when there more that 9 filter in a query. The problem is that filter ::1 interferes with ::10 ,::11, ::12 and so on.
The fix is quite simple (SqlReport.vb line 210) and will be available in the next release.
|
| juliom |
Posted - 01/06/2006 : 08:20:29
Hi Igor:
This is what I get, using the quotes for the 11th parameter...
[code]
SQL: EXEC dbo.[CONSULTA PERFILES PERSONAS] 'TODOS','TODOS','TODOS',100,100,100,100,100,100,'TODOS0','TODOS1','TODOS2','TODOS3',100,'TODOS5',100,'TODOS7',100,'TODOS9','TODOS0',100,'TODOS2'
[/code]
I marked in red the 10th and 11th parameter and as you can see, I'm getting TODOSX as the resutl, where X is the last digit of the reference for the filter result.. For the 1st to the 9th filters I'm getting 'TODOS','TODOS','TODOS',100,100,100,100,100 and 100 respectively thats why I said before that
quote:
It seems to me that some how the system is taking the filter's display value (not the return value) and attaches the last digit of the name to it and returns that.. This only happens for those filters above the 9th (10th, 11th, 12th, etc.)
Thanks,
Julio.
quote:
Originally posted by admin
Please try to put quotes around 11th parameter like so:
EXEC dbo.[CONSULTA PERFILES PERSONAS] '::1','::2','::3',::4,::5,::6,::7,::8,::9,'::10','::9','::12','::13',::9,'::15',::9,'::17',::9,'::19','::20',::9,'::22'
|
| admin |
Posted - 01/05/2006 : 20:51:22
Please try to put quotes around 11th parameter like so:
EXEC dbo.[CONSULTA PERFILES PERSONAS] '::1','::2','::3',::4,::5,::6,::7,::8,::9,'::10','::9','::12','::13',::9,'::15',::9,'::17',::9,'::19','::20',::9,'::22'
|
| juliom |
Posted - 01/05/2006 : 09:49:50
Hi Igor:
This is the SQL statement generated by the report:
[code]
EXEC dbo.[CONSULTA PERFILES PERSONAS] 'TODOS','TODOS','TODOS',100,100,100,100,100,100,'TODOS0',TODOS1,'TODOS2','TODOS3',100,'TODOS5',100,'TODOS7',100,'TODOS9','TODOS0',100,'TODOS2'
[/code]
I've marked in red what I think is important. Those are the 10th and 11th filters. As you can see, their values are 'TODOS0' and TODOS1, respectively. It seems to me that some how the system is taking the filter's display value (not the return value) and attaches the last digit of the name to it and returns that.. This only happens for those filters above the 9th (10th, 11th, 12th, etc.). Of course,we get a conversion error when SQL tries to convert TODOS1 to a number.
Thanks in advance for your help...
Julio.
quote:
Originally posted by juliom
Hi Igor...
I'm creating an SQL Report which has 22 filters.
Some of those filters (I'll refer to this kind of filter as Type A) are based on the table that has the main data for the report, so the SQL for those filters looks like this:
[code]
SELECT DISTINCT t.c432, t.c432
FROM T56 t
UNION
SELECT TOP 1 'TODOS', 'TODOS'
FROM T56
[/code]
The value returned by filters such as the one above is a string, I'm handling this as a varchar(20)
Other filters (Type B) are based on other tables and I'm selecting a code and a description to create the filter, those filters' code looks like this
[code]
SELECT f.FRE_CODIGO, f.FRE_DESCRIPCION
FROM FRE_FREGADO f
UNION
SELECT TOP 1 100, 'TODOS'
FROM FRE_FREGADO
[/code]
The value returned by filters such as the one above depends on the datatype of the first column selectd, I'm handling this according to its datatype, some are int, some are numeric and some are float.
I created an store procedure that receives the parameters (the results from the filters) and creates the WHERE part of the select and executes it.
The problem is with Type B filters when the index of the name is greater than 9 (so they have to be referred as ::10, ::11, etc...
I'm calling the store procedure and this, for example works fine:
[code]
EXEC dbo.[CONSULTA PERFILES PERSONAS] '::1','::2','::3',::4,::5,::6,::7,::8,::9,'::10',::9,'::12','::13',::9,'::15',::9,'::17',::9,'::19','::20',::9,'::22'
[/code]
(I'm using the defalult value for every filter which is either 'TODOS' OR 'TODAS' - this is ALL in spanish - so the store procedure doesn´t generate the WHERE part of the select)
As you can see the highest filter result, not between qotation marks is 9 (which appears several times) if I change any of those for ::11, ::14, ::16, ::18 or ::21 I get this error message:
[code]
Error converting data type nvarchar to numeric
[/code]
I can enclose the reference to the filter results in quotation marks but i get the same error.
I gess this is a bug...
Sorry.. there was an error in a code tag so some of the Post wasn't shown... I'm going to try an get the Sentence with the SQLProfiler any ways..
Thanks
Julio
|
| admin |
Posted - 01/03/2006 : 21:05:57
Your error suggests that there a conversion problem.
Did you see an SQL statement returned? If yes, can you please show it?
If no, please try to use the SQL profiler to get the SQL statement with the problem?
|
|
|
Report Portal
