Hello,
i need to get information about users that belong to the local
administrators operating system group.
I execute the following procedure and got my information:
exec master..xp_cmdshell 'net localgroup administrators'
The question is that i need to get this information without the NULLs and
the output lines that appear.
Is it possible? Is there any other way to get the same information?
Thanks and best regards.
You can also try using the following in query analyzer if
the builtin\administrators group hasn't been removed:
exec master..xp_logininfo 'BUILTIN\Administrators',
'MEMBERS'
-Sue
On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:
>Hello,
>i need to get information about users that belong to the local
>administrators operating system group.
>I execute the following procedure and got my information:
>exec master..xp_cmdshell 'net localgroup administrators'
>The question is that i need to get this information without the NULLs and
>the output lines that appear.
>Is it possible? Is there any other way to get the same information?
>Thanks and best regards.
|||> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible?
You can use INSERT ... EXEC to insert the results into a table and filter as
needed:
CREATE TABLE #Results(OutputLine varchar(8000))
INSERT INTO #Results
EXEC master..xp_cmdshell 'net localgroup administrators'
SELECT *
FROM #Results
WHERE OutputLine IS NOT NULL AND
OutputLine NOT LIKE '-%' AND
OutputLine <> 'The command completed successfully.'
DROP TABLE #Results
> Is there any other way to get the same information?
Consider using application code rather than Transact-SQL. API's like WMI
are much more robust for this sort of thing.
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:0DEE4267-7967-4552-8F42-7A8E0E6BCCD6@.microsoft.com...
> Hello,
> i need to get information about users that belong to the local
> administrators operating system group.
> I execute the following procedure and got my information:
> exec master..xp_cmdshell 'net localgroup administrators'
> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible? Is there any other way to get the same information?
> Thanks and best regards.
|||Thanks Sue,
Best regards.
"Sue Hoegemeier" wrote:
> You can also try using the following in query analyzer if
> the builtin\administrators group hasn't been removed:
> exec master..xp_logininfo 'BUILTIN\Administrators',
> 'MEMBERS'
> -Sue
> On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
> <CCJM@.discussions.microsoft.com> wrote:
>
>
Showing posts with label following. Show all posts
Showing posts with label following. Show all posts
Tuesday, March 27, 2012
Check OS Users
Hello,
i need to get information about users that belong to the local
administrators operating system group.
I execute the following procedure and got my information:
exec master..xp_cmdshell 'net localgroup administrators'
The question is that i need to get this information without the NULLs and
the output lines that appear.
Is it possible? Is there any other way to get the same information?
Thanks and best regards.You can also try using the following in query analyzer if
the builtin\administrators group hasn't been removed:
exec master..xp_logininfo 'BUILTIN\Administrators',
'MEMBERS'
-Sue
On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:
>Hello,
>i need to get information about users that belong to the local
>administrators operating system group.
>I execute the following procedure and got my information:
>exec master..xp_cmdshell 'net localgroup administrators'
>The question is that i need to get this information without the NULLs and
>the output lines that appear.
>Is it possible? Is there any other way to get the same information?
>Thanks and best regards.|||> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible?
You can use INSERT ... EXEC to insert the results into a table and filter as
needed:
CREATE TABLE #Results(OutputLine varchar(8000))
INSERT INTO #Results
EXEC master..xp_cmdshell 'net localgroup administrators'
SELECT *
FROM #Results
WHERE OutputLine IS NOT NULL AND
OutputLine NOT LIKE '-%' AND
OutputLine <> 'The command completed successfully.'
DROP TABLE #Results
> Is there any other way to get the same information?
Consider using application code rather than Transact-SQL. API's like WMI
are much more robust for this sort of thing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:0DEE4267-7967-4552-8F42-7A8E0E6BCCD6@.microsoft.com...
> Hello,
> i need to get information about users that belong to the local
> administrators operating system group.
> I execute the following procedure and got my information:
> exec master..xp_cmdshell 'net localgroup administrators'
> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible? Is there any other way to get the same information?
> Thanks and best regards.|||Thanks Sue,
Best regards.
"Sue Hoegemeier" wrote:
> You can also try using the following in query analyzer if
> the builtin\administrators group hasn't been removed:
> exec master..xp_logininfo 'BUILTIN\Administrators',
> 'MEMBERS'
> -Sue
> On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
> <CCJM@.discussions.microsoft.com> wrote:
> >Hello,
> >
> >i need to get information about users that belong to the local
> >administrators operating system group.
> >
> >I execute the following procedure and got my information:
> >
> >exec master..xp_cmdshell 'net localgroup administrators'
> >
> >The question is that i need to get this information without the NULLs and
> >the output lines that appear.
> >Is it possible? Is there any other way to get the same information?
> >
> >Thanks and best regards.
>
i need to get information about users that belong to the local
administrators operating system group.
I execute the following procedure and got my information:
exec master..xp_cmdshell 'net localgroup administrators'
The question is that i need to get this information without the NULLs and
the output lines that appear.
Is it possible? Is there any other way to get the same information?
Thanks and best regards.You can also try using the following in query analyzer if
the builtin\administrators group hasn't been removed:
exec master..xp_logininfo 'BUILTIN\Administrators',
'MEMBERS'
-Sue
On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:
>Hello,
>i need to get information about users that belong to the local
>administrators operating system group.
>I execute the following procedure and got my information:
>exec master..xp_cmdshell 'net localgroup administrators'
>The question is that i need to get this information without the NULLs and
>the output lines that appear.
>Is it possible? Is there any other way to get the same information?
>Thanks and best regards.|||> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible?
You can use INSERT ... EXEC to insert the results into a table and filter as
needed:
CREATE TABLE #Results(OutputLine varchar(8000))
INSERT INTO #Results
EXEC master..xp_cmdshell 'net localgroup administrators'
SELECT *
FROM #Results
WHERE OutputLine IS NOT NULL AND
OutputLine NOT LIKE '-%' AND
OutputLine <> 'The command completed successfully.'
DROP TABLE #Results
> Is there any other way to get the same information?
Consider using application code rather than Transact-SQL. API's like WMI
are much more robust for this sort of thing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:0DEE4267-7967-4552-8F42-7A8E0E6BCCD6@.microsoft.com...
> Hello,
> i need to get information about users that belong to the local
> administrators operating system group.
> I execute the following procedure and got my information:
> exec master..xp_cmdshell 'net localgroup administrators'
> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible? Is there any other way to get the same information?
> Thanks and best regards.|||Thanks Sue,
Best regards.
"Sue Hoegemeier" wrote:
> You can also try using the following in query analyzer if
> the builtin\administrators group hasn't been removed:
> exec master..xp_logininfo 'BUILTIN\Administrators',
> 'MEMBERS'
> -Sue
> On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
> <CCJM@.discussions.microsoft.com> wrote:
> >Hello,
> >
> >i need to get information about users that belong to the local
> >administrators operating system group.
> >
> >I execute the following procedure and got my information:
> >
> >exec master..xp_cmdshell 'net localgroup administrators'
> >
> >The question is that i need to get this information without the NULLs and
> >the output lines that appear.
> >Is it possible? Is there any other way to get the same information?
> >
> >Thanks and best regards.
>
Check OS Users
Hello,
i need to get information about users that belong to the local
administrators operating system group.
I execute the following procedure and got my information:
exec master..xp_cmdshell 'net localgroup administrators'
The question is that i need to get this information without the NULLs and
the output lines that appear.
Is it possible? Is there any other way to get the same information?
Thanks and best regards.You can also try using the following in query analyzer if
the builtin\administrators group hasn't been removed:
exec master..xp_logininfo 'BUILTIN\Administrators',
'MEMBERS'
-Sue
On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:
>Hello,
>i need to get information about users that belong to the local
>administrators operating system group.
>I execute the following procedure and got my information:
>exec master..xp_cmdshell 'net localgroup administrators'
>The question is that i need to get this information without the NULLs and
>the output lines that appear.
>Is it possible? Is there any other way to get the same information?
>Thanks and best regards.|||> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible?
You can use INSERT ... EXEC to insert the results into a table and filter as
needed:
CREATE TABLE #Results(OutputLine varchar(8000))
INSERT INTO #Results
EXEC master..xp_cmdshell 'net localgroup administrators'
SELECT *
FROM #Results
WHERE OutputLine IS NOT NULL AND
OutputLine NOT LIKE '-%' AND
OutputLine <> 'The command completed successfully.'
DROP TABLE #Results
> Is there any other way to get the same information?
Consider using application code rather than Transact-SQL. API's like WMI
are much more robust for this sort of thing.
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:0DEE4267-7967-4552-8F42-7A8E0E6BCCD6@.microsoft.com...
> Hello,
> i need to get information about users that belong to the local
> administrators operating system group.
> I execute the following procedure and got my information:
> exec master..xp_cmdshell 'net localgroup administrators'
> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible? Is there any other way to get the same information?
> Thanks and best regards.|||Thanks Sue,
Best regards.
"Sue Hoegemeier" wrote:
> You can also try using the following in query analyzer if
> the builtin\administrators group hasn't been removed:
> exec master..xp_logininfo 'BUILTIN\Administrators',
> 'MEMBERS'
> -Sue
> On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
> <CCJM@.discussions.microsoft.com> wrote:
>
>
i need to get information about users that belong to the local
administrators operating system group.
I execute the following procedure and got my information:
exec master..xp_cmdshell 'net localgroup administrators'
The question is that i need to get this information without the NULLs and
the output lines that appear.
Is it possible? Is there any other way to get the same information?
Thanks and best regards.You can also try using the following in query analyzer if
the builtin\administrators group hasn't been removed:
exec master..xp_logininfo 'BUILTIN\Administrators',
'MEMBERS'
-Sue
On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:
>Hello,
>i need to get information about users that belong to the local
>administrators operating system group.
>I execute the following procedure and got my information:
>exec master..xp_cmdshell 'net localgroup administrators'
>The question is that i need to get this information without the NULLs and
>the output lines that appear.
>Is it possible? Is there any other way to get the same information?
>Thanks and best regards.|||> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible?
You can use INSERT ... EXEC to insert the results into a table and filter as
needed:
CREATE TABLE #Results(OutputLine varchar(8000))
INSERT INTO #Results
EXEC master..xp_cmdshell 'net localgroup administrators'
SELECT *
FROM #Results
WHERE OutputLine IS NOT NULL AND
OutputLine NOT LIKE '-%' AND
OutputLine <> 'The command completed successfully.'
DROP TABLE #Results
> Is there any other way to get the same information?
Consider using application code rather than Transact-SQL. API's like WMI
are much more robust for this sort of thing.
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:0DEE4267-7967-4552-8F42-7A8E0E6BCCD6@.microsoft.com...
> Hello,
> i need to get information about users that belong to the local
> administrators operating system group.
> I execute the following procedure and got my information:
> exec master..xp_cmdshell 'net localgroup administrators'
> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible? Is there any other way to get the same information?
> Thanks and best regards.|||Thanks Sue,
Best regards.
"Sue Hoegemeier" wrote:
> You can also try using the following in query analyzer if
> the builtin\administrators group hasn't been removed:
> exec master..xp_logininfo 'BUILTIN\Administrators',
> 'MEMBERS'
> -Sue
> On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
> <CCJM@.discussions.microsoft.com> wrote:
>
>
Sunday, March 25, 2012
Check on Replication
i,
I have the following scenario:
One server (Publisher) is in one domain "Domain A" and the other server
(Subscriber) is in a different domain "Domain B"; the mail server is in
"Domain A" for which the subscriber is unable to send email notifications
toward any user in "Domain A".
Because of the above it's difficult for us to monitor the status of the
replication and so far we just learn that the replication is down generally
hours later it's happened.
Is there any way to check out the status of the replication from the
publisher?query the msrepl_errors table on the distribution database. If you are using
pull replication you could query the msdistribution_view to check to see if
commands are pooling. You can find this view in the distribution database.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"FJC" <FJC@.discussions.microsoft.com> wrote in message
news:37B0A8A1-FC72-443A-AC15-14A76CE3ADCD@.microsoft.com...
> i,
> I have the following scenario:
> One server (Publisher) is in one domain "Domain A" and the other server
> (Subscriber) is in a different domain "Domain B"; the mail server is in
> "Domain A" for which the subscriber is unable to send email notifications
> toward any user in "Domain A".
> Because of the above it's difficult for us to monitor the status of the
> replication and so far we just learn that the replication is down
> generally
> hours later it's happened.
> Is there any way to check out the status of the replication from the
> publisher?|||query the msrepl_errors table on the distribution database. If you are using
pull replication you could query the msdistribution_view to check to see if
commands are pooling. You can find this view in the distribution database.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"FJC" <FJC@.discussions.microsoft.com> wrote in message
news:37B0A8A1-FC72-443A-AC15-14A76CE3ADCD@.microsoft.com...
> i,
> I have the following scenario:
> One server (Publisher) is in one domain "Domain A" and the other server
> (Subscriber) is in a different domain "Domain B"; the mail server is in
> "Domain A" for which the subscriber is unable to send email notifications
> toward any user in "Domain A".
> Because of the above it's difficult for us to monitor the status of the
> replication and so far we just learn that the replication is down
> generally
> hours later it's happened.
> Is there any way to check out the status of the replication from the
> publisher?
I have the following scenario:
One server (Publisher) is in one domain "Domain A" and the other server
(Subscriber) is in a different domain "Domain B"; the mail server is in
"Domain A" for which the subscriber is unable to send email notifications
toward any user in "Domain A".
Because of the above it's difficult for us to monitor the status of the
replication and so far we just learn that the replication is down generally
hours later it's happened.
Is there any way to check out the status of the replication from the
publisher?query the msrepl_errors table on the distribution database. If you are using
pull replication you could query the msdistribution_view to check to see if
commands are pooling. You can find this view in the distribution database.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"FJC" <FJC@.discussions.microsoft.com> wrote in message
news:37B0A8A1-FC72-443A-AC15-14A76CE3ADCD@.microsoft.com...
> i,
> I have the following scenario:
> One server (Publisher) is in one domain "Domain A" and the other server
> (Subscriber) is in a different domain "Domain B"; the mail server is in
> "Domain A" for which the subscriber is unable to send email notifications
> toward any user in "Domain A".
> Because of the above it's difficult for us to monitor the status of the
> replication and so far we just learn that the replication is down
> generally
> hours later it's happened.
> Is there any way to check out the status of the replication from the
> publisher?|||query the msrepl_errors table on the distribution database. If you are using
pull replication you could query the msdistribution_view to check to see if
commands are pooling. You can find this view in the distribution database.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"FJC" <FJC@.discussions.microsoft.com> wrote in message
news:37B0A8A1-FC72-443A-AC15-14A76CE3ADCD@.microsoft.com...
> i,
> I have the following scenario:
> One server (Publisher) is in one domain "Domain A" and the other server
> (Subscriber) is in a different domain "Domain B"; the mail server is in
> "Domain A" for which the subscriber is unable to send email notifications
> toward any user in "Domain A".
> Because of the above it's difficult for us to monitor the status of the
> replication and so far we just learn that the replication is down
> generally
> hours later it's happened.
> Is there any way to check out the status of the replication from the
> publisher?
Check on Replication
i,
I have the following scenario:
One server (Publisher) is in one domain "Domain A" and the other server
(Subscriber) is in a different domain "Domain B"; the mail server is in
"Domain A" for which the subscriber is unable to send email notifications
toward any user in "Domain A".
Because of the above it's difficult for us to monitor the status of the
replication and so far we just learn that the replication is down generally
hours later it's happened.
Is there any way to check out the status of the replication from the
publisher?query the msrepl_errors table on the distribution database. If you are using
pull replication you could query the msdistribution_view to check to see if
commands are pooling. You can find this view in the distribution database.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"FJC" <FJC@.discussions.microsoft.com> wrote in message
news:37B0A8A1-FC72-443A-AC15-14A76CE3ADCD@.microsoft.com...
> i,
> I have the following scenario:
> One server (Publisher) is in one domain "Domain A" and the other server
> (Subscriber) is in a different domain "Domain B"; the mail server is in
> "Domain A" for which the subscriber is unable to send email notifications
> toward any user in "Domain A".
> Because of the above it's difficult for us to monitor the status of the
> replication and so far we just learn that the replication is down
> generally
> hours later it's happened.
> Is there any way to check out the status of the replication from the
> publisher?
I have the following scenario:
One server (Publisher) is in one domain "Domain A" and the other server
(Subscriber) is in a different domain "Domain B"; the mail server is in
"Domain A" for which the subscriber is unable to send email notifications
toward any user in "Domain A".
Because of the above it's difficult for us to monitor the status of the
replication and so far we just learn that the replication is down generally
hours later it's happened.
Is there any way to check out the status of the replication from the
publisher?query the msrepl_errors table on the distribution database. If you are using
pull replication you could query the msdistribution_view to check to see if
commands are pooling. You can find this view in the distribution database.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"FJC" <FJC@.discussions.microsoft.com> wrote in message
news:37B0A8A1-FC72-443A-AC15-14A76CE3ADCD@.microsoft.com...
> i,
> I have the following scenario:
> One server (Publisher) is in one domain "Domain A" and the other server
> (Subscriber) is in a different domain "Domain B"; the mail server is in
> "Domain A" for which the subscriber is unable to send email notifications
> toward any user in "Domain A".
> Because of the above it's difficult for us to monitor the status of the
> replication and so far we just learn that the replication is down
> generally
> hours later it's happened.
> Is there any way to check out the status of the replication from the
> publisher?
Thursday, March 22, 2012
check if recordset is empy
Hello,
Using Oracle 8.1.7
In the following package how do I determine whether a value for pROW_ID is returned or not (within the package)?
Merry X-mas,
Ronan van Riet
CREATE OR REPLACE PACKAGE VALIDATE_USER_PKG
AS
PROCEDURE VALIDATEUSER
( pEMAIL_ADDR in S_CONTACT.EMAIL_ADDR%TYPE,
pPWD in S_CONTACT.PWD%TYPE,
pROW_ID out S_CONTACT.ROW_ID%TYPE,
pFST_NAME out S_CONTACT.FST_NAME%TYPE,
pLAST_NAME out S_CONTACT.LAST_NAME%TYPE,
pCOMPANYNAME out S_ORG_EXT.DESC_TEXT%TYPE
);
END VALIDATE_USER_PKG;
/
CREATE OR REPLACE PACKAGE BODY VALIDATE_USER_PKG
AS
PROCEDURE VALIDATEUSER
( pEMAIL_ADDR in S_CONTACT.EMAIL_ADDR%TYPE,
pPWD in S_CONTACT.PWD%TYPE,
pROW_ID out S_CONTACT.ROW_ID%TYPE,
pFST_NAME out S_CONTACT.FST_NAME%TYPE,
pLAST_NAME out S_CONTACT.LAST_NAME%TYPE,
pCOMPANYNAME out S_ORG_EXT.DESC_TEXT%TYPE
)
IS
-- Purpose: Authenticate user with Oracle DB
-- MODIFICATION HISTORY
-- --- -- --------------
-- RVR 03-MAY-2003 Created
BEGIN
SELECT S_CONTACT.ROW_ID, S_CONTACT.FST_NAME, S_CONTACT.LAST_NAME, S_ORG_EXT.DESC_TEXT
INTO pROW_ID, pFST_NAME, pLAST_NAME, pCOMPANYNAME
FROM S_CONTACT, S_ORG_EXT
WHERE S_CONTACT.COMPANYID = S_ORG_EXT.ROW_ID
AND S_CONTACT.EMAIL_ADDR = pEMAIL_ADDR
AND S_CONTACT.PWD = pPWD;
--EXCEPTION
-- WHEN exception_name THEN
-- statements ;
END; -- VALIDATEUSER
END VALIDATE_USER_pkg;handle the execption, such as...
BEGIN
your PL/SQL statement
EXCEPTION
WHEN OTHERS THEN
pRowId := NULL;
END;
IF pRowId IS NULL THEN
trapTheError();
END IF;
Where "trapThe Error()" is the built-in that handles the exception,
or simply handle it thru the exception. Your preference.|||Hi,
Use %FOUND or %NOTFOUND or %ROWCOUNT .|||Originally posted by ronanvanriet
SELECT S_CONTACT.ROW_ID, S_CONTACT.FST_NAME, S_CONTACT.LAST_NAME, S_ORG_EXT.DESC_TEXT
INTO pROW_ID, pFST_NAME, pLAST_NAME, pCOMPANYNAME
FROM S_CONTACT, S_ORG_EXT
WHERE S_CONTACT.COMPANYID = S_ORG_EXT.ROW_ID
AND S_CONTACT.EMAIL_ADDR = pEMAIL_ADDR
AND S_CONTACT.PWD = pPWD;
you should not select directly into variables
use a cursor and
EXIT WHEN cursor_name%NOTFOUND;
Using Oracle 8.1.7
In the following package how do I determine whether a value for pROW_ID is returned or not (within the package)?
Merry X-mas,
Ronan van Riet
CREATE OR REPLACE PACKAGE VALIDATE_USER_PKG
AS
PROCEDURE VALIDATEUSER
( pEMAIL_ADDR in S_CONTACT.EMAIL_ADDR%TYPE,
pPWD in S_CONTACT.PWD%TYPE,
pROW_ID out S_CONTACT.ROW_ID%TYPE,
pFST_NAME out S_CONTACT.FST_NAME%TYPE,
pLAST_NAME out S_CONTACT.LAST_NAME%TYPE,
pCOMPANYNAME out S_ORG_EXT.DESC_TEXT%TYPE
);
END VALIDATE_USER_PKG;
/
CREATE OR REPLACE PACKAGE BODY VALIDATE_USER_PKG
AS
PROCEDURE VALIDATEUSER
( pEMAIL_ADDR in S_CONTACT.EMAIL_ADDR%TYPE,
pPWD in S_CONTACT.PWD%TYPE,
pROW_ID out S_CONTACT.ROW_ID%TYPE,
pFST_NAME out S_CONTACT.FST_NAME%TYPE,
pLAST_NAME out S_CONTACT.LAST_NAME%TYPE,
pCOMPANYNAME out S_ORG_EXT.DESC_TEXT%TYPE
)
IS
-- Purpose: Authenticate user with Oracle DB
-- MODIFICATION HISTORY
-- --- -- --------------
-- RVR 03-MAY-2003 Created
BEGIN
SELECT S_CONTACT.ROW_ID, S_CONTACT.FST_NAME, S_CONTACT.LAST_NAME, S_ORG_EXT.DESC_TEXT
INTO pROW_ID, pFST_NAME, pLAST_NAME, pCOMPANYNAME
FROM S_CONTACT, S_ORG_EXT
WHERE S_CONTACT.COMPANYID = S_ORG_EXT.ROW_ID
AND S_CONTACT.EMAIL_ADDR = pEMAIL_ADDR
AND S_CONTACT.PWD = pPWD;
--EXCEPTION
-- WHEN exception_name THEN
-- statements ;
END; -- VALIDATEUSER
END VALIDATE_USER_pkg;handle the execption, such as...
BEGIN
your PL/SQL statement
EXCEPTION
WHEN OTHERS THEN
pRowId := NULL;
END;
IF pRowId IS NULL THEN
trapTheError();
END IF;
Where "trapThe Error()" is the built-in that handles the exception,
or simply handle it thru the exception. Your preference.|||Hi,
Use %FOUND or %NOTFOUND or %ROWCOUNT .|||Originally posted by ronanvanriet
SELECT S_CONTACT.ROW_ID, S_CONTACT.FST_NAME, S_CONTACT.LAST_NAME, S_ORG_EXT.DESC_TEXT
INTO pROW_ID, pFST_NAME, pLAST_NAME, pCOMPANYNAME
FROM S_CONTACT, S_ORG_EXT
WHERE S_CONTACT.COMPANYID = S_ORG_EXT.ROW_ID
AND S_CONTACT.EMAIL_ADDR = pEMAIL_ADDR
AND S_CONTACT.PWD = pPWD;
you should not select directly into variables
use a cursor and
EXIT WHEN cursor_name%NOTFOUND;
Check if record exists
Hello,
I created the following SQL script to check if a record exists:
IF (EXISTS (SELECT LevelName FROM dbo.by27_Levels WHERE LOWER(@.LevelName) = LOWER(LevelName)))
Return (1)
ELSE
Return (0)
And I also found in a web page another solution:
IF EXISTS(SELECT 1 FROM TABLENAME WHERE LevelName=@.LevelName)
SELECT 1
ELSE
SELECT 0
- Which approach should I use?
- Why "SELECT 1 FROM"?
- And when should I use SELECT or RETURN?
All I need is to know if the record exists ... nothing else.
I will use this procedure on an ASP.NET 2.0 / C# web site.
I am not sure if this important but anyway ...
Thank You,
Miguel
select 1 from table returns a value which is basically the same as selecting a column name when evaluating from the exists function. The difference is that 1 is a constant so the column name does not need to be looked up, and since you do not need the value of the column then select 1 can be used.
using return or select depends on what you are using to call the sql statement. If you use return then you need to look into the calls returns parameters. Using a select , you need to use a scalar or dataset return call. Most people use the select call because those calls are easier to handle but not necessarily more efficient
|||
In EXISTS you can use any of them but the result will be the same (it always look for first occurrence of value selected) I do not know about time of execution but I would prefer something like this
RETURN (CASE
when EXISTS (SELECT LevelName
FROM dbo.by27_Levels
WHERE LOWER(@.LevelName) = LOWER(LevelName))) then 1
else
0
end)
If it will be executed on SQL server and you server is Case insensitive you do not have to use LOWER and it will speed up a little.
Thanks
ozkary:
If you use return then you need to look into the calls returns parameters.
What do you mean to look the calls returns parameters?
Can you point to some info about it?
Thanks,.
Miguel
By default SQL Server is not case sensitive so the LOWER() is not needed.
If the LevelName is a unique key for the table I would avaoid using T-SQL and use a single generic SQL query:
SELECT COUNT(*) FROM dbo.by27_Levels WHERE @.LevelName = LevelName
RETURN ends the execution of the batch T-SQL and SELECT does not. Note any select results not stored in local variables will be output.
|||Some stored procedures could have return parameters they are defined with OUTPUT
create procedure TEST
@.tcParam1 as varchar(100) = NULL,
@.tcOutputParam as varchar(100) = NULL OUTPUT
AS
BEGIN
...
SET @.tcOutputParam= 'result'
END
and if you call it
declare @.oparam as varchar(100)
exec test 'Test valuee', @.oparam OUTPUT
you can get output value from procedure
RETURN always is returned by stored procedure and you can get it like:but it only integer, output parameter can be almost any type
EXEC @.result = test 'Test valuee', @.oparam OUTPUT
Thanks
|||yes, one usually uses parameters to call a stored procedure. Those parameters can have the following directions: INPUT, OUTPUT, RETURN.
To hadle a return value, one needs to add a return parameter to the call:
SqlCommand cmd = new SqlCommand("myProc", myConnection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters..Add("ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
add block to make the call
if using a reader make sure to close it before trying to read the return parameter
read the return parameter value
string value = cmd.Parameters.item["ReturnValue"].Value.Tostring();
for more info search on ParameterDirection.ReturnValue
I hope this helps.
Tuesday, March 20, 2012
Check for username
Hello,
I have the following Table:
CREATE TABLE [dbo].[tb_User] (
[usr_id] [int] IDENTITY (1, 1) NOT NULL ,
[usg_groupID] [int] NOT NULL ,
[usr_createdBy] [int] NOT NULL ,
[usr_isActive] [int] NOT NULL ,
[usr_dateCreated] [datetime] NOT NULL ,
[usr_userName] [varchar] (25) NULL ,
[usr_password] [varchar] (50) NULL ,
[usr_firstName] [varchar] (50) NULL ,
[usr_lastName] [varchar] (50) NULL ,
[usr_eMail] [varchar] (50) NULL ,
[usr_phoneNumber] [varchar] (50) NULL ,
[usr_notes] [varchar] (512) NULL
)
I want to set up a stored procedure that will take two parameters
(@.UserName varchar(50) and @.Password varchar(50)) that I want to use to
check for the following
1. Check to see that the username exisits and that the column isActive = 1
2. If step 1 is true, then check that the @.Password Parameter is the
same for the user
3. If step 2 above is true return 1
4. If step 1 about is false (either the username does not exist, of if
it does it's not active (isActive = 0) return 0
I am going to use the Return value in my C# application to warn the user
that either the username does not exist, or it's not active or the
password is wrong.
Anything anyone can provide me with will be greatly appreciated!It would be simple as this:
e.g.
create proc usp
@.user varchar(25),
@.pass varchar(50)
as
set nocount on
if exists(select 1 from tb_User where usr_userName=@.user and usr_isActive=1
and usr_password=@.pass)
return 0
else
return 1
go
-oj
"Ed_P." <ed_p@.no-email.com> wrote in message
news:eX3GmN0CFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have the following Table:
> CREATE TABLE [dbo].[tb_User] (
> [usr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [usg_groupID] [int] NOT NULL ,
> [usr_createdBy] [int] NOT NULL ,
> [usr_isActive] [int] NOT NULL ,
> [usr_dateCreated] [datetime] NOT NULL ,
> [usr_userName] [varchar] (25) NULL ,
> [usr_password] [varchar] (50) NULL ,
> [usr_firstName] [varchar] (50) NULL ,
> [usr_lastName] [varchar] (50) NULL ,
> [usr_eMail] [varchar] (50) NULL ,
> [usr_phoneNumber] [varchar] (50) NULL ,
> [usr_notes] [varchar] (512) NULL
> )
> I want to set up a stored procedure that will take two parameters
> (@.UserName varchar(50) and @.Password varchar(50)) that I want to use to
> check for the following
> 1. Check to see that the username exisits and that the column isActive = 1
> 2. If step 1 is true, then check that the @.Password Parameter is the same
> for the user
> 3. If step 2 above is true return 1
> 4. If step 1 about is false (either the username does not exist, of if it
> does it's not active (isActive = 0) return 0
> I am going to use the Return value in my C# application to warn the user
> that either the username does not exist, or it's not active or the
> password is wrong.
> Anything anyone can provide me with will be greatly appreciated!
I have the following Table:
CREATE TABLE [dbo].[tb_User] (
[usr_id] [int] IDENTITY (1, 1) NOT NULL ,
[usg_groupID] [int] NOT NULL ,
[usr_createdBy] [int] NOT NULL ,
[usr_isActive] [int] NOT NULL ,
[usr_dateCreated] [datetime] NOT NULL ,
[usr_userName] [varchar] (25) NULL ,
[usr_password] [varchar] (50) NULL ,
[usr_firstName] [varchar] (50) NULL ,
[usr_lastName] [varchar] (50) NULL ,
[usr_eMail] [varchar] (50) NULL ,
[usr_phoneNumber] [varchar] (50) NULL ,
[usr_notes] [varchar] (512) NULL
)
I want to set up a stored procedure that will take two parameters
(@.UserName varchar(50) and @.Password varchar(50)) that I want to use to
check for the following
1. Check to see that the username exisits and that the column isActive = 1
2. If step 1 is true, then check that the @.Password Parameter is the
same for the user
3. If step 2 above is true return 1
4. If step 1 about is false (either the username does not exist, of if
it does it's not active (isActive = 0) return 0
I am going to use the Return value in my C# application to warn the user
that either the username does not exist, or it's not active or the
password is wrong.
Anything anyone can provide me with will be greatly appreciated!It would be simple as this:
e.g.
create proc usp
@.user varchar(25),
@.pass varchar(50)
as
set nocount on
if exists(select 1 from tb_User where usr_userName=@.user and usr_isActive=1
and usr_password=@.pass)
return 0
else
return 1
go
-oj
"Ed_P." <ed_p@.no-email.com> wrote in message
news:eX3GmN0CFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have the following Table:
> CREATE TABLE [dbo].[tb_User] (
> [usr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [usg_groupID] [int] NOT NULL ,
> [usr_createdBy] [int] NOT NULL ,
> [usr_isActive] [int] NOT NULL ,
> [usr_dateCreated] [datetime] NOT NULL ,
> [usr_userName] [varchar] (25) NULL ,
> [usr_password] [varchar] (50) NULL ,
> [usr_firstName] [varchar] (50) NULL ,
> [usr_lastName] [varchar] (50) NULL ,
> [usr_eMail] [varchar] (50) NULL ,
> [usr_phoneNumber] [varchar] (50) NULL ,
> [usr_notes] [varchar] (512) NULL
> )
> I want to set up a stored procedure that will take two parameters
> (@.UserName varchar(50) and @.Password varchar(50)) that I want to use to
> check for the following
> 1. Check to see that the username exisits and that the column isActive = 1
> 2. If step 1 is true, then check that the @.Password Parameter is the same
> for the user
> 3. If step 2 above is true return 1
> 4. If step 1 about is false (either the username does not exist, of if it
> does it's not active (isActive = 0) return 0
> I am going to use the Return value in my C# application to warn the user
> that either the username does not exist, or it's not active or the
> password is wrong.
> Anything anyone can provide me with will be greatly appreciated!
Check for Temp Table
How do you check if a temp table exists?
I usually do the following for tables and Views:
if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME =
'EarningsDeductions')
DROP Table EarningsDeductions
if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME =
'EarningsWithRank')
DROP VIEW EarningsWithRank
But I can't seem to find out how to check for a temp Table.
I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
was there and whether there was a type code there, but there wasn't.
Thanks,
Tomtry this:
http://www.devx.com/tips/Tip/13938
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
> How do you check if a temp table exists?
> I usually do the following for tables and Views:
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME
> = 'EarningsDeductions')
> DROP Table EarningsDeductions
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME
> = 'EarningsWithRank')
> DROP VIEW EarningsWithRank
> But I can't seem to find out how to check for a temp Table.
> I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
> was there and whether there was a type code there, but there wasn't.
> Thanks,
> Tom
>
>|||or this :
if object_id('tempdb..#temp') is not null
print 'exists'
else
print 'not exists'
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
> How do you check if a temp table exists?
> I usually do the following for tables and Views:
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME
> = 'EarningsDeductions')
> DROP Table EarningsDeductions
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME
> = 'EarningsWithRank')
> DROP VIEW EarningsWithRank
> But I can't seem to find out how to check for a temp Table.
> I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
> was there and whether there was a type code there, but there wasn't.
> Thanks,
> Tom
>
>|||"Yosh" <yoshi@.nospam.com> wrote in message
news:%23%23mEoyxvFHA.708@.TK2MSFTNGP10.phx.gbl...
> or this :
> if object_id('tempdb..#temp') is not null
> print 'exists'
> else
> print 'not exists'
That would do what I wanted.
Thanks,
Tom
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
NAME
NAME
it
>
I usually do the following for tables and Views:
if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME =
'EarningsDeductions')
DROP Table EarningsDeductions
if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME =
'EarningsWithRank')
DROP VIEW EarningsWithRank
But I can't seem to find out how to check for a temp Table.
I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
was there and whether there was a type code there, but there wasn't.
Thanks,
Tomtry this:
http://www.devx.com/tips/Tip/13938
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
> How do you check if a temp table exists?
> I usually do the following for tables and Views:
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME
> = 'EarningsDeductions')
> DROP Table EarningsDeductions
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME
> = 'EarningsWithRank')
> DROP VIEW EarningsWithRank
> But I can't seem to find out how to check for a temp Table.
> I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
> was there and whether there was a type code there, but there wasn't.
> Thanks,
> Tom
>
>|||or this :
if object_id('tempdb..#temp') is not null
print 'exists'
else
print 'not exists'
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
> How do you check if a temp table exists?
> I usually do the following for tables and Views:
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME
> = 'EarningsDeductions')
> DROP Table EarningsDeductions
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME
> = 'EarningsWithRank')
> DROP VIEW EarningsWithRank
> But I can't seem to find out how to check for a temp Table.
> I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
> was there and whether there was a type code there, but there wasn't.
> Thanks,
> Tom
>
>|||"Yosh" <yoshi@.nospam.com> wrote in message
news:%23%23mEoyxvFHA.708@.TK2MSFTNGP10.phx.gbl...
> or this :
> if object_id('tempdb..#temp') is not null
> print 'exists'
> else
> print 'not exists'
That would do what I wanted.
Thanks,
Tom
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
NAME
NAME
it
>
Sunday, March 11, 2012
Check Contraint question
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + ']%'),
1;LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is the
re a way to have it not throw an error, but rather just remove the offending
characters if entered? ThanksNo, that's not what a constraint does.
You can perhaps use an instead-of trigger to achieve this functionality.
Conor
"Burma Jones" <somebody@.somedomain.not> wrote in message
news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
']%'),[LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is
there a way to have it not throw an error, but rather just remove the
offending characters if entered? Thanks|||No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.|||Since this is only a few thousand records, I'm not too worried about the
cost of using a trigger. Can you share an example, even pseudocode, showing
how to create a trigger which will remove those characters? Thanks
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:eu$9uj9cGHA.4932@.TK2MSFTNGP03.phx.gbl...
> No, that's not what a constraint does.
> You can perhaps use an instead-of trigger to achieve this functionality.
> Conor
> "Burma Jones" <somebody@.somedomain.not> wrote in message
> news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
> I have the following check constraint
> (isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
> ']%'),[LicensePlateNumber]),0) = 0)
> which works fine, throwing an error if those characters are entered. Is
> there a way to have it not throw an error, but rather just remove the
> offending characters if entered? Thanks
>|||On Wed, 10 May 2006 08:26:16 -0700, Burma Jones wrote:
>Since this is only a few thousand records, I'm not too worried about the
>cost of using a trigger. Can you share an example, even pseudocode, showin
g
>how to create a trigger which will remove those characters? Thanks
Hi Burma,
Here's a sample trigger that will remove the offending characters
silently:
CREATE TRIGGER YourTrigger
ON YourTable INSTEAD OF INSERT
AS
INSERT INTO YourTable (OtherColumns, LicensePlate)
SELECT OtherColumns,
REPLACE(REPLACE(REPLACE(REPLACE(LicenseP
late, ' ', ''), CHAR(9),
''), CHAR(10), ''), CHAR(13), ''), OtherColumns
FROM inserted
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + ']%'),
1;LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is the
re a way to have it not throw an error, but rather just remove the offending
characters if entered? ThanksNo, that's not what a constraint does.
You can perhaps use an instead-of trigger to achieve this functionality.
Conor
"Burma Jones" <somebody@.somedomain.not> wrote in message
news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
']%'),[LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is
there a way to have it not throw an error, but rather just remove the
offending characters if entered? Thanks|||No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.|||Since this is only a few thousand records, I'm not too worried about the
cost of using a trigger. Can you share an example, even pseudocode, showing
how to create a trigger which will remove those characters? Thanks
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:eu$9uj9cGHA.4932@.TK2MSFTNGP03.phx.gbl...
> No, that's not what a constraint does.
> You can perhaps use an instead-of trigger to achieve this functionality.
> Conor
> "Burma Jones" <somebody@.somedomain.not> wrote in message
> news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
> I have the following check constraint
> (isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
> ']%'),[LicensePlateNumber]),0) = 0)
> which works fine, throwing an error if those characters are entered. Is
> there a way to have it not throw an error, but rather just remove the
> offending characters if entered? Thanks
>|||On Wed, 10 May 2006 08:26:16 -0700, Burma Jones wrote:
>Since this is only a few thousand records, I'm not too worried about the
>cost of using a trigger. Can you share an example, even pseudocode, showin
g
>how to create a trigger which will remove those characters? Thanks
Hi Burma,
Here's a sample trigger that will remove the offending characters
silently:
CREATE TRIGGER YourTrigger
ON YourTable INSTEAD OF INSERT
AS
INSERT INTO YourTable (OtherColumns, LicensePlate)
SELECT OtherColumns,
REPLACE(REPLACE(REPLACE(REPLACE(LicenseP
late, ' ', ''), CHAR(9),
''), CHAR(10), ''), CHAR(13), ''), OtherColumns
FROM inserted
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
Check Contraint question
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + ']%'),[LicensePlateNumber]),0) =
0)
which works fine, throwing an error if those characters are entered. Is the
re a way to have it not throw an error, but rather just remove the offending
characters if entered? ThanksNo, that's not what a constraint does.
You can perhaps use an instead-of trigger to achieve this functionality.
Conor
"Burma Jones" <somebody@.somedomain.not> wrote in message
news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
']%'),[LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is
there a way to have it not throw an error, but rather just remove the
offending characters if entered? Thanks|||No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.|||Since this is only a few thousand records, I'm not too worried about the
cost of using a trigger. Can you share an example, even pseudocode, showing
how to create a trigger which will remove those characters? Thanks
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:eu$9uj9cGHA.4932@.TK2MSFTNGP03.phx.gbl...
> No, that's not what a constraint does.
> You can perhaps use an instead-of trigger to achieve this functionality.
> Conor
> "Burma Jones" <somebody@.somedomain.not> wrote in message
> news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
> I have the following check constraint
> (isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
> ']%'),[LicensePlateNumber]),0) = 0)
> which works fine, throwing an error if those characters are entered. Is
> there a way to have it not throw an error, but rather just remove the
> offending characters if entered? Thanks
>|||On Wed, 10 May 2006 08:26:16 -0700, Burma Jones wrote:
>Since this is only a few thousand records, I'm not too worried about the
>cost of using a trigger. Can you share an example, even pseudocode, showin
g
>how to create a trigger which will remove those characters? Thanks
Hi Burma,
Here's a sample trigger that will remove the offending characters
silently:
CREATE TRIGGER YourTrigger
ON YourTable INSTEAD OF INSERT
AS
INSERT INTO YourTable (OtherColumns, LicensePlate)
SELECT OtherColumns,
REPLACE(REPLACE(REPLACE(REPLACE(LicenseP
late, ' ', ''), CHAR(9),
''), CHAR(10), ''), CHAR(13), ''), OtherColumns
FROM inserted
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + ']%'),[LicensePlateNumber]),0) =
0)
which works fine, throwing an error if those characters are entered. Is the
re a way to have it not throw an error, but rather just remove the offending
characters if entered? ThanksNo, that's not what a constraint does.
You can perhaps use an instead-of trigger to achieve this functionality.
Conor
"Burma Jones" <somebody@.somedomain.not> wrote in message
news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
']%'),[LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is
there a way to have it not throw an error, but rather just remove the
offending characters if entered? Thanks|||No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.|||Since this is only a few thousand records, I'm not too worried about the
cost of using a trigger. Can you share an example, even pseudocode, showing
how to create a trigger which will remove those characters? Thanks
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:eu$9uj9cGHA.4932@.TK2MSFTNGP03.phx.gbl...
> No, that's not what a constraint does.
> You can perhaps use an instead-of trigger to achieve this functionality.
> Conor
> "Burma Jones" <somebody@.somedomain.not> wrote in message
> news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
> I have the following check constraint
> (isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
> ']%'),[LicensePlateNumber]),0) = 0)
> which works fine, throwing an error if those characters are entered. Is
> there a way to have it not throw an error, but rather just remove the
> offending characters if entered? Thanks
>|||On Wed, 10 May 2006 08:26:16 -0700, Burma Jones wrote:
>Since this is only a few thousand records, I'm not too worried about the
>cost of using a trigger. Can you share an example, even pseudocode, showin
g
>how to create a trigger which will remove those characters? Thanks
Hi Burma,
Here's a sample trigger that will remove the offending characters
silently:
CREATE TRIGGER YourTrigger
ON YourTable INSTEAD OF INSERT
AS
INSERT INTO YourTable (OtherColumns, LicensePlate)
SELECT OtherColumns,
REPLACE(REPLACE(REPLACE(REPLACE(LicenseP
late, ' ', ''), CHAR(9),
''), CHAR(10), ''), CHAR(13), ''), OtherColumns
FROM inserted
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
Check Constraints or Triggers
Hi, Im facing teh following situation:
This are just sample table names, but should do for discussing
purpouses.
Create table Invoice
(
InvoiceID Integer Not Null,
CustomerType Integer Not Null,
CustomerCode Integer Not Null,
Amount DECIMAL(10,2) Not Null,
.............
)
Create Table Type1Customer
(
CustomerCode Integer Not Null,
........................
)
Create Table Type2Customer
(
CustomerCode Integer Not Null,
........................
)
I need to add a way to restrict the CustomerType and CustomerCode,
in the Invoice table to the correct values.
This means that if customerType equals 1 the customerCode should be
checked against Type1Customer and if customerType equals 2 the
customerCode should be checked against Type2Customer.
I succesfully created a check constraint. That ensures that the valid
values exists when the rows in the Invoice table are inserted or
updated, but doesnt prevent from deleting records from tables
Type1Customer and Type2Customer that are referenced from the Invoice
table.
Are triggers the only way to go?
Thanks in advance
Sebastin streigerIn addition to Erland's suggestion,
I would recommend adding CustomerType to both Type1Customer and
Type2Customer, and adding CustomerType to their FK constraints|||(sebastian.streiger@.gmail.com) writes:
> This are just sample table names, but should do for discussing
> purpouses.
> Create table Invoice (
> InvoiceID Integer Not Null,
> CustomerType Integer Not Null,
> CustomerCode Integer Not Null,
> Amount DECIMAL(10,2) Not Null,
> ............. )
> Create Table Type1Customer (
> CustomerCode Integer Not Null,
> ....................... )
>
> Create Table Type2Customer (
> CustomerCode Integer Not Null,
> ....................... )
> I need to add a way to restrict the CustomerType and CustomerCode,
> in the Invoice table to the correct values.
> This means that if customerType equals 1 the customerCode should be
> checked against Type1Customer and if customerType equals 2 the
> customerCode should be checked against Type2Customer.
>...
> Are triggers the only way to go?
With that data model, yes. But is that really the right data model?
I would rather have a CustomerCode table which could look like this:
CREATE TABLE CustomerCode (
CustomerType integer NOT NULL,
CustomerCode integer NOT NULL,
CONSTRAINT pk_CustomerCode(CustomerType, CustomerCode))
Then Invoices could refer to this table, and so could the child
tables Type1Customer and Type2Customer.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland and AK:
Thank you for answering.
I DO agree that the model is no the best one that we can have. But due
to organizational issues Im not in position to change the tables
structures by now. So, Im trying to add constraints to ensure the
data consistency.
Thanks for your valuable feedback
Check Constraint Violation
I am getting a check constraint error on the following query.
INSERT TABLEA
( COL1, COL2, COL3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
returning 20000. All columns are of datatype INT as is the return value of
the function.
If i replace the function call with a literal 0, it works, but having the
function in there violates the constraint on COL3, despite the value still
being 0.
Does anyone have any idea why this is happening? I have tried making COL3 a
computed column, but can't have a constraint on a computed column, tried
making the select statement into a derived table and selecting from that int
o
my insert statement.
This is being done on SQL Server 2000 Enterprise.
Thank you
Clint ColefaxHi
What is the error that you received.
and what was displayed when you tried:
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:
> I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that i
nto
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||The error received was a violation of check constraint.
INSERT statement conflicted with COLUMN CHECK constraint...
Execute that select statement returns as expected
20000, -20000, 0
Thank you
Clint Colefax|||Hi
Can you try as
INSERT INTO TABLEA
SELECT dbo.Function1(),-20000, dbo.Function1() + (-20000)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:
> The error received was a violation of check constraint.
> INSERT statement conflicted with COLUMN CHECK constraint...
> Execute that select statement returns as expected
> 20000, -20000, 0
> Thank you
> Clint Colefax
>|||What data types are the columns and the function?
If they are not of an exact type, but e.g. "real", the third expression may
evaluate to a value slightly below 0, although it is displayed as 0. CAST to
integer to avoid this problem.
I hope this helps!
Martin
"Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
news:42D1D89D-9EA0-4F00-8F91-D8D55732C387@.microsoft.com...
>I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
> a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that
> into
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||On Mon, 30 May 2005 19:19:41 -0700, Clint Colefax wrote:
>I am getting a check constraint error on the following query.
>INSERT TABLEA
>( COL1, COL2, COL3)
>SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
>There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
>returning 20000. All columns are of datatype INT as is the return value of
>the function.
Hi Clint,
I could not reproduce this behaviour (see repro script below). Could you
post a repro script for me to run and reproduce the error?
>I have tried making COL3 a
>computed column, but can't have a constraint on a computed column,
If Col3 is always equal to Col1 - Col2, you should make it a computed
column. You can replace the check constraint wiuth the following
equivalent:
CHECK (Col1 >= Col2)
Here's the script I used to try to reproduce your problem, and the
output I got from it:
create table TableA
(Col1 int not null,
Col2 int not null,
Col3 int not null,
PRIMARY KEY (Col1),
CHECK (Col3 >= 0)
)
go
create function dbo.Function1()
returns int
as
begin
return 20000
end
go
INSERT TableA
( Col1, Col2, Col3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
go
select * from TableA
go
drop function dbo.Function1
go
drop table TableA
go
Col1 Col2 Col3
-- -- --
20000 -20000 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I gave the earlier example as I didn't want to post all of the infrastructur
e
around this problem. With the following cut down version, I am still able to
replicate the problem.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST
Thank you
Clint Colefax|||Sorry, I didn't think out my example very well, the following is code that
should reproduce the error.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST|||Thank you but all datatype are of INT, all match, even using a CAST or
CONVERT statement does not get around the problem (had previouslty attempted
).
Thank you for your input
Clint Colefax|||It's a bug, and a surprising one:
CREATE TABLE TEST (
a int not null,
b smalldatetime not null,
constraint finalv CHECK (a >= 0)
)
go
INSERT TEST(a) SELECT 1
Gives this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.TEST';
column does not allow nulls. INSERT fails.
The statement has been terminated.
Can you post the result of
SELECT @.@.version
so we can see what version you're running?
I verified this on 8.00.2039, and will report it to Microsoft.
SK
Clint Colefax wrote:
>there error is a check constraint violation for the FINAL_VALUE constraint.
>Server: Msg 547, Level 16, State 1, Line 1
>INSERT statement conflicted with COLUMN CHECK constraint
>'CK__test__FINAL_VALU__3AA27A0F'. The conflict occurred in database
>'LIPSDev', table 'test', column 'FINAL_VALUE'.
>The statement has been terminated.
>Thank you
>
INSERT TABLEA
( COL1, COL2, COL3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
returning 20000. All columns are of datatype INT as is the return value of
the function.
If i replace the function call with a literal 0, it works, but having the
function in there violates the constraint on COL3, despite the value still
being 0.
Does anyone have any idea why this is happening? I have tried making COL3 a
computed column, but can't have a constraint on a computed column, tried
making the select statement into a derived table and selecting from that int
o
my insert statement.
This is being done on SQL Server 2000 Enterprise.
Thank you
Clint ColefaxHi
What is the error that you received.
and what was displayed when you tried:
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:
> I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that i
nto
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||The error received was a violation of check constraint.
INSERT statement conflicted with COLUMN CHECK constraint...
Execute that select statement returns as expected
20000, -20000, 0
Thank you
Clint Colefax|||Hi
Can you try as
INSERT INTO TABLEA
SELECT dbo.Function1(),-20000, dbo.Function1() + (-20000)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:
> The error received was a violation of check constraint.
> INSERT statement conflicted with COLUMN CHECK constraint...
> Execute that select statement returns as expected
> 20000, -20000, 0
> Thank you
> Clint Colefax
>|||What data types are the columns and the function?
If they are not of an exact type, but e.g. "real", the third expression may
evaluate to a value slightly below 0, although it is displayed as 0. CAST to
integer to avoid this problem.
I hope this helps!
Martin
"Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
news:42D1D89D-9EA0-4F00-8F91-D8D55732C387@.microsoft.com...
>I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
> a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that
> into
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||On Mon, 30 May 2005 19:19:41 -0700, Clint Colefax wrote:
>I am getting a check constraint error on the following query.
>INSERT TABLEA
>( COL1, COL2, COL3)
>SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
>There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
>returning 20000. All columns are of datatype INT as is the return value of
>the function.
Hi Clint,
I could not reproduce this behaviour (see repro script below). Could you
post a repro script for me to run and reproduce the error?
>I have tried making COL3 a
>computed column, but can't have a constraint on a computed column,
If Col3 is always equal to Col1 - Col2, you should make it a computed
column. You can replace the check constraint wiuth the following
equivalent:
CHECK (Col1 >= Col2)
Here's the script I used to try to reproduce your problem, and the
output I got from it:
create table TableA
(Col1 int not null,
Col2 int not null,
Col3 int not null,
PRIMARY KEY (Col1),
CHECK (Col3 >= 0)
)
go
create function dbo.Function1()
returns int
as
begin
return 20000
end
go
INSERT TableA
( Col1, Col2, Col3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
go
select * from TableA
go
drop function dbo.Function1
go
drop table TableA
go
Col1 Col2 Col3
-- -- --
20000 -20000 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I gave the earlier example as I didn't want to post all of the infrastructur
e
around this problem. With the following cut down version, I am still able to
replicate the problem.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST
Thank you
Clint Colefax|||Sorry, I didn't think out my example very well, the following is code that
should reproduce the error.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST|||Thank you but all datatype are of INT, all match, even using a CAST or
CONVERT statement does not get around the problem (had previouslty attempted
).
Thank you for your input
Clint Colefax|||It's a bug, and a surprising one:
CREATE TABLE TEST (
a int not null,
b smalldatetime not null,
constraint finalv CHECK (a >= 0)
)
go
INSERT TEST(a) SELECT 1
Gives this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.TEST';
column does not allow nulls. INSERT fails.
The statement has been terminated.
Can you post the result of
SELECT @.@.version
so we can see what version you're running?
I verified this on 8.00.2039, and will report it to Microsoft.
SK
Clint Colefax wrote:
>there error is a check constraint violation for the FINAL_VALUE constraint.
>Server: Msg 547, Level 16, State 1, Line 1
>INSERT statement conflicted with COLUMN CHECK constraint
>'CK__test__FINAL_VALU__3AA27A0F'. The conflict occurred in database
>'LIPSDev', table 'test', column 'FINAL_VALUE'.
>The statement has been terminated.
>Thank you
>
Check Constraint fails!
Hi Everybody,
Can anybody help me on the following query...
I have a table structure as follows
CREATE TABLE [dbo].[event_logs] (
[WSE_Idx] [int] NULL ,
[WSE_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Date_Generated] [datetime] NULL ,
[WSE_lDate_Generated] [datetime] NULL ,
[WSE_Date_Written] [datetime] NULL ,
[WSE_lDate_Written] [datetime] NULL ,
[WSE_tzname] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Source] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Category] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Event] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_User_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Computer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Agent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Log_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
go
It contains data. I tried to create the following 'check constraint' to the above table
alter table event_logs
add constraint ck_event_logs
check((WSE_Category = 'application' and wse_log_type in ('Audit Success','error')) OR
(WSE_Category = 'system' and wse_log_type in ('Warning')) OR
(WSE_Category = 'security' and wse_log_type in ('Audit Failure')))
It is giving the following error...
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE CHECK constraint 'ck_event_logs'.
The conflict occurred in database 'slm', table 'event_logs'.
Even I modified the above alter table script as follows, still it is giving the same error.
alter table event_logs
add constraint ck_event_logs
check(WSE_Category like '%applica%')
I created the similar table structure with different table name and applied the check constraint,
it works. No error. Ofcourse table doesn't have data (Empty table).
I have created RULE on this 'event_logs' table (with data). It works fine. No Error.
Can anybody tell me why this 'Check Constraint' is giving problem?.
tks in advance,
vasumData in a table are not valid for 'check constraint' that you specified.
You mast correct data in your table or in ALTER TABLE statement put WITH
NOCHECK option.
Look ALTER TABLE in BOL.
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:748BD3CB-E545-4DEA-B05B-103EEF45BFAE@.microsoft.com...
> Hi Everybody,
> Can anybody help me on the following query...
> I have a table structure as follows
> CREATE TABLE [dbo].[event_logs] (
> [WSE_Idx] [int] NULL ,
> [WSE_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Date_Generated] [datetime] NULL ,
> [WSE_lDate_Generated] [datetime] NULL ,
> [WSE_Date_Written] [datetime] NULL ,
> [WSE_lDate_Written] [datetime] NULL ,
> [WSE_tzname] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Source] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Category] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Event] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_User_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Computer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Agent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Log_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> go
> It contains data. I tried to create the following 'check constraint' to
the above table
> alter table event_logs
> add constraint ck_event_logs
> check((WSE_Category = 'application' and wse_log_type in ('Audit
Success','error')) OR
> (WSE_Category = 'system' and wse_log_type in ('Warning')) OR
> (WSE_Category = 'security' and wse_log_type in ('Audit Failure')))
> It is giving the following error...
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE CHECK constraint
'ck_event_logs'.
> The conflict occurred in database 'slm', table 'event_logs'.
> Even I modified the above alter table script as follows, still it is
giving the same error.
> alter table event_logs
> add constraint ck_event_logs
> check(WSE_Category like '%applica%')
> I created the similar table structure with different table name and
applied the check constraint,
> it works. No error. Ofcourse table doesn't have data (Empty table).
> I have created RULE on this 'event_logs' table (with data). It works fine.
No Error.
> Can anybody tell me why this 'Check Constraint' is giving problem?.
> tks in advance,
> vasum
>|||thanks for the timely help. I works. I used 'with nocheck' option. Able to create new check constraint and this new check constraint is validating the any new rows coming into the table
Can anybody help me on the following query...
I have a table structure as follows
CREATE TABLE [dbo].[event_logs] (
[WSE_Idx] [int] NULL ,
[WSE_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Date_Generated] [datetime] NULL ,
[WSE_lDate_Generated] [datetime] NULL ,
[WSE_Date_Written] [datetime] NULL ,
[WSE_lDate_Written] [datetime] NULL ,
[WSE_tzname] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Source] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Category] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Event] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_User_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Computer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Agent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Log_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
go
It contains data. I tried to create the following 'check constraint' to the above table
alter table event_logs
add constraint ck_event_logs
check((WSE_Category = 'application' and wse_log_type in ('Audit Success','error')) OR
(WSE_Category = 'system' and wse_log_type in ('Warning')) OR
(WSE_Category = 'security' and wse_log_type in ('Audit Failure')))
It is giving the following error...
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE CHECK constraint 'ck_event_logs'.
The conflict occurred in database 'slm', table 'event_logs'.
Even I modified the above alter table script as follows, still it is giving the same error.
alter table event_logs
add constraint ck_event_logs
check(WSE_Category like '%applica%')
I created the similar table structure with different table name and applied the check constraint,
it works. No error. Ofcourse table doesn't have data (Empty table).
I have created RULE on this 'event_logs' table (with data). It works fine. No Error.
Can anybody tell me why this 'Check Constraint' is giving problem?.
tks in advance,
vasumData in a table are not valid for 'check constraint' that you specified.
You mast correct data in your table or in ALTER TABLE statement put WITH
NOCHECK option.
Look ALTER TABLE in BOL.
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:748BD3CB-E545-4DEA-B05B-103EEF45BFAE@.microsoft.com...
> Hi Everybody,
> Can anybody help me on the following query...
> I have a table structure as follows
> CREATE TABLE [dbo].[event_logs] (
> [WSE_Idx] [int] NULL ,
> [WSE_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Date_Generated] [datetime] NULL ,
> [WSE_lDate_Generated] [datetime] NULL ,
> [WSE_Date_Written] [datetime] NULL ,
> [WSE_lDate_Written] [datetime] NULL ,
> [WSE_tzname] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Source] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Category] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Event] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_User_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Computer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Agent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Log_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> go
> It contains data. I tried to create the following 'check constraint' to
the above table
> alter table event_logs
> add constraint ck_event_logs
> check((WSE_Category = 'application' and wse_log_type in ('Audit
Success','error')) OR
> (WSE_Category = 'system' and wse_log_type in ('Warning')) OR
> (WSE_Category = 'security' and wse_log_type in ('Audit Failure')))
> It is giving the following error...
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE CHECK constraint
'ck_event_logs'.
> The conflict occurred in database 'slm', table 'event_logs'.
> Even I modified the above alter table script as follows, still it is
giving the same error.
> alter table event_logs
> add constraint ck_event_logs
> check(WSE_Category like '%applica%')
> I created the similar table structure with different table name and
applied the check constraint,
> it works. No error. Ofcourse table doesn't have data (Empty table).
> I have created RULE on this 'event_logs' table (with data). It works fine.
No Error.
> Can anybody tell me why this 'Check Constraint' is giving problem?.
> tks in advance,
> vasum
>|||thanks for the timely help. I works. I used 'with nocheck' option. Able to create new check constraint and this new check constraint is validating the any new rows coming into the table
Wednesday, March 7, 2012
Check 3 occurrences of double characters.
Hi all,
I need to write some sort of statement to identify which numbers from a list fall into the following pattern:
% TwoIdenticalNumbers % TwoIdenticalNumbers % TwoIdenticalNumbers %
for example:
08812355677
I thought I would be able to use a LIKE statement but I'm not sure how to write it so that double characters are used rather than single. For example if I wanted to check three numbers appear within the string I could do the following:
SELECT *
FROM Table
WHERE Number LIKE '%[0-9]%[0-9]%[0-9]%'
I think the easiest way to check the doubles would be to represent them as a string, so I want to replace each of the [0-9] above with something like the following:
['00' OR '11' OR '22' OR '33' OR '44' OR '55' OR '66' OR '66' OR '77' OR '88' OR '99']
How could I write this using proper SQL code?
Any help would be much appreciated.
Thanks very much,
Will
Thre is no predefined expressions available,
Following approach is one of the way to achive this,
Code Snippet
Create Table #data (
[Numbers] Varchar(100)
);
Insert Into #data Values('1242432');
Insert Into #data Values('242423423');
Insert Into #data Values('2332232');
Insert Into #data Values('828289');
Insert Into #data Values('99887766');
Insert Into #data Values('92829299');
select
numbers
from #data
cross join
(
select '00' n
union all
select '11'
union all
select '22'
union all
select '33'
union all
select '44'
union all
select '55'
union all
select '66'
union all
select '77'
union all
select '88'
union all
select '99' ) as d
group by numbers having sum(case when patindex('%'+ n + '%',numbers) <> 0 Then 1 Else 0 End) >= 3
|||:-) Looks oofy, but works
SELECT * FROM sysobjects
WHERE
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%'
|||
The idea is good, but the following query might fit the asked requirement,(3 occurrence of …)
Code Snippet
SELECT Id FROM sysobjects
WHERE
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%X%X%'
|||
Very close. At least three instances are needed.
'11' -- does not qualify
'1122' -- does not qualify
'112233' -- bingo
...
SELECT
*
FROM
(select '11' as ID) as t
WHERE
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%'
AMB
|||Talking about ugly. Try:
-- thanks to Manni for the sample data
select
numbers
from
#Data
where
(len(numbers) - len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(numbers, '00', ''), '11', ''), '22', ''), '33', ''), '44', ''), '55', ''), '66', ''), '77', ''), '88', ''), '99', ''))) / 2 >= 3
AMB
|||Thanks for all the help, this has saved me a lot of time trying to figure this one out.ChartType in RunTime
Could I set up Chart type in run-time. The attempt to set <Type> attribute
inside RDL in the following way:
<Chart Name="1">
...
<Type> = Parameters!ReportChart.Value</Type>
was failed
"Deserialization failed: Requested value =Parameters!ChartType.Value was not
found."
Any suggestion?
Regards
YuriPlease read these postings:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=eda1cbbf-cb32-4ae3-903b-eff73af5cdc6&sloc=en-us
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a1325500-ca6c-4a71-ad3f-6a59dac96d88&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Yuri Belenky" <yurib@.mercatus.no> wrote in message
news:u3HYLMGgEHA.536@.TK2MSFTNGP11.phx.gbl...
> Could I set up Chart type in run-time. The attempt to set <Type> attribute
> inside RDL in the following way:
> <Chart Name="1">
> ...
> <Type> = Parameters!ReportChart.Value</Type>
> was failed
> "Deserialization failed: Requested value =Parameters!ChartType.Value was
not
> found."
> Any suggestion?
> Regards
> Yuri
>|||Thanks a lot.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O3lz4oIgEHA.3932@.TK2MSFTNGP09.phx.gbl...
> Please read these postings:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=eda1cbbf-cb32-4ae3-903b-eff73af5cdc6&sloc=en-us
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a1325500-ca6c-4a71-ad3f-6a59dac96d88&sloc=en-us
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Yuri Belenky" <yurib@.mercatus.no> wrote in message
> news:u3HYLMGgEHA.536@.TK2MSFTNGP11.phx.gbl...
>> Could I set up Chart type in run-time. The attempt to set <Type>
>> attribute
>> inside RDL in the following way:
>> <Chart Name="1">
>> ...
>> <Type> = Parameters!ReportChart.Value</Type>
>> was failed
>> "Deserialization failed: Requested value =Parameters!ChartType.Value was
> not
>> found."
>> Any suggestion?
>> Regards
>> Yuri
>>
>|||Hi Robert,
I have this same question, although when i click the links they dont work'
Any help would be great.
Thanks in Advance
Shea
"Robert Bruckner [MSFT]" wrote:
> Please read these postings:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=eda1cbbf-cb32-4ae3-903b-eff73af5cdc6&sloc=en-us
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a1325500-ca6c-4a71-ad3f-6a59dac96d88&sloc=en-us
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Yuri Belenky" <yurib@.mercatus.no> wrote in message
> news:u3HYLMGgEHA.536@.TK2MSFTNGP11.phx.gbl...
> > Could I set up Chart type in run-time. The attempt to set <Type> attribute
> > inside RDL in the following way:
> > <Chart Name="1">
> > ...
> > <Type> = Parameters!ReportChart.Value</Type>
> >
> > was failed
> > "Deserialization failed: Requested value =Parameters!ChartType.Value was
> not
> > found."
> >
> > Any suggestion?
> >
> > Regards
> > Yuri
> >
> >
>
>|||Unfortunately, the news server does not keep an unlimited history of
postings. It seems that both threads have been already removed because they
were too old.
To answer the question: dynamic expression-based chart types are not
available. The closest you can get today is to have multiple charts defined
in your report with different chart types and dynamically hide all of them
(through the Visibility.Hidden property) except for the one you want show.
If you want to change the chart type based on the amount of datapoints, you
might also want to look into using drillthrough actions on the chart. The
main chart would show an aggregated view of the data. Clicking on one of the
data points will then drill through to another report with a detailed view
of the data.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shea Strickland" <SheaStrickland@.discussions.microsoft.com> wrote in
message news:6A9B2659-3337-4186-B189-CC4FB7CDC7D6@.microsoft.com...
> Hi Robert,
> I have this same question, although when i click the links they dont
work'
> Any help would be great.
> Thanks in Advance
> Shea
> "Robert Bruckner [MSFT]" wrote:
> > Please read these postings:
> >
> >
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=eda1cbbf-cb32-4ae3-903b-eff73af5cdc6&sloc=en-us
> >
> >
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a1325500-ca6c-4a71-ad3f-6a59dac96d88&sloc=en-us
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Yuri Belenky" <yurib@.mercatus.no> wrote in message
> > news:u3HYLMGgEHA.536@.TK2MSFTNGP11.phx.gbl...
> > > Could I set up Chart type in run-time. The attempt to set <Type>
attribute
> > > inside RDL in the following way:
> > > <Chart Name="1">
> > > ...
> > > <Type> = Parameters!ReportChart.Value</Type>
> > >
> > > was failed
> > > "Deserialization failed: Requested value =Parameters!ChartType.Value
was
> > not
> > > found."
> > >
> > > Any suggestion?
> > >
> > > Regards
> > > Yuri
> > >
> > >
> >
> >
> >|||Robert,
Thanks for the speedy reply, I was heading that way, that post just sounded
like you could do dynamic chart types.
Thanks Again
Shea
"Robert Bruckner [MSFT]" wrote:
> Unfortunately, the news server does not keep an unlimited history of
> postings. It seems that both threads have been already removed because they
> were too old.
> To answer the question: dynamic expression-based chart types are not
> available. The closest you can get today is to have multiple charts defined
> in your report with different chart types and dynamically hide all of them
> (through the Visibility.Hidden property) except for the one you want show.
> If you want to change the chart type based on the amount of datapoints, you
> might also want to look into using drillthrough actions on the chart. The
> main chart would show an aggregated view of the data. Clicking on one of the
> data points will then drill through to another report with a detailed view
> of the data.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shea Strickland" <SheaStrickland@.discussions.microsoft.com> wrote in
> message news:6A9B2659-3337-4186-B189-CC4FB7CDC7D6@.microsoft.com...
> > Hi Robert,
> >
> > I have this same question, although when i click the links they dont
> work'
> >
> > Any help would be great.
> >
> > Thanks in Advance
> > Shea
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Please read these postings:
> > >
> > >
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=eda1cbbf-cb32-4ae3-903b-eff73af5cdc6&sloc=en-us
> > >
> > >
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a1325500-ca6c-4a71-ad3f-6a59dac96d88&sloc=en-us
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Yuri Belenky" <yurib@.mercatus.no> wrote in message
> > > news:u3HYLMGgEHA.536@.TK2MSFTNGP11.phx.gbl...
> > > > Could I set up Chart type in run-time. The attempt to set <Type>
> attribute
> > > > inside RDL in the following way:
> > > > <Chart Name="1">
> > > > ...
> > > > <Type> = Parameters!ReportChart.Value</Type>
> > > >
> > > > was failed
> > > > "Deserialization failed: Requested value =Parameters!ChartType.Value
> was
> > > not
> > > > found."
> > > >
> > > > Any suggestion?
> > > >
> > > > Regards
> > > > Yuri
> > > >
> > > >
> > >
> > >
> > >
>
>
inside RDL in the following way:
<Chart Name="1">
...
<Type> = Parameters!ReportChart.Value</Type>
was failed
"Deserialization failed: Requested value =Parameters!ChartType.Value was not
found."
Any suggestion?
Regards
YuriPlease read these postings:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=eda1cbbf-cb32-4ae3-903b-eff73af5cdc6&sloc=en-us
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a1325500-ca6c-4a71-ad3f-6a59dac96d88&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Yuri Belenky" <yurib@.mercatus.no> wrote in message
news:u3HYLMGgEHA.536@.TK2MSFTNGP11.phx.gbl...
> Could I set up Chart type in run-time. The attempt to set <Type> attribute
> inside RDL in the following way:
> <Chart Name="1">
> ...
> <Type> = Parameters!ReportChart.Value</Type>
> was failed
> "Deserialization failed: Requested value =Parameters!ChartType.Value was
not
> found."
> Any suggestion?
> Regards
> Yuri
>|||Thanks a lot.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O3lz4oIgEHA.3932@.TK2MSFTNGP09.phx.gbl...
> Please read these postings:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=eda1cbbf-cb32-4ae3-903b-eff73af5cdc6&sloc=en-us
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a1325500-ca6c-4a71-ad3f-6a59dac96d88&sloc=en-us
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Yuri Belenky" <yurib@.mercatus.no> wrote in message
> news:u3HYLMGgEHA.536@.TK2MSFTNGP11.phx.gbl...
>> Could I set up Chart type in run-time. The attempt to set <Type>
>> attribute
>> inside RDL in the following way:
>> <Chart Name="1">
>> ...
>> <Type> = Parameters!ReportChart.Value</Type>
>> was failed
>> "Deserialization failed: Requested value =Parameters!ChartType.Value was
> not
>> found."
>> Any suggestion?
>> Regards
>> Yuri
>>
>|||Hi Robert,
I have this same question, although when i click the links they dont work'
Any help would be great.
Thanks in Advance
Shea
"Robert Bruckner [MSFT]" wrote:
> Please read these postings:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=eda1cbbf-cb32-4ae3-903b-eff73af5cdc6&sloc=en-us
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a1325500-ca6c-4a71-ad3f-6a59dac96d88&sloc=en-us
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Yuri Belenky" <yurib@.mercatus.no> wrote in message
> news:u3HYLMGgEHA.536@.TK2MSFTNGP11.phx.gbl...
> > Could I set up Chart type in run-time. The attempt to set <Type> attribute
> > inside RDL in the following way:
> > <Chart Name="1">
> > ...
> > <Type> = Parameters!ReportChart.Value</Type>
> >
> > was failed
> > "Deserialization failed: Requested value =Parameters!ChartType.Value was
> not
> > found."
> >
> > Any suggestion?
> >
> > Regards
> > Yuri
> >
> >
>
>|||Unfortunately, the news server does not keep an unlimited history of
postings. It seems that both threads have been already removed because they
were too old.
To answer the question: dynamic expression-based chart types are not
available. The closest you can get today is to have multiple charts defined
in your report with different chart types and dynamically hide all of them
(through the Visibility.Hidden property) except for the one you want show.
If you want to change the chart type based on the amount of datapoints, you
might also want to look into using drillthrough actions on the chart. The
main chart would show an aggregated view of the data. Clicking on one of the
data points will then drill through to another report with a detailed view
of the data.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shea Strickland" <SheaStrickland@.discussions.microsoft.com> wrote in
message news:6A9B2659-3337-4186-B189-CC4FB7CDC7D6@.microsoft.com...
> Hi Robert,
> I have this same question, although when i click the links they dont
work'
> Any help would be great.
> Thanks in Advance
> Shea
> "Robert Bruckner [MSFT]" wrote:
> > Please read these postings:
> >
> >
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=eda1cbbf-cb32-4ae3-903b-eff73af5cdc6&sloc=en-us
> >
> >
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a1325500-ca6c-4a71-ad3f-6a59dac96d88&sloc=en-us
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Yuri Belenky" <yurib@.mercatus.no> wrote in message
> > news:u3HYLMGgEHA.536@.TK2MSFTNGP11.phx.gbl...
> > > Could I set up Chart type in run-time. The attempt to set <Type>
attribute
> > > inside RDL in the following way:
> > > <Chart Name="1">
> > > ...
> > > <Type> = Parameters!ReportChart.Value</Type>
> > >
> > > was failed
> > > "Deserialization failed: Requested value =Parameters!ChartType.Value
was
> > not
> > > found."
> > >
> > > Any suggestion?
> > >
> > > Regards
> > > Yuri
> > >
> > >
> >
> >
> >|||Robert,
Thanks for the speedy reply, I was heading that way, that post just sounded
like you could do dynamic chart types.
Thanks Again
Shea
"Robert Bruckner [MSFT]" wrote:
> Unfortunately, the news server does not keep an unlimited history of
> postings. It seems that both threads have been already removed because they
> were too old.
> To answer the question: dynamic expression-based chart types are not
> available. The closest you can get today is to have multiple charts defined
> in your report with different chart types and dynamically hide all of them
> (through the Visibility.Hidden property) except for the one you want show.
> If you want to change the chart type based on the amount of datapoints, you
> might also want to look into using drillthrough actions on the chart. The
> main chart would show an aggregated view of the data. Clicking on one of the
> data points will then drill through to another report with a detailed view
> of the data.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shea Strickland" <SheaStrickland@.discussions.microsoft.com> wrote in
> message news:6A9B2659-3337-4186-B189-CC4FB7CDC7D6@.microsoft.com...
> > Hi Robert,
> >
> > I have this same question, although when i click the links they dont
> work'
> >
> > Any help would be great.
> >
> > Thanks in Advance
> > Shea
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Please read these postings:
> > >
> > >
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=eda1cbbf-cb32-4ae3-903b-eff73af5cdc6&sloc=en-us
> > >
> > >
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a1325500-ca6c-4a71-ad3f-6a59dac96d88&sloc=en-us
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Yuri Belenky" <yurib@.mercatus.no> wrote in message
> > > news:u3HYLMGgEHA.536@.TK2MSFTNGP11.phx.gbl...
> > > > Could I set up Chart type in run-time. The attempt to set <Type>
> attribute
> > > > inside RDL in the following way:
> > > > <Chart Name="1">
> > > > ...
> > > > <Type> = Parameters!ReportChart.Value</Type>
> > > >
> > > > was failed
> > > > "Deserialization failed: Requested value =Parameters!ChartType.Value
> was
> > > not
> > > > found."
> > > >
> > > > Any suggestion?
> > > >
> > > > Regards
> > > > Yuri
> > > >
> > > >
> > >
> > >
> > >
>
>
Charts using SSRS
Friends,
I have a requirement in Bar charts using SSRS...Assume that I have 3 Products Quantity and the representation will look like following
A - 10
B- 20
C-15
Though it is easy to develop this as a bar chart...The requirement is that a Product Perfomace should be represented using Images in the chart area...Instead of showing that A has 10 quantitites in a single bar, a general product image should be coming in the place of 10 and same with the case of B and C.. I have used smiley icons here for clarity
The look of the chart should be like following
|
|__
|__|
|
|__
|__|
|
|_
|_|
|_
Please let me know if there is a way to customize this bar chart using SSRS Charts...Thanks in Advance
There is a custom chart displaying in the above format, but does not have image associated to the data lable.
have anyone come across charts of this type, like the one mentioned in this thread
Thursday, February 16, 2012
charlist_to_table for mvp function
Hi, I found the following function on this site and am trying to use it my
reports.
The dataset for my mvp is different from my stored proc I'm using.
The data set for my mvp is simple
codes dataset = select distinct codes from tbl_codes
values are
AAA-2222
BBB-3333
CCC-444
In my stored procedure I call the function
select * from dbo.tbl_codes as a
where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
the issue is that it only retrives the first code instead of all three.
this is how I test it:
select nstr from charlist_to_table
('AAA-2222,
BBB-3333,
CCC-444
',',')
I get the following
AAA-2222,
BBB-3333,
CCC-444
I don't think the function is working in the sp because there is a space in
front of the values. Even when I put a space in the before the codes data
set I still only get the data for the first code AAA-2222.
Am I missing something in the code below. Thanks, Lisa
CREATE FUNCTION [dbo].[charlist_to_table]
(@.list ntext, @.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
ENDI call it using default keyword.
select str from charlist_to_talbe(@.codes,default)
I use a join.
select a.* from dbo.tbl_codes a inner join charlist_to_table(@.CODES,Default)
b on a.codes = b.str
change b.str to b.nstr depending on the datatype of a.codes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> Hi, I found the following function on this site and am trying to use it my
> reports.
> The dataset for my mvp is different from my stored proc I'm using.
> The data set for my mvp is simple
> codes dataset => select distinct codes from tbl_codes
> values are
> AAA-2222
> BBB-3333
> CCC-444
> In my stored procedure I call the function
> select * from dbo.tbl_codes as a
> where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> the issue is that it only retrives the first code instead of all three.
> this is how I test it:
> select nstr from charlist_to_table
> ('AAA-2222,
> BBB-3333,
> CCC-444
> ',',')
> I get the following
> AAA-2222,
> BBB-3333,
> CCC-444
> I don't think the function is working in the sp because there is a space
> in
> front of the values. Even when I put a space in the before the codes data
> set I still only get the data for the first code AAA-2222.
> Am I missing something in the code below. Thanks, Lisa
> CREATE FUNCTION [dbo].[charlist_to_table]
> (@.list ntext, @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END|||Thanks for your help. I understand, but there is still something missing.
see the test
declare @.codes varchar(50)
select
@.codes = ('SWA35-2948,
SWAP2-2892,
SWA27-2946,
GRE1-2936,
ADM2-2930,
SWA28-2938,
SWUA2-2938,
SWA31-2948,
SWAP4-2950,
SWUA3-2938)
--test
print @.codes
this come out correct
SWA35-2948,
SWAP2-2892,
SWA27-2946,
GRE1-2936,
ADM2-2930,
SWA28-2938,
SWUA2-2938,
SWA31-2948,
SWAP4-2950,
SWUA3-2938
but when I run this
select * from charlist_to_table(@.promo_code,default)
I get the following
listpos str nstr
1 SWA35-2948 SWA35-2948
2 SWAP2-2892 SWAP2-2892
3 SWA27-2946 SWA27-2946
4 GRE1-2936 GRE1-2936
5
I should have 10 listpos and there still spaces in front out the other values.
so this only returns the first row's value for code SWA35-2948
select a.* from dbo.swp_camps as a
inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
Any suggestions. Thanks, Lisa
"Bruce L-C [MVP]" wrote:
> I call it using default keyword.
> select str from charlist_to_talbe(@.codes,default)
> I use a join.
> select a.* from dbo.tbl_codes a inner join charlist_to_table(@.CODES,Default)
> b on a.codes = b.str
> change b.str to b.nstr depending on the datatype of a.codes.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> > Hi, I found the following function on this site and am trying to use it my
> > reports.
> > The dataset for my mvp is different from my stored proc I'm using.
> > The data set for my mvp is simple
> >
> > codes dataset => > select distinct codes from tbl_codes
> >
> > values are
> > AAA-2222
> > BBB-3333
> > CCC-444
> >
> > In my stored procedure I call the function
> >
> > select * from dbo.tbl_codes as a
> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> >
> > the issue is that it only retrives the first code instead of all three.
> >
> > this is how I test it:
> > select nstr from charlist_to_table
> > ('AAA-2222,
> > BBB-3333,
> > CCC-444
> > ',',')
> >
> > I get the following
> > AAA-2222,
> > BBB-3333,
> > CCC-444
> >
> > I don't think the function is working in the sp because there is a space
> > in
> > front of the values. Even when I put a space in the before the codes data
> > set I still only get the data for the first code AAA-2222.
> >
> > Am I missing something in the code below. Thanks, Lisa
> >
> > CREATE FUNCTION [dbo].[charlist_to_table]
> > (@.list ntext, @.delimiter nchar(1) = N',')
> >
> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> > str varchar(4000),
> > nstr nvarchar(2000)) AS
> > BEGIN
> > DECLARE @.pos int,
> > @.textpos int,
> > @.chunklen smallint,
> > @.tmpstr nvarchar(4000),
> > @.leftover nvarchar(4000),
> > @.tmpval nvarchar(4000)
> > SET @.textpos = 1
> > SET @.leftover = ''
> > WHILE @.textpos <= datalength(@.list) / 2
> > BEGIN
> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> > SET @.textpos = @.textpos + @.chunklen
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > WHILE @.pos > 0
> > BEGIN
> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > END
> > SET @.leftover = @.tmpstr
> > END
> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> > ltrim(rtrim(@.leftover)))
> > RETURN
> > END
>
>|||I meant this above
select * from charlist_to_table(@.codes,default)
"Lisa" wrote:
> Thanks for your help. I understand, but there is still something missing.
>
> see the test
> declare @.codes varchar(50)
> select
> @.codes = ('SWA35-2948,
> SWAP2-2892,
> SWA27-2946,
> GRE1-2936,
> ADM2-2930,
> SWA28-2938,
> SWUA2-2938,
> SWA31-2948,
> SWAP4-2950,
> SWUA3-2938)
> --test
> print @.codes
> this come out correct
> SWA35-2948,
> SWAP2-2892,
> SWA27-2946,
> GRE1-2936,
> ADM2-2930,
> SWA28-2938,
> SWUA2-2938,
> SWA31-2948,
> SWAP4-2950,
> SWUA3-2938
> but when I run this
> select * from charlist_to_table(@.promo_code,default)
> I get the following
> listpos str nstr
> 1 SWA35-2948 SWA35-2948
> 2 SWAP2-2892 SWAP2-2892
> 3 SWA27-2946 SWA27-2946
> 4 GRE1-2936 GRE1-2936
> 5
>
> I should have 10 listpos and there still spaces in front out the other values.
> so this only returns the first row's value for code SWA35-2948
> select a.* from dbo.swp_camps as a
> inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
>
> Any suggestions. Thanks, Lisa
> "Bruce L-C [MVP]" wrote:
> > I call it using default keyword.
> >
> > select str from charlist_to_talbe(@.codes,default)
> >
> > I use a join.
> >
> > select a.* from dbo.tbl_codes a inner join charlist_to_table(@.CODES,Default)
> > b on a.codes = b.str
> >
> > change b.str to b.nstr depending on the datatype of a.codes.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> > news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> > > Hi, I found the following function on this site and am trying to use it my
> > > reports.
> > > The dataset for my mvp is different from my stored proc I'm using.
> > > The data set for my mvp is simple
> > >
> > > codes dataset => > > select distinct codes from tbl_codes
> > >
> > > values are
> > > AAA-2222
> > > BBB-3333
> > > CCC-444
> > >
> > > In my stored procedure I call the function
> > >
> > > select * from dbo.tbl_codes as a
> > > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> > >
> > > the issue is that it only retrives the first code instead of all three.
> > >
> > > this is how I test it:
> > > select nstr from charlist_to_table
> > > ('AAA-2222,
> > > BBB-3333,
> > > CCC-444
> > > ',',')
> > >
> > > I get the following
> > > AAA-2222,
> > > BBB-3333,
> > > CCC-444
> > >
> > > I don't think the function is working in the sp because there is a space
> > > in
> > > front of the values. Even when I put a space in the before the codes data
> > > set I still only get the data for the first code AAA-2222.
> > >
> > > Am I missing something in the code below. Thanks, Lisa
> > >
> > > CREATE FUNCTION [dbo].[charlist_to_table]
> > > (@.list ntext, @.delimiter nchar(1) = N',')
> > >
> > > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> > > str varchar(4000),
> > > nstr nvarchar(2000)) AS
> > > BEGIN
> > > DECLARE @.pos int,
> > > @.textpos int,
> > > @.chunklen smallint,
> > > @.tmpstr nvarchar(4000),
> > > @.leftover nvarchar(4000),
> > > @.tmpval nvarchar(4000)
> > > SET @.textpos = 1
> > > SET @.leftover = ''
> > > WHILE @.textpos <= datalength(@.list) / 2
> > > BEGIN
> > > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> > > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> > > SET @.textpos = @.textpos + @.chunklen
> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > > WHILE @.pos > 0
> > > BEGIN
> > > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> > > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> > > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > > END
> > > SET @.leftover = @.tmpstr
> > > END
> > > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> > > ltrim(rtrim(@.leftover)))
> > > RETURN
> > > END
> >
> >
> >|||Make your @.codes larger. At least for the below that is why it is not
working.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> Thanks for your help. I understand, but there is still something missing.
>
> see the test
> declare @.codes varchar(50)
> select
> @.codes = ('SWA35-2948,
> SWAP2-2892,
> SWA27-2946,
> GRE1-2936,
> ADM2-2930,
> SWA28-2938,
> SWUA2-2938,
> SWA31-2948,
> SWAP4-2950,
> SWUA3-2938)
> --test
> print @.codes
> this come out correct
> SWA35-2948,
> SWAP2-2892,
> SWA27-2946,
> GRE1-2936,
> ADM2-2930,
> SWA28-2938,
> SWUA2-2938,
> SWA31-2948,
> SWAP4-2950,
> SWUA3-2938
> but when I run this
> select * from charlist_to_table(@.promo_code,default)
> I get the following
> listpos str nstr
> 1 SWA35-2948 SWA35-2948
> 2 SWAP2-2892 SWAP2-2892
> 3 SWA27-2946 SWA27-2946
> 4 GRE1-2936 GRE1-2936
> 5
>
> I should have 10 listpos and there still spaces in front out the other
> values.
> so this only returns the first row's value for code SWA35-2948
> select a.* from dbo.swp_camps as a
> inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
>
> Any suggestions. Thanks, Lisa
> "Bruce L-C [MVP]" wrote:
>> I call it using default keyword.
>> select str from charlist_to_talbe(@.codes,default)
>> I use a join.
>> select a.* from dbo.tbl_codes a inner join
>> charlist_to_table(@.CODES,Default)
>> b on a.codes = b.str
>> change b.str to b.nstr depending on the datatype of a.codes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
>> > Hi, I found the following function on this site and am trying to use it
>> > my
>> > reports.
>> > The dataset for my mvp is different from my stored proc I'm using.
>> > The data set for my mvp is simple
>> >
>> > codes dataset =>> > select distinct codes from tbl_codes
>> >
>> > values are
>> > AAA-2222
>> > BBB-3333
>> > CCC-444
>> >
>> > In my stored procedure I call the function
>> >
>> > select * from dbo.tbl_codes as a
>> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
>> >
>> > the issue is that it only retrives the first code instead of all three.
>> >
>> > this is how I test it:
>> > select nstr from charlist_to_table
>> > ('AAA-2222,
>> > BBB-3333,
>> > CCC-444
>> > ',',')
>> >
>> > I get the following
>> > AAA-2222,
>> > BBB-3333,
>> > CCC-444
>> >
>> > I don't think the function is working in the sp because there is a
>> > space
>> > in
>> > front of the values. Even when I put a space in the before the codes
>> > data
>> > set I still only get the data for the first code AAA-2222.
>> >
>> > Am I missing something in the code below. Thanks, Lisa
>> >
>> > CREATE FUNCTION [dbo].[charlist_to_table]
>> > (@.list ntext, @.delimiter nchar(1) = N',')
>> >
>> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> > str varchar(4000),
>> > nstr nvarchar(2000)) AS
>> > BEGIN
>> > DECLARE @.pos int,
>> > @.textpos int,
>> > @.chunklen smallint,
>> > @.tmpstr nvarchar(4000),
>> > @.leftover nvarchar(4000),
>> > @.tmpval nvarchar(4000)
>> > SET @.textpos = 1
>> > SET @.leftover = ''
>> > WHILE @.textpos <= datalength(@.list) / 2
>> > BEGIN
>> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
>> > SET @.textpos = @.textpos + @.chunklen
>> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > WHILE @.pos > 0
>> > BEGIN
>> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > END
>> > SET @.leftover = @.tmpstr
>> > END
>> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> > ltrim(rtrim(@.leftover)))
>> > RETURN
>> > END
>>|||thanks, that worked. But, I still have the space issue
listpos str nstr
1 SWA35-2948 SWA35-2948
2 SWAP2-2892 SWAP2-2892
3 SWA27-2946 SWA27-2946
in the str and nstr fields all but the first row has spaces in front of the
value. This is why it's only returning the first row. thanks for you help.
"Bruce L-C [MVP]" wrote:
> Make your @.codes larger. At least for the below that is why it is not
> working.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> > Thanks for your help. I understand, but there is still something missing.
> >
> >
> > see the test
> > declare @.codes varchar(50)
> >
> > select
> > @.codes = ('SWA35-2948,
> > SWAP2-2892,
> > SWA27-2946,
> > GRE1-2936,
> > ADM2-2930,
> > SWA28-2938,
> > SWUA2-2938,
> > SWA31-2948,
> > SWAP4-2950,
> > SWUA3-2938)
> > --test
> > print @.codes
> > this come out correct
> > SWA35-2948,
> > SWAP2-2892,
> > SWA27-2946,
> > GRE1-2936,
> > ADM2-2930,
> > SWA28-2938,
> > SWUA2-2938,
> > SWA31-2948,
> > SWAP4-2950,
> > SWUA3-2938
> >
> > but when I run this
> > select * from charlist_to_table(@.promo_code,default)
> > I get the following
> >
> > listpos str nstr
> > 1 SWA35-2948 SWA35-2948
> > 2 SWAP2-2892 SWAP2-2892
> > 3 SWA27-2946 SWA27-2946
> > 4 GRE1-2936 GRE1-2936
> > 5
> >
> >
> > I should have 10 listpos and there still spaces in front out the other
> > values.
> > so this only returns the first row's value for code SWA35-2948
> > select a.* from dbo.swp_camps as a
> > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
> >
> >
> > Any suggestions. Thanks, Lisa
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I call it using default keyword.
> >>
> >> select str from charlist_to_talbe(@.codes,default)
> >>
> >> I use a join.
> >>
> >> select a.* from dbo.tbl_codes a inner join
> >> charlist_to_table(@.CODES,Default)
> >> b on a.codes = b.str
> >>
> >> change b.str to b.nstr depending on the datatype of a.codes.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> >> > Hi, I found the following function on this site and am trying to use it
> >> > my
> >> > reports.
> >> > The dataset for my mvp is different from my stored proc I'm using.
> >> > The data set for my mvp is simple
> >> >
> >> > codes dataset => >> > select distinct codes from tbl_codes
> >> >
> >> > values are
> >> > AAA-2222
> >> > BBB-3333
> >> > CCC-444
> >> >
> >> > In my stored procedure I call the function
> >> >
> >> > select * from dbo.tbl_codes as a
> >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> >> >
> >> > the issue is that it only retrives the first code instead of all three.
> >> >
> >> > this is how I test it:
> >> > select nstr from charlist_to_table
> >> > ('AAA-2222,
> >> > BBB-3333,
> >> > CCC-444
> >> > ',',')
> >> >
> >> > I get the following
> >> > AAA-2222,
> >> > BBB-3333,
> >> > CCC-444
> >> >
> >> > I don't think the function is working in the sp because there is a
> >> > space
> >> > in
> >> > front of the values. Even when I put a space in the before the codes
> >> > data
> >> > set I still only get the data for the first code AAA-2222.
> >> >
> >> > Am I missing something in the code below. Thanks, Lisa
> >> >
> >> > CREATE FUNCTION [dbo].[charlist_to_table]
> >> > (@.list ntext, @.delimiter nchar(1) = N',')
> >> >
> >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> >> > str varchar(4000),
> >> > nstr nvarchar(2000)) AS
> >> > BEGIN
> >> > DECLARE @.pos int,
> >> > @.textpos int,
> >> > @.chunklen smallint,
> >> > @.tmpstr nvarchar(4000),
> >> > @.leftover nvarchar(4000),
> >> > @.tmpval nvarchar(4000)
> >> > SET @.textpos = 1
> >> > SET @.leftover = ''
> >> > WHILE @.textpos <= datalength(@.list) / 2
> >> > BEGIN
> >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> >> > SET @.textpos = @.textpos + @.chunklen
> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >> > WHILE @.pos > 0
> >> > BEGIN
> >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >> > END
> >> > SET @.leftover = @.tmpstr
> >> > END
> >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> >> > ltrim(rtrim(@.leftover)))
> >> > RETURN
> >> > END
> >>
> >>
> >>
>
>|||never mind. It actually worked when I ran within the sp in ssrs. thanks.
Before I was testing it in query analyzer.
"Lisa" wrote:
> thanks, that worked. But, I still have the space issue
> listpos str nstr
> 1 SWA35-2948 SWA35-2948
> 2 SWAP2-2892 SWAP2-2892
> 3 SWA27-2946 SWA27-2946
>
> in the str and nstr fields all but the first row has spaces in front of the
> value. This is why it's only returning the first row. thanks for you help.
> "Bruce L-C [MVP]" wrote:
> > Make your @.codes larger. At least for the below that is why it is not
> > working.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> > news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> > > Thanks for your help. I understand, but there is still something missing.
> > >
> > >
> > > see the test
> > > declare @.codes varchar(50)
> > >
> > > select
> > > @.codes = ('SWA35-2948,
> > > SWAP2-2892,
> > > SWA27-2946,
> > > GRE1-2936,
> > > ADM2-2930,
> > > SWA28-2938,
> > > SWUA2-2938,
> > > SWA31-2948,
> > > SWAP4-2950,
> > > SWUA3-2938)
> > > --test
> > > print @.codes
> > > this come out correct
> > > SWA35-2948,
> > > SWAP2-2892,
> > > SWA27-2946,
> > > GRE1-2936,
> > > ADM2-2930,
> > > SWA28-2938,
> > > SWUA2-2938,
> > > SWA31-2948,
> > > SWAP4-2950,
> > > SWUA3-2938
> > >
> > > but when I run this
> > > select * from charlist_to_table(@.promo_code,default)
> > > I get the following
> > >
> > > listpos str nstr
> > > 1 SWA35-2948 SWA35-2948
> > > 2 SWAP2-2892 SWAP2-2892
> > > 3 SWA27-2946 SWA27-2946
> > > 4 GRE1-2936 GRE1-2936
> > > 5
> > >
> > >
> > > I should have 10 listpos and there still spaces in front out the other
> > > values.
> > > so this only returns the first row's value for code SWA35-2948
> > > select a.* from dbo.swp_camps as a
> > > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
> > >
> > >
> > > Any suggestions. Thanks, Lisa
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> I call it using default keyword.
> > >>
> > >> select str from charlist_to_talbe(@.codes,default)
> > >>
> > >> I use a join.
> > >>
> > >> select a.* from dbo.tbl_codes a inner join
> > >> charlist_to_table(@.CODES,Default)
> > >> b on a.codes = b.str
> > >>
> > >> change b.str to b.nstr depending on the datatype of a.codes.
> > >>
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> > >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> > >> > Hi, I found the following function on this site and am trying to use it
> > >> > my
> > >> > reports.
> > >> > The dataset for my mvp is different from my stored proc I'm using.
> > >> > The data set for my mvp is simple
> > >> >
> > >> > codes dataset => > >> > select distinct codes from tbl_codes
> > >> >
> > >> > values are
> > >> > AAA-2222
> > >> > BBB-3333
> > >> > CCC-444
> > >> >
> > >> > In my stored procedure I call the function
> > >> >
> > >> > select * from dbo.tbl_codes as a
> > >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> > >> >
> > >> > the issue is that it only retrives the first code instead of all three.
> > >> >
> > >> > this is how I test it:
> > >> > select nstr from charlist_to_table
> > >> > ('AAA-2222,
> > >> > BBB-3333,
> > >> > CCC-444
> > >> > ',',')
> > >> >
> > >> > I get the following
> > >> > AAA-2222,
> > >> > BBB-3333,
> > >> > CCC-444
> > >> >
> > >> > I don't think the function is working in the sp because there is a
> > >> > space
> > >> > in
> > >> > front of the values. Even when I put a space in the before the codes
> > >> > data
> > >> > set I still only get the data for the first code AAA-2222.
> > >> >
> > >> > Am I missing something in the code below. Thanks, Lisa
> > >> >
> > >> > CREATE FUNCTION [dbo].[charlist_to_table]
> > >> > (@.list ntext, @.delimiter nchar(1) = N',')
> > >> >
> > >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> > >> > str varchar(4000),
> > >> > nstr nvarchar(2000)) AS
> > >> > BEGIN
> > >> > DECLARE @.pos int,
> > >> > @.textpos int,
> > >> > @.chunklen smallint,
> > >> > @.tmpstr nvarchar(4000),
> > >> > @.leftover nvarchar(4000),
> > >> > @.tmpval nvarchar(4000)
> > >> > SET @.textpos = 1
> > >> > SET @.leftover = ''
> > >> > WHILE @.textpos <= datalength(@.list) / 2
> > >> > BEGIN
> > >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> > >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> > >> > SET @.textpos = @.textpos + @.chunklen
> > >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > >> > WHILE @.pos > 0
> > >> > BEGIN
> > >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> > >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> > >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> > >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > >> > END
> > >> > SET @.leftover = @.tmpstr
> > >> > END
> > >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> > >> > ltrim(rtrim(@.leftover)))
> > >> > RETURN
> > >> > END
> > >>
> > >>
> > >>
> >
> >
> >|||Are you putting it on separate lines when you do your test?
select nstr from charlist_to_table
('AAA-2222,
BBB-3333,
CCC-444
',',')
Since you are enclosing the whole thing in a string it is included the
carriage return (which will look like a blank). Do it like this:
select nstr from charlist_to_table
('AAA-2222,BBB-3333,CCC-444',',')
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:FEB9528A-30AB-44DC-A9FD-DBA43412B2CD@.microsoft.com...
> thanks, that worked. But, I still have the space issue
> listpos str nstr
> 1 SWA35-2948 SWA35-2948
> 2 SWAP2-2892 SWAP2-2892
> 3 SWA27-2946 SWA27-2946
>
> in the str and nstr fields all but the first row has spaces in front of
> the
> value. This is why it's only returning the first row. thanks for you
> help.
> "Bruce L-C [MVP]" wrote:
>> Make your @.codes larger. At least for the below that is why it is not
>> working.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
>> > Thanks for your help. I understand, but there is still something
>> > missing.
>> >
>> >
>> > see the test
>> > declare @.codes varchar(50)
>> >
>> > select
>> > @.codes = ('SWA35-2948,
>> > SWAP2-2892,
>> > SWA27-2946,
>> > GRE1-2936,
>> > ADM2-2930,
>> > SWA28-2938,
>> > SWUA2-2938,
>> > SWA31-2948,
>> > SWAP4-2950,
>> > SWUA3-2938)
>> > --test
>> > print @.codes
>> > this come out correct
>> > SWA35-2948,
>> > SWAP2-2892,
>> > SWA27-2946,
>> > GRE1-2936,
>> > ADM2-2930,
>> > SWA28-2938,
>> > SWUA2-2938,
>> > SWA31-2948,
>> > SWAP4-2950,
>> > SWUA3-2938
>> >
>> > but when I run this
>> > select * from charlist_to_table(@.promo_code,default)
>> > I get the following
>> >
>> > listpos str nstr
>> > 1 SWA35-2948 SWA35-2948
>> > 2 SWAP2-2892 SWAP2-2892
>> > 3 SWA27-2946 SWA27-2946
>> > 4 GRE1-2936 GRE1-2936
>> > 5
>> >
>> >
>> > I should have 10 listpos and there still spaces in front out the other
>> > values.
>> > so this only returns the first row's value for code SWA35-2948
>> > select a.* from dbo.swp_camps as a
>> > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes =>> > b.nstr
>> >
>> >
>> > Any suggestions. Thanks, Lisa
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> I call it using default keyword.
>> >>
>> >> select str from charlist_to_talbe(@.codes,default)
>> >>
>> >> I use a join.
>> >>
>> >> select a.* from dbo.tbl_codes a inner join
>> >> charlist_to_table(@.CODES,Default)
>> >> b on a.codes = b.str
>> >>
>> >> change b.str to b.nstr depending on the datatype of a.codes.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
>> >> > Hi, I found the following function on this site and am trying to use
>> >> > it
>> >> > my
>> >> > reports.
>> >> > The dataset for my mvp is different from my stored proc I'm using.
>> >> > The data set for my mvp is simple
>> >> >
>> >> > codes dataset =>> >> > select distinct codes from tbl_codes
>> >> >
>> >> > values are
>> >> > AAA-2222
>> >> > BBB-3333
>> >> > CCC-444
>> >> >
>> >> > In my stored procedure I call the function
>> >> >
>> >> > select * from dbo.tbl_codes as a
>> >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
>> >> >
>> >> > the issue is that it only retrives the first code instead of all
>> >> > three.
>> >> >
>> >> > this is how I test it:
>> >> > select nstr from charlist_to_table
>> >> > ('AAA-2222,
>> >> > BBB-3333,
>> >> > CCC-444
>> >> > ',',')
>> >> >
>> >> > I get the following
>> >> > AAA-2222,
>> >> > BBB-3333,
>> >> > CCC-444
>> >> >
>> >> > I don't think the function is working in the sp because there is a
>> >> > space
>> >> > in
>> >> > front of the values. Even when I put a space in the before the
>> >> > codes
>> >> > data
>> >> > set I still only get the data for the first code AAA-2222.
>> >> >
>> >> > Am I missing something in the code below. Thanks, Lisa
>> >> >
>> >> > CREATE FUNCTION [dbo].[charlist_to_table]
>> >> > (@.list ntext, @.delimiter nchar(1) = N',')
>> >> >
>> >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> >> > str varchar(4000),
>> >> > nstr nvarchar(2000)) AS
>> >> > BEGIN
>> >> > DECLARE @.pos int,
>> >> > @.textpos int,
>> >> > @.chunklen smallint,
>> >> > @.tmpstr nvarchar(4000),
>> >> > @.leftover nvarchar(4000),
>> >> > @.tmpval nvarchar(4000)
>> >> > SET @.textpos = 1
>> >> > SET @.leftover = ''
>> >> > WHILE @.textpos <= datalength(@.list) / 2
>> >> > BEGIN
>> >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
>> >> > SET @.textpos = @.textpos + @.chunklen
>> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> >> > WHILE @.pos > 0
>> >> > BEGIN
>> >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> >> > END
>> >> > SET @.leftover = @.tmpstr
>> >> > END
>> >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> >> > ltrim(rtrim(@.leftover)))
>> >> > RETURN
>> >> > END
>> >>
>> >>
>> >>
>>|||I bet it was the issue with the carriage return.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:0B5C65E7-8118-49B2-A4D7-A30ACCD372D5@.microsoft.com...
> never mind. It actually worked when I ran within the sp in ssrs. thanks.
> Before I was testing it in query analyzer.
> "Lisa" wrote:
>> thanks, that worked. But, I still have the space issue
>> listpos str nstr
>> 1 SWA35-2948 SWA35-2948
>> 2 SWAP2-2892 SWAP2-2892
>> 3 SWA27-2946 SWA27-2946
>>
>> in the str and nstr fields all but the first row has spaces in front of
>> the
>> value. This is why it's only returning the first row. thanks for you
>> help.
>> "Bruce L-C [MVP]" wrote:
>> > Make your @.codes larger. At least for the below that is why it is not
>> > working.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> > news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
>> > > Thanks for your help. I understand, but there is still something
>> > > missing.
>> > >
>> > >
>> > > see the test
>> > > declare @.codes varchar(50)
>> > >
>> > > select
>> > > @.codes = ('SWA35-2948,
>> > > SWAP2-2892,
>> > > SWA27-2946,
>> > > GRE1-2936,
>> > > ADM2-2930,
>> > > SWA28-2938,
>> > > SWUA2-2938,
>> > > SWA31-2948,
>> > > SWAP4-2950,
>> > > SWUA3-2938)
>> > > --test
>> > > print @.codes
>> > > this come out correct
>> > > SWA35-2948,
>> > > SWAP2-2892,
>> > > SWA27-2946,
>> > > GRE1-2936,
>> > > ADM2-2930,
>> > > SWA28-2938,
>> > > SWUA2-2938,
>> > > SWA31-2948,
>> > > SWAP4-2950,
>> > > SWUA3-2938
>> > >
>> > > but when I run this
>> > > select * from charlist_to_table(@.promo_code,default)
>> > > I get the following
>> > >
>> > > listpos str nstr
>> > > 1 SWA35-2948 SWA35-2948
>> > > 2 SWAP2-2892 SWAP2-2892
>> > > 3 SWA27-2946 SWA27-2946
>> > > 4 GRE1-2936 GRE1-2936
>> > > 5
>> > >
>> > >
>> > > I should have 10 listpos and there still spaces in front out the
>> > > other
>> > > values.
>> > > so this only returns the first row's value for code SWA35-2948
>> > > select a.* from dbo.swp_camps as a
>> > > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes =>> > > b.nstr
>> > >
>> > >
>> > > Any suggestions. Thanks, Lisa
>> > >
>> > > "Bruce L-C [MVP]" wrote:
>> > >
>> > >> I call it using default keyword.
>> > >>
>> > >> select str from charlist_to_talbe(@.codes,default)
>> > >>
>> > >> I use a join.
>> > >>
>> > >> select a.* from dbo.tbl_codes a inner join
>> > >> charlist_to_table(@.CODES,Default)
>> > >> b on a.codes = b.str
>> > >>
>> > >> change b.str to b.nstr depending on the datatype of a.codes.
>> > >>
>> > >>
>> > >> --
>> > >> Bruce Loehle-Conger
>> > >> MVP SQL Server Reporting Services
>> > >>
>> > >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> > >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
>> > >> > Hi, I found the following function on this site and am trying to
>> > >> > use it
>> > >> > my
>> > >> > reports.
>> > >> > The dataset for my mvp is different from my stored proc I'm using.
>> > >> > The data set for my mvp is simple
>> > >> >
>> > >> > codes dataset =>> > >> > select distinct codes from tbl_codes
>> > >> >
>> > >> > values are
>> > >> > AAA-2222
>> > >> > BBB-3333
>> > >> > CCC-444
>> > >> >
>> > >> > In my stored procedure I call the function
>> > >> >
>> > >> > select * from dbo.tbl_codes as a
>> > >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
>> > >> >
>> > >> > the issue is that it only retrives the first code instead of all
>> > >> > three.
>> > >> >
>> > >> > this is how I test it:
>> > >> > select nstr from charlist_to_table
>> > >> > ('AAA-2222,
>> > >> > BBB-3333,
>> > >> > CCC-444
>> > >> > ',',')
>> > >> >
>> > >> > I get the following
>> > >> > AAA-2222,
>> > >> > BBB-3333,
>> > >> > CCC-444
>> > >> >
>> > >> > I don't think the function is working in the sp because there is a
>> > >> > space
>> > >> > in
>> > >> > front of the values. Even when I put a space in the before the
>> > >> > codes
>> > >> > data
>> > >> > set I still only get the data for the first code AAA-2222.
>> > >> >
>> > >> > Am I missing something in the code below. Thanks, Lisa
>> > >> >
>> > >> > CREATE FUNCTION [dbo].[charlist_to_table]
>> > >> > (@.list ntext, @.delimiter nchar(1) = N',')
>> > >> >
>> > >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> > >> > str varchar(4000),
>> > >> > nstr nvarchar(2000)) AS
>> > >> > BEGIN
>> > >> > DECLARE @.pos int,
>> > >> > @.textpos int,
>> > >> > @.chunklen smallint,
>> > >> > @.tmpstr nvarchar(4000),
>> > >> > @.leftover nvarchar(4000),
>> > >> > @.tmpval nvarchar(4000)
>> > >> > SET @.textpos = 1
>> > >> > SET @.leftover = ''
>> > >> > WHILE @.textpos <= datalength(@.list) / 2
>> > >> > BEGIN
>> > >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> > >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
>> > >> > SET @.textpos = @.textpos + @.chunklen
>> > >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > >> > WHILE @.pos > 0
>> > >> > BEGIN
>> > >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> > >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> > >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> > >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > >> > END
>> > >> > SET @.leftover = @.tmpstr
>> > >> > END
>> > >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> > >> > ltrim(rtrim(@.leftover)))
>> > >> > RETURN
>> > >> > END
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >|||that was it.
It actually makes sense now because in SSRS the MVP is
('AAA-2222,BBB-3333,CCC-444')
I'm just use to writing it like this in sql
('AAA-2222,
BBB-3333,
CCC-444')
Thanks - Lisa
"Bruce L-C [MVP]" wrote:
> Are you putting it on separate lines when you do your test?
> select nstr from charlist_to_table
> ('AAA-2222,
> BBB-3333,
> CCC-444
> ',',')
> Since you are enclosing the whole thing in a string it is included the
> carriage return (which will look like a blank). Do it like this:
> select nstr from charlist_to_table
> ('AAA-2222,BBB-3333,CCC-444',',')
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:FEB9528A-30AB-44DC-A9FD-DBA43412B2CD@.microsoft.com...
> > thanks, that worked. But, I still have the space issue
> > listpos str nstr
> > 1 SWA35-2948 SWA35-2948
> > 2 SWAP2-2892 SWAP2-2892
> > 3 SWA27-2946 SWA27-2946
> >
> >
> > in the str and nstr fields all but the first row has spaces in front of
> > the
> > value. This is why it's only returning the first row. thanks for you
> > help.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Make your @.codes larger. At least for the below that is why it is not
> >> working.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> >> news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> >> > Thanks for your help. I understand, but there is still something
> >> > missing.
> >> >
> >> >
> >> > see the test
> >> > declare @.codes varchar(50)
> >> >
> >> > select
> >> > @.codes = ('SWA35-2948,
> >> > SWAP2-2892,
> >> > SWA27-2946,
> >> > GRE1-2936,
> >> > ADM2-2930,
> >> > SWA28-2938,
> >> > SWUA2-2938,
> >> > SWA31-2948,
> >> > SWAP4-2950,
> >> > SWUA3-2938)
> >> > --test
> >> > print @.codes
> >> > this come out correct
> >> > SWA35-2948,
> >> > SWAP2-2892,
> >> > SWA27-2946,
> >> > GRE1-2936,
> >> > ADM2-2930,
> >> > SWA28-2938,
> >> > SWUA2-2938,
> >> > SWA31-2948,
> >> > SWAP4-2950,
> >> > SWUA3-2938
> >> >
> >> > but when I run this
> >> > select * from charlist_to_table(@.promo_code,default)
> >> > I get the following
> >> >
> >> > listpos str nstr
> >> > 1 SWA35-2948 SWA35-2948
> >> > 2 SWAP2-2892 SWAP2-2892
> >> > 3 SWA27-2946 SWA27-2946
> >> > 4 GRE1-2936 GRE1-2936
> >> > 5
> >> >
> >> >
> >> > I should have 10 listpos and there still spaces in front out the other
> >> > values.
> >> > so this only returns the first row's value for code SWA35-2948
> >> > select a.* from dbo.swp_camps as a
> >> > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes => >> > b.nstr
> >> >
> >> >
> >> > Any suggestions. Thanks, Lisa
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> I call it using default keyword.
> >> >>
> >> >> select str from charlist_to_talbe(@.codes,default)
> >> >>
> >> >> I use a join.
> >> >>
> >> >> select a.* from dbo.tbl_codes a inner join
> >> >> charlist_to_table(@.CODES,Default)
> >> >> b on a.codes = b.str
> >> >>
> >> >> change b.str to b.nstr depending on the datatype of a.codes.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> >> >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> >> >> > Hi, I found the following function on this site and am trying to use
> >> >> > it
> >> >> > my
> >> >> > reports.
> >> >> > The dataset for my mvp is different from my stored proc I'm using.
> >> >> > The data set for my mvp is simple
> >> >> >
> >> >> > codes dataset => >> >> > select distinct codes from tbl_codes
> >> >> >
> >> >> > values are
> >> >> > AAA-2222
> >> >> > BBB-3333
> >> >> > CCC-444
> >> >> >
> >> >> > In my stored procedure I call the function
> >> >> >
> >> >> > select * from dbo.tbl_codes as a
> >> >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> >> >> >
> >> >> > the issue is that it only retrives the first code instead of all
> >> >> > three.
> >> >> >
> >> >> > this is how I test it:
> >> >> > select nstr from charlist_to_table
> >> >> > ('AAA-2222,
> >> >> > BBB-3333,
> >> >> > CCC-444
> >> >> > ',',')
> >> >> >
> >> >> > I get the following
> >> >> > AAA-2222,
> >> >> > BBB-3333,
> >> >> > CCC-444
> >> >> >
> >> >> > I don't think the function is working in the sp because there is a
> >> >> > space
> >> >> > in
> >> >> > front of the values. Even when I put a space in the before the
> >> >> > codes
> >> >> > data
> >> >> > set I still only get the data for the first code AAA-2222.
> >> >> >
> >> >> > Am I missing something in the code below. Thanks, Lisa
> >> >> >
> >> >> > CREATE FUNCTION [dbo].[charlist_to_table]
> >> >> > (@.list ntext, @.delimiter nchar(1) = N',')
> >> >> >
> >> >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> >> >> > str varchar(4000),
> >> >> > nstr nvarchar(2000)) AS
> >> >> > BEGIN
> >> >> > DECLARE @.pos int,
> >> >> > @.textpos int,
> >> >> > @.chunklen smallint,
> >> >> > @.tmpstr nvarchar(4000),
> >> >> > @.leftover nvarchar(4000),
> >> >> > @.tmpval nvarchar(4000)
> >> >> > SET @.textpos = 1
> >> >> > SET @.leftover = ''
> >> >> > WHILE @.textpos <= datalength(@.list) / 2
> >> >> > BEGIN
> >> >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> >> >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> >> >> > SET @.textpos = @.textpos + @.chunklen
> >> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >> >> > WHILE @.pos > 0
> >> >> > BEGIN
> >> >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> >> >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> >> >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> >> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >> >> > END
> >> >> > SET @.leftover = @.tmpstr
> >> >> > END
> >> >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> >> >> > ltrim(rtrim(@.leftover)))
> >> >> > RETURN
> >> >> > END
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
reports.
The dataset for my mvp is different from my stored proc I'm using.
The data set for my mvp is simple
codes dataset = select distinct codes from tbl_codes
values are
AAA-2222
BBB-3333
CCC-444
In my stored procedure I call the function
select * from dbo.tbl_codes as a
where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
the issue is that it only retrives the first code instead of all three.
this is how I test it:
select nstr from charlist_to_table
('AAA-2222,
BBB-3333,
CCC-444
',',')
I get the following
AAA-2222,
BBB-3333,
CCC-444
I don't think the function is working in the sp because there is a space in
front of the values. Even when I put a space in the before the codes data
set I still only get the data for the first code AAA-2222.
Am I missing something in the code below. Thanks, Lisa
CREATE FUNCTION [dbo].[charlist_to_table]
(@.list ntext, @.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
ENDI call it using default keyword.
select str from charlist_to_talbe(@.codes,default)
I use a join.
select a.* from dbo.tbl_codes a inner join charlist_to_table(@.CODES,Default)
b on a.codes = b.str
change b.str to b.nstr depending on the datatype of a.codes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> Hi, I found the following function on this site and am trying to use it my
> reports.
> The dataset for my mvp is different from my stored proc I'm using.
> The data set for my mvp is simple
> codes dataset => select distinct codes from tbl_codes
> values are
> AAA-2222
> BBB-3333
> CCC-444
> In my stored procedure I call the function
> select * from dbo.tbl_codes as a
> where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> the issue is that it only retrives the first code instead of all three.
> this is how I test it:
> select nstr from charlist_to_table
> ('AAA-2222,
> BBB-3333,
> CCC-444
> ',',')
> I get the following
> AAA-2222,
> BBB-3333,
> CCC-444
> I don't think the function is working in the sp because there is a space
> in
> front of the values. Even when I put a space in the before the codes data
> set I still only get the data for the first code AAA-2222.
> Am I missing something in the code below. Thanks, Lisa
> CREATE FUNCTION [dbo].[charlist_to_table]
> (@.list ntext, @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END|||Thanks for your help. I understand, but there is still something missing.
see the test
declare @.codes varchar(50)
select
@.codes = ('SWA35-2948,
SWAP2-2892,
SWA27-2946,
GRE1-2936,
ADM2-2930,
SWA28-2938,
SWUA2-2938,
SWA31-2948,
SWAP4-2950,
SWUA3-2938)
--test
print @.codes
this come out correct
SWA35-2948,
SWAP2-2892,
SWA27-2946,
GRE1-2936,
ADM2-2930,
SWA28-2938,
SWUA2-2938,
SWA31-2948,
SWAP4-2950,
SWUA3-2938
but when I run this
select * from charlist_to_table(@.promo_code,default)
I get the following
listpos str nstr
1 SWA35-2948 SWA35-2948
2 SWAP2-2892 SWAP2-2892
3 SWA27-2946 SWA27-2946
4 GRE1-2936 GRE1-2936
5
I should have 10 listpos and there still spaces in front out the other values.
so this only returns the first row's value for code SWA35-2948
select a.* from dbo.swp_camps as a
inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
Any suggestions. Thanks, Lisa
"Bruce L-C [MVP]" wrote:
> I call it using default keyword.
> select str from charlist_to_talbe(@.codes,default)
> I use a join.
> select a.* from dbo.tbl_codes a inner join charlist_to_table(@.CODES,Default)
> b on a.codes = b.str
> change b.str to b.nstr depending on the datatype of a.codes.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> > Hi, I found the following function on this site and am trying to use it my
> > reports.
> > The dataset for my mvp is different from my stored proc I'm using.
> > The data set for my mvp is simple
> >
> > codes dataset => > select distinct codes from tbl_codes
> >
> > values are
> > AAA-2222
> > BBB-3333
> > CCC-444
> >
> > In my stored procedure I call the function
> >
> > select * from dbo.tbl_codes as a
> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> >
> > the issue is that it only retrives the first code instead of all three.
> >
> > this is how I test it:
> > select nstr from charlist_to_table
> > ('AAA-2222,
> > BBB-3333,
> > CCC-444
> > ',',')
> >
> > I get the following
> > AAA-2222,
> > BBB-3333,
> > CCC-444
> >
> > I don't think the function is working in the sp because there is a space
> > in
> > front of the values. Even when I put a space in the before the codes data
> > set I still only get the data for the first code AAA-2222.
> >
> > Am I missing something in the code below. Thanks, Lisa
> >
> > CREATE FUNCTION [dbo].[charlist_to_table]
> > (@.list ntext, @.delimiter nchar(1) = N',')
> >
> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> > str varchar(4000),
> > nstr nvarchar(2000)) AS
> > BEGIN
> > DECLARE @.pos int,
> > @.textpos int,
> > @.chunklen smallint,
> > @.tmpstr nvarchar(4000),
> > @.leftover nvarchar(4000),
> > @.tmpval nvarchar(4000)
> > SET @.textpos = 1
> > SET @.leftover = ''
> > WHILE @.textpos <= datalength(@.list) / 2
> > BEGIN
> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> > SET @.textpos = @.textpos + @.chunklen
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > WHILE @.pos > 0
> > BEGIN
> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > END
> > SET @.leftover = @.tmpstr
> > END
> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> > ltrim(rtrim(@.leftover)))
> > RETURN
> > END
>
>|||I meant this above
select * from charlist_to_table(@.codes,default)
"Lisa" wrote:
> Thanks for your help. I understand, but there is still something missing.
>
> see the test
> declare @.codes varchar(50)
> select
> @.codes = ('SWA35-2948,
> SWAP2-2892,
> SWA27-2946,
> GRE1-2936,
> ADM2-2930,
> SWA28-2938,
> SWUA2-2938,
> SWA31-2948,
> SWAP4-2950,
> SWUA3-2938)
> --test
> print @.codes
> this come out correct
> SWA35-2948,
> SWAP2-2892,
> SWA27-2946,
> GRE1-2936,
> ADM2-2930,
> SWA28-2938,
> SWUA2-2938,
> SWA31-2948,
> SWAP4-2950,
> SWUA3-2938
> but when I run this
> select * from charlist_to_table(@.promo_code,default)
> I get the following
> listpos str nstr
> 1 SWA35-2948 SWA35-2948
> 2 SWAP2-2892 SWAP2-2892
> 3 SWA27-2946 SWA27-2946
> 4 GRE1-2936 GRE1-2936
> 5
>
> I should have 10 listpos and there still spaces in front out the other values.
> so this only returns the first row's value for code SWA35-2948
> select a.* from dbo.swp_camps as a
> inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
>
> Any suggestions. Thanks, Lisa
> "Bruce L-C [MVP]" wrote:
> > I call it using default keyword.
> >
> > select str from charlist_to_talbe(@.codes,default)
> >
> > I use a join.
> >
> > select a.* from dbo.tbl_codes a inner join charlist_to_table(@.CODES,Default)
> > b on a.codes = b.str
> >
> > change b.str to b.nstr depending on the datatype of a.codes.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> > news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> > > Hi, I found the following function on this site and am trying to use it my
> > > reports.
> > > The dataset for my mvp is different from my stored proc I'm using.
> > > The data set for my mvp is simple
> > >
> > > codes dataset => > > select distinct codes from tbl_codes
> > >
> > > values are
> > > AAA-2222
> > > BBB-3333
> > > CCC-444
> > >
> > > In my stored procedure I call the function
> > >
> > > select * from dbo.tbl_codes as a
> > > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> > >
> > > the issue is that it only retrives the first code instead of all three.
> > >
> > > this is how I test it:
> > > select nstr from charlist_to_table
> > > ('AAA-2222,
> > > BBB-3333,
> > > CCC-444
> > > ',',')
> > >
> > > I get the following
> > > AAA-2222,
> > > BBB-3333,
> > > CCC-444
> > >
> > > I don't think the function is working in the sp because there is a space
> > > in
> > > front of the values. Even when I put a space in the before the codes data
> > > set I still only get the data for the first code AAA-2222.
> > >
> > > Am I missing something in the code below. Thanks, Lisa
> > >
> > > CREATE FUNCTION [dbo].[charlist_to_table]
> > > (@.list ntext, @.delimiter nchar(1) = N',')
> > >
> > > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> > > str varchar(4000),
> > > nstr nvarchar(2000)) AS
> > > BEGIN
> > > DECLARE @.pos int,
> > > @.textpos int,
> > > @.chunklen smallint,
> > > @.tmpstr nvarchar(4000),
> > > @.leftover nvarchar(4000),
> > > @.tmpval nvarchar(4000)
> > > SET @.textpos = 1
> > > SET @.leftover = ''
> > > WHILE @.textpos <= datalength(@.list) / 2
> > > BEGIN
> > > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> > > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> > > SET @.textpos = @.textpos + @.chunklen
> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > > WHILE @.pos > 0
> > > BEGIN
> > > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> > > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> > > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > > END
> > > SET @.leftover = @.tmpstr
> > > END
> > > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> > > ltrim(rtrim(@.leftover)))
> > > RETURN
> > > END
> >
> >
> >|||Make your @.codes larger. At least for the below that is why it is not
working.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> Thanks for your help. I understand, but there is still something missing.
>
> see the test
> declare @.codes varchar(50)
> select
> @.codes = ('SWA35-2948,
> SWAP2-2892,
> SWA27-2946,
> GRE1-2936,
> ADM2-2930,
> SWA28-2938,
> SWUA2-2938,
> SWA31-2948,
> SWAP4-2950,
> SWUA3-2938)
> --test
> print @.codes
> this come out correct
> SWA35-2948,
> SWAP2-2892,
> SWA27-2946,
> GRE1-2936,
> ADM2-2930,
> SWA28-2938,
> SWUA2-2938,
> SWA31-2948,
> SWAP4-2950,
> SWUA3-2938
> but when I run this
> select * from charlist_to_table(@.promo_code,default)
> I get the following
> listpos str nstr
> 1 SWA35-2948 SWA35-2948
> 2 SWAP2-2892 SWAP2-2892
> 3 SWA27-2946 SWA27-2946
> 4 GRE1-2936 GRE1-2936
> 5
>
> I should have 10 listpos and there still spaces in front out the other
> values.
> so this only returns the first row's value for code SWA35-2948
> select a.* from dbo.swp_camps as a
> inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
>
> Any suggestions. Thanks, Lisa
> "Bruce L-C [MVP]" wrote:
>> I call it using default keyword.
>> select str from charlist_to_talbe(@.codes,default)
>> I use a join.
>> select a.* from dbo.tbl_codes a inner join
>> charlist_to_table(@.CODES,Default)
>> b on a.codes = b.str
>> change b.str to b.nstr depending on the datatype of a.codes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
>> > Hi, I found the following function on this site and am trying to use it
>> > my
>> > reports.
>> > The dataset for my mvp is different from my stored proc I'm using.
>> > The data set for my mvp is simple
>> >
>> > codes dataset =>> > select distinct codes from tbl_codes
>> >
>> > values are
>> > AAA-2222
>> > BBB-3333
>> > CCC-444
>> >
>> > In my stored procedure I call the function
>> >
>> > select * from dbo.tbl_codes as a
>> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
>> >
>> > the issue is that it only retrives the first code instead of all three.
>> >
>> > this is how I test it:
>> > select nstr from charlist_to_table
>> > ('AAA-2222,
>> > BBB-3333,
>> > CCC-444
>> > ',',')
>> >
>> > I get the following
>> > AAA-2222,
>> > BBB-3333,
>> > CCC-444
>> >
>> > I don't think the function is working in the sp because there is a
>> > space
>> > in
>> > front of the values. Even when I put a space in the before the codes
>> > data
>> > set I still only get the data for the first code AAA-2222.
>> >
>> > Am I missing something in the code below. Thanks, Lisa
>> >
>> > CREATE FUNCTION [dbo].[charlist_to_table]
>> > (@.list ntext, @.delimiter nchar(1) = N',')
>> >
>> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> > str varchar(4000),
>> > nstr nvarchar(2000)) AS
>> > BEGIN
>> > DECLARE @.pos int,
>> > @.textpos int,
>> > @.chunklen smallint,
>> > @.tmpstr nvarchar(4000),
>> > @.leftover nvarchar(4000),
>> > @.tmpval nvarchar(4000)
>> > SET @.textpos = 1
>> > SET @.leftover = ''
>> > WHILE @.textpos <= datalength(@.list) / 2
>> > BEGIN
>> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
>> > SET @.textpos = @.textpos + @.chunklen
>> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > WHILE @.pos > 0
>> > BEGIN
>> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > END
>> > SET @.leftover = @.tmpstr
>> > END
>> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> > ltrim(rtrim(@.leftover)))
>> > RETURN
>> > END
>>|||thanks, that worked. But, I still have the space issue
listpos str nstr
1 SWA35-2948 SWA35-2948
2 SWAP2-2892 SWAP2-2892
3 SWA27-2946 SWA27-2946
in the str and nstr fields all but the first row has spaces in front of the
value. This is why it's only returning the first row. thanks for you help.
"Bruce L-C [MVP]" wrote:
> Make your @.codes larger. At least for the below that is why it is not
> working.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> > Thanks for your help. I understand, but there is still something missing.
> >
> >
> > see the test
> > declare @.codes varchar(50)
> >
> > select
> > @.codes = ('SWA35-2948,
> > SWAP2-2892,
> > SWA27-2946,
> > GRE1-2936,
> > ADM2-2930,
> > SWA28-2938,
> > SWUA2-2938,
> > SWA31-2948,
> > SWAP4-2950,
> > SWUA3-2938)
> > --test
> > print @.codes
> > this come out correct
> > SWA35-2948,
> > SWAP2-2892,
> > SWA27-2946,
> > GRE1-2936,
> > ADM2-2930,
> > SWA28-2938,
> > SWUA2-2938,
> > SWA31-2948,
> > SWAP4-2950,
> > SWUA3-2938
> >
> > but when I run this
> > select * from charlist_to_table(@.promo_code,default)
> > I get the following
> >
> > listpos str nstr
> > 1 SWA35-2948 SWA35-2948
> > 2 SWAP2-2892 SWAP2-2892
> > 3 SWA27-2946 SWA27-2946
> > 4 GRE1-2936 GRE1-2936
> > 5
> >
> >
> > I should have 10 listpos and there still spaces in front out the other
> > values.
> > so this only returns the first row's value for code SWA35-2948
> > select a.* from dbo.swp_camps as a
> > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
> >
> >
> > Any suggestions. Thanks, Lisa
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I call it using default keyword.
> >>
> >> select str from charlist_to_talbe(@.codes,default)
> >>
> >> I use a join.
> >>
> >> select a.* from dbo.tbl_codes a inner join
> >> charlist_to_table(@.CODES,Default)
> >> b on a.codes = b.str
> >>
> >> change b.str to b.nstr depending on the datatype of a.codes.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> >> > Hi, I found the following function on this site and am trying to use it
> >> > my
> >> > reports.
> >> > The dataset for my mvp is different from my stored proc I'm using.
> >> > The data set for my mvp is simple
> >> >
> >> > codes dataset => >> > select distinct codes from tbl_codes
> >> >
> >> > values are
> >> > AAA-2222
> >> > BBB-3333
> >> > CCC-444
> >> >
> >> > In my stored procedure I call the function
> >> >
> >> > select * from dbo.tbl_codes as a
> >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> >> >
> >> > the issue is that it only retrives the first code instead of all three.
> >> >
> >> > this is how I test it:
> >> > select nstr from charlist_to_table
> >> > ('AAA-2222,
> >> > BBB-3333,
> >> > CCC-444
> >> > ',',')
> >> >
> >> > I get the following
> >> > AAA-2222,
> >> > BBB-3333,
> >> > CCC-444
> >> >
> >> > I don't think the function is working in the sp because there is a
> >> > space
> >> > in
> >> > front of the values. Even when I put a space in the before the codes
> >> > data
> >> > set I still only get the data for the first code AAA-2222.
> >> >
> >> > Am I missing something in the code below. Thanks, Lisa
> >> >
> >> > CREATE FUNCTION [dbo].[charlist_to_table]
> >> > (@.list ntext, @.delimiter nchar(1) = N',')
> >> >
> >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> >> > str varchar(4000),
> >> > nstr nvarchar(2000)) AS
> >> > BEGIN
> >> > DECLARE @.pos int,
> >> > @.textpos int,
> >> > @.chunklen smallint,
> >> > @.tmpstr nvarchar(4000),
> >> > @.leftover nvarchar(4000),
> >> > @.tmpval nvarchar(4000)
> >> > SET @.textpos = 1
> >> > SET @.leftover = ''
> >> > WHILE @.textpos <= datalength(@.list) / 2
> >> > BEGIN
> >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> >> > SET @.textpos = @.textpos + @.chunklen
> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >> > WHILE @.pos > 0
> >> > BEGIN
> >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >> > END
> >> > SET @.leftover = @.tmpstr
> >> > END
> >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> >> > ltrim(rtrim(@.leftover)))
> >> > RETURN
> >> > END
> >>
> >>
> >>
>
>|||never mind. It actually worked when I ran within the sp in ssrs. thanks.
Before I was testing it in query analyzer.
"Lisa" wrote:
> thanks, that worked. But, I still have the space issue
> listpos str nstr
> 1 SWA35-2948 SWA35-2948
> 2 SWAP2-2892 SWAP2-2892
> 3 SWA27-2946 SWA27-2946
>
> in the str and nstr fields all but the first row has spaces in front of the
> value. This is why it's only returning the first row. thanks for you help.
> "Bruce L-C [MVP]" wrote:
> > Make your @.codes larger. At least for the below that is why it is not
> > working.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> > news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> > > Thanks for your help. I understand, but there is still something missing.
> > >
> > >
> > > see the test
> > > declare @.codes varchar(50)
> > >
> > > select
> > > @.codes = ('SWA35-2948,
> > > SWAP2-2892,
> > > SWA27-2946,
> > > GRE1-2936,
> > > ADM2-2930,
> > > SWA28-2938,
> > > SWUA2-2938,
> > > SWA31-2948,
> > > SWAP4-2950,
> > > SWUA3-2938)
> > > --test
> > > print @.codes
> > > this come out correct
> > > SWA35-2948,
> > > SWAP2-2892,
> > > SWA27-2946,
> > > GRE1-2936,
> > > ADM2-2930,
> > > SWA28-2938,
> > > SWUA2-2938,
> > > SWA31-2948,
> > > SWAP4-2950,
> > > SWUA3-2938
> > >
> > > but when I run this
> > > select * from charlist_to_table(@.promo_code,default)
> > > I get the following
> > >
> > > listpos str nstr
> > > 1 SWA35-2948 SWA35-2948
> > > 2 SWAP2-2892 SWAP2-2892
> > > 3 SWA27-2946 SWA27-2946
> > > 4 GRE1-2936 GRE1-2936
> > > 5
> > >
> > >
> > > I should have 10 listpos and there still spaces in front out the other
> > > values.
> > > so this only returns the first row's value for code SWA35-2948
> > > select a.* from dbo.swp_camps as a
> > > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
> > >
> > >
> > > Any suggestions. Thanks, Lisa
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> I call it using default keyword.
> > >>
> > >> select str from charlist_to_talbe(@.codes,default)
> > >>
> > >> I use a join.
> > >>
> > >> select a.* from dbo.tbl_codes a inner join
> > >> charlist_to_table(@.CODES,Default)
> > >> b on a.codes = b.str
> > >>
> > >> change b.str to b.nstr depending on the datatype of a.codes.
> > >>
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> > >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> > >> > Hi, I found the following function on this site and am trying to use it
> > >> > my
> > >> > reports.
> > >> > The dataset for my mvp is different from my stored proc I'm using.
> > >> > The data set for my mvp is simple
> > >> >
> > >> > codes dataset => > >> > select distinct codes from tbl_codes
> > >> >
> > >> > values are
> > >> > AAA-2222
> > >> > BBB-3333
> > >> > CCC-444
> > >> >
> > >> > In my stored procedure I call the function
> > >> >
> > >> > select * from dbo.tbl_codes as a
> > >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> > >> >
> > >> > the issue is that it only retrives the first code instead of all three.
> > >> >
> > >> > this is how I test it:
> > >> > select nstr from charlist_to_table
> > >> > ('AAA-2222,
> > >> > BBB-3333,
> > >> > CCC-444
> > >> > ',',')
> > >> >
> > >> > I get the following
> > >> > AAA-2222,
> > >> > BBB-3333,
> > >> > CCC-444
> > >> >
> > >> > I don't think the function is working in the sp because there is a
> > >> > space
> > >> > in
> > >> > front of the values. Even when I put a space in the before the codes
> > >> > data
> > >> > set I still only get the data for the first code AAA-2222.
> > >> >
> > >> > Am I missing something in the code below. Thanks, Lisa
> > >> >
> > >> > CREATE FUNCTION [dbo].[charlist_to_table]
> > >> > (@.list ntext, @.delimiter nchar(1) = N',')
> > >> >
> > >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> > >> > str varchar(4000),
> > >> > nstr nvarchar(2000)) AS
> > >> > BEGIN
> > >> > DECLARE @.pos int,
> > >> > @.textpos int,
> > >> > @.chunklen smallint,
> > >> > @.tmpstr nvarchar(4000),
> > >> > @.leftover nvarchar(4000),
> > >> > @.tmpval nvarchar(4000)
> > >> > SET @.textpos = 1
> > >> > SET @.leftover = ''
> > >> > WHILE @.textpos <= datalength(@.list) / 2
> > >> > BEGIN
> > >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> > >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> > >> > SET @.textpos = @.textpos + @.chunklen
> > >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > >> > WHILE @.pos > 0
> > >> > BEGIN
> > >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> > >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> > >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> > >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > >> > END
> > >> > SET @.leftover = @.tmpstr
> > >> > END
> > >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> > >> > ltrim(rtrim(@.leftover)))
> > >> > RETURN
> > >> > END
> > >>
> > >>
> > >>
> >
> >
> >|||Are you putting it on separate lines when you do your test?
select nstr from charlist_to_table
('AAA-2222,
BBB-3333,
CCC-444
',',')
Since you are enclosing the whole thing in a string it is included the
carriage return (which will look like a blank). Do it like this:
select nstr from charlist_to_table
('AAA-2222,BBB-3333,CCC-444',',')
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:FEB9528A-30AB-44DC-A9FD-DBA43412B2CD@.microsoft.com...
> thanks, that worked. But, I still have the space issue
> listpos str nstr
> 1 SWA35-2948 SWA35-2948
> 2 SWAP2-2892 SWAP2-2892
> 3 SWA27-2946 SWA27-2946
>
> in the str and nstr fields all but the first row has spaces in front of
> the
> value. This is why it's only returning the first row. thanks for you
> help.
> "Bruce L-C [MVP]" wrote:
>> Make your @.codes larger. At least for the below that is why it is not
>> working.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
>> > Thanks for your help. I understand, but there is still something
>> > missing.
>> >
>> >
>> > see the test
>> > declare @.codes varchar(50)
>> >
>> > select
>> > @.codes = ('SWA35-2948,
>> > SWAP2-2892,
>> > SWA27-2946,
>> > GRE1-2936,
>> > ADM2-2930,
>> > SWA28-2938,
>> > SWUA2-2938,
>> > SWA31-2948,
>> > SWAP4-2950,
>> > SWUA3-2938)
>> > --test
>> > print @.codes
>> > this come out correct
>> > SWA35-2948,
>> > SWAP2-2892,
>> > SWA27-2946,
>> > GRE1-2936,
>> > ADM2-2930,
>> > SWA28-2938,
>> > SWUA2-2938,
>> > SWA31-2948,
>> > SWAP4-2950,
>> > SWUA3-2938
>> >
>> > but when I run this
>> > select * from charlist_to_table(@.promo_code,default)
>> > I get the following
>> >
>> > listpos str nstr
>> > 1 SWA35-2948 SWA35-2948
>> > 2 SWAP2-2892 SWAP2-2892
>> > 3 SWA27-2946 SWA27-2946
>> > 4 GRE1-2936 GRE1-2936
>> > 5
>> >
>> >
>> > I should have 10 listpos and there still spaces in front out the other
>> > values.
>> > so this only returns the first row's value for code SWA35-2948
>> > select a.* from dbo.swp_camps as a
>> > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes =>> > b.nstr
>> >
>> >
>> > Any suggestions. Thanks, Lisa
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> I call it using default keyword.
>> >>
>> >> select str from charlist_to_talbe(@.codes,default)
>> >>
>> >> I use a join.
>> >>
>> >> select a.* from dbo.tbl_codes a inner join
>> >> charlist_to_table(@.CODES,Default)
>> >> b on a.codes = b.str
>> >>
>> >> change b.str to b.nstr depending on the datatype of a.codes.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
>> >> > Hi, I found the following function on this site and am trying to use
>> >> > it
>> >> > my
>> >> > reports.
>> >> > The dataset for my mvp is different from my stored proc I'm using.
>> >> > The data set for my mvp is simple
>> >> >
>> >> > codes dataset =>> >> > select distinct codes from tbl_codes
>> >> >
>> >> > values are
>> >> > AAA-2222
>> >> > BBB-3333
>> >> > CCC-444
>> >> >
>> >> > In my stored procedure I call the function
>> >> >
>> >> > select * from dbo.tbl_codes as a
>> >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
>> >> >
>> >> > the issue is that it only retrives the first code instead of all
>> >> > three.
>> >> >
>> >> > this is how I test it:
>> >> > select nstr from charlist_to_table
>> >> > ('AAA-2222,
>> >> > BBB-3333,
>> >> > CCC-444
>> >> > ',',')
>> >> >
>> >> > I get the following
>> >> > AAA-2222,
>> >> > BBB-3333,
>> >> > CCC-444
>> >> >
>> >> > I don't think the function is working in the sp because there is a
>> >> > space
>> >> > in
>> >> > front of the values. Even when I put a space in the before the
>> >> > codes
>> >> > data
>> >> > set I still only get the data for the first code AAA-2222.
>> >> >
>> >> > Am I missing something in the code below. Thanks, Lisa
>> >> >
>> >> > CREATE FUNCTION [dbo].[charlist_to_table]
>> >> > (@.list ntext, @.delimiter nchar(1) = N',')
>> >> >
>> >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> >> > str varchar(4000),
>> >> > nstr nvarchar(2000)) AS
>> >> > BEGIN
>> >> > DECLARE @.pos int,
>> >> > @.textpos int,
>> >> > @.chunklen smallint,
>> >> > @.tmpstr nvarchar(4000),
>> >> > @.leftover nvarchar(4000),
>> >> > @.tmpval nvarchar(4000)
>> >> > SET @.textpos = 1
>> >> > SET @.leftover = ''
>> >> > WHILE @.textpos <= datalength(@.list) / 2
>> >> > BEGIN
>> >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
>> >> > SET @.textpos = @.textpos + @.chunklen
>> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> >> > WHILE @.pos > 0
>> >> > BEGIN
>> >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> >> > END
>> >> > SET @.leftover = @.tmpstr
>> >> > END
>> >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> >> > ltrim(rtrim(@.leftover)))
>> >> > RETURN
>> >> > END
>> >>
>> >>
>> >>
>>|||I bet it was the issue with the carriage return.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:0B5C65E7-8118-49B2-A4D7-A30ACCD372D5@.microsoft.com...
> never mind. It actually worked when I ran within the sp in ssrs. thanks.
> Before I was testing it in query analyzer.
> "Lisa" wrote:
>> thanks, that worked. But, I still have the space issue
>> listpos str nstr
>> 1 SWA35-2948 SWA35-2948
>> 2 SWAP2-2892 SWAP2-2892
>> 3 SWA27-2946 SWA27-2946
>>
>> in the str and nstr fields all but the first row has spaces in front of
>> the
>> value. This is why it's only returning the first row. thanks for you
>> help.
>> "Bruce L-C [MVP]" wrote:
>> > Make your @.codes larger. At least for the below that is why it is not
>> > working.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> > news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
>> > > Thanks for your help. I understand, but there is still something
>> > > missing.
>> > >
>> > >
>> > > see the test
>> > > declare @.codes varchar(50)
>> > >
>> > > select
>> > > @.codes = ('SWA35-2948,
>> > > SWAP2-2892,
>> > > SWA27-2946,
>> > > GRE1-2936,
>> > > ADM2-2930,
>> > > SWA28-2938,
>> > > SWUA2-2938,
>> > > SWA31-2948,
>> > > SWAP4-2950,
>> > > SWUA3-2938)
>> > > --test
>> > > print @.codes
>> > > this come out correct
>> > > SWA35-2948,
>> > > SWAP2-2892,
>> > > SWA27-2946,
>> > > GRE1-2936,
>> > > ADM2-2930,
>> > > SWA28-2938,
>> > > SWUA2-2938,
>> > > SWA31-2948,
>> > > SWAP4-2950,
>> > > SWUA3-2938
>> > >
>> > > but when I run this
>> > > select * from charlist_to_table(@.promo_code,default)
>> > > I get the following
>> > >
>> > > listpos str nstr
>> > > 1 SWA35-2948 SWA35-2948
>> > > 2 SWAP2-2892 SWAP2-2892
>> > > 3 SWA27-2946 SWA27-2946
>> > > 4 GRE1-2936 GRE1-2936
>> > > 5
>> > >
>> > >
>> > > I should have 10 listpos and there still spaces in front out the
>> > > other
>> > > values.
>> > > so this only returns the first row's value for code SWA35-2948
>> > > select a.* from dbo.swp_camps as a
>> > > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes =>> > > b.nstr
>> > >
>> > >
>> > > Any suggestions. Thanks, Lisa
>> > >
>> > > "Bruce L-C [MVP]" wrote:
>> > >
>> > >> I call it using default keyword.
>> > >>
>> > >> select str from charlist_to_talbe(@.codes,default)
>> > >>
>> > >> I use a join.
>> > >>
>> > >> select a.* from dbo.tbl_codes a inner join
>> > >> charlist_to_table(@.CODES,Default)
>> > >> b on a.codes = b.str
>> > >>
>> > >> change b.str to b.nstr depending on the datatype of a.codes.
>> > >>
>> > >>
>> > >> --
>> > >> Bruce Loehle-Conger
>> > >> MVP SQL Server Reporting Services
>> > >>
>> > >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> > >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
>> > >> > Hi, I found the following function on this site and am trying to
>> > >> > use it
>> > >> > my
>> > >> > reports.
>> > >> > The dataset for my mvp is different from my stored proc I'm using.
>> > >> > The data set for my mvp is simple
>> > >> >
>> > >> > codes dataset =>> > >> > select distinct codes from tbl_codes
>> > >> >
>> > >> > values are
>> > >> > AAA-2222
>> > >> > BBB-3333
>> > >> > CCC-444
>> > >> >
>> > >> > In my stored procedure I call the function
>> > >> >
>> > >> > select * from dbo.tbl_codes as a
>> > >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
>> > >> >
>> > >> > the issue is that it only retrives the first code instead of all
>> > >> > three.
>> > >> >
>> > >> > this is how I test it:
>> > >> > select nstr from charlist_to_table
>> > >> > ('AAA-2222,
>> > >> > BBB-3333,
>> > >> > CCC-444
>> > >> > ',',')
>> > >> >
>> > >> > I get the following
>> > >> > AAA-2222,
>> > >> > BBB-3333,
>> > >> > CCC-444
>> > >> >
>> > >> > I don't think the function is working in the sp because there is a
>> > >> > space
>> > >> > in
>> > >> > front of the values. Even when I put a space in the before the
>> > >> > codes
>> > >> > data
>> > >> > set I still only get the data for the first code AAA-2222.
>> > >> >
>> > >> > Am I missing something in the code below. Thanks, Lisa
>> > >> >
>> > >> > CREATE FUNCTION [dbo].[charlist_to_table]
>> > >> > (@.list ntext, @.delimiter nchar(1) = N',')
>> > >> >
>> > >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> > >> > str varchar(4000),
>> > >> > nstr nvarchar(2000)) AS
>> > >> > BEGIN
>> > >> > DECLARE @.pos int,
>> > >> > @.textpos int,
>> > >> > @.chunklen smallint,
>> > >> > @.tmpstr nvarchar(4000),
>> > >> > @.leftover nvarchar(4000),
>> > >> > @.tmpval nvarchar(4000)
>> > >> > SET @.textpos = 1
>> > >> > SET @.leftover = ''
>> > >> > WHILE @.textpos <= datalength(@.list) / 2
>> > >> > BEGIN
>> > >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> > >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
>> > >> > SET @.textpos = @.textpos + @.chunklen
>> > >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > >> > WHILE @.pos > 0
>> > >> > BEGIN
>> > >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> > >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> > >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> > >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > >> > END
>> > >> > SET @.leftover = @.tmpstr
>> > >> > END
>> > >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> > >> > ltrim(rtrim(@.leftover)))
>> > >> > RETURN
>> > >> > END
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >|||that was it.
It actually makes sense now because in SSRS the MVP is
('AAA-2222,BBB-3333,CCC-444')
I'm just use to writing it like this in sql
('AAA-2222,
BBB-3333,
CCC-444')
Thanks - Lisa
"Bruce L-C [MVP]" wrote:
> Are you putting it on separate lines when you do your test?
> select nstr from charlist_to_table
> ('AAA-2222,
> BBB-3333,
> CCC-444
> ',',')
> Since you are enclosing the whole thing in a string it is included the
> carriage return (which will look like a blank). Do it like this:
> select nstr from charlist_to_table
> ('AAA-2222,BBB-3333,CCC-444',',')
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:FEB9528A-30AB-44DC-A9FD-DBA43412B2CD@.microsoft.com...
> > thanks, that worked. But, I still have the space issue
> > listpos str nstr
> > 1 SWA35-2948 SWA35-2948
> > 2 SWAP2-2892 SWAP2-2892
> > 3 SWA27-2946 SWA27-2946
> >
> >
> > in the str and nstr fields all but the first row has spaces in front of
> > the
> > value. This is why it's only returning the first row. thanks for you
> > help.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Make your @.codes larger. At least for the below that is why it is not
> >> working.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> >> news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> >> > Thanks for your help. I understand, but there is still something
> >> > missing.
> >> >
> >> >
> >> > see the test
> >> > declare @.codes varchar(50)
> >> >
> >> > select
> >> > @.codes = ('SWA35-2948,
> >> > SWAP2-2892,
> >> > SWA27-2946,
> >> > GRE1-2936,
> >> > ADM2-2930,
> >> > SWA28-2938,
> >> > SWUA2-2938,
> >> > SWA31-2948,
> >> > SWAP4-2950,
> >> > SWUA3-2938)
> >> > --test
> >> > print @.codes
> >> > this come out correct
> >> > SWA35-2948,
> >> > SWAP2-2892,
> >> > SWA27-2946,
> >> > GRE1-2936,
> >> > ADM2-2930,
> >> > SWA28-2938,
> >> > SWUA2-2938,
> >> > SWA31-2948,
> >> > SWAP4-2950,
> >> > SWUA3-2938
> >> >
> >> > but when I run this
> >> > select * from charlist_to_table(@.promo_code,default)
> >> > I get the following
> >> >
> >> > listpos str nstr
> >> > 1 SWA35-2948 SWA35-2948
> >> > 2 SWAP2-2892 SWAP2-2892
> >> > 3 SWA27-2946 SWA27-2946
> >> > 4 GRE1-2936 GRE1-2936
> >> > 5
> >> >
> >> >
> >> > I should have 10 listpos and there still spaces in front out the other
> >> > values.
> >> > so this only returns the first row's value for code SWA35-2948
> >> > select a.* from dbo.swp_camps as a
> >> > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes => >> > b.nstr
> >> >
> >> >
> >> > Any suggestions. Thanks, Lisa
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> I call it using default keyword.
> >> >>
> >> >> select str from charlist_to_talbe(@.codes,default)
> >> >>
> >> >> I use a join.
> >> >>
> >> >> select a.* from dbo.tbl_codes a inner join
> >> >> charlist_to_table(@.CODES,Default)
> >> >> b on a.codes = b.str
> >> >>
> >> >> change b.str to b.nstr depending on the datatype of a.codes.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> >> >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> >> >> > Hi, I found the following function on this site and am trying to use
> >> >> > it
> >> >> > my
> >> >> > reports.
> >> >> > The dataset for my mvp is different from my stored proc I'm using.
> >> >> > The data set for my mvp is simple
> >> >> >
> >> >> > codes dataset => >> >> > select distinct codes from tbl_codes
> >> >> >
> >> >> > values are
> >> >> > AAA-2222
> >> >> > BBB-3333
> >> >> > CCC-444
> >> >> >
> >> >> > In my stored procedure I call the function
> >> >> >
> >> >> > select * from dbo.tbl_codes as a
> >> >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> >> >> >
> >> >> > the issue is that it only retrives the first code instead of all
> >> >> > three.
> >> >> >
> >> >> > this is how I test it:
> >> >> > select nstr from charlist_to_table
> >> >> > ('AAA-2222,
> >> >> > BBB-3333,
> >> >> > CCC-444
> >> >> > ',',')
> >> >> >
> >> >> > I get the following
> >> >> > AAA-2222,
> >> >> > BBB-3333,
> >> >> > CCC-444
> >> >> >
> >> >> > I don't think the function is working in the sp because there is a
> >> >> > space
> >> >> > in
> >> >> > front of the values. Even when I put a space in the before the
> >> >> > codes
> >> >> > data
> >> >> > set I still only get the data for the first code AAA-2222.
> >> >> >
> >> >> > Am I missing something in the code below. Thanks, Lisa
> >> >> >
> >> >> > CREATE FUNCTION [dbo].[charlist_to_table]
> >> >> > (@.list ntext, @.delimiter nchar(1) = N',')
> >> >> >
> >> >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> >> >> > str varchar(4000),
> >> >> > nstr nvarchar(2000)) AS
> >> >> > BEGIN
> >> >> > DECLARE @.pos int,
> >> >> > @.textpos int,
> >> >> > @.chunklen smallint,
> >> >> > @.tmpstr nvarchar(4000),
> >> >> > @.leftover nvarchar(4000),
> >> >> > @.tmpval nvarchar(4000)
> >> >> > SET @.textpos = 1
> >> >> > SET @.leftover = ''
> >> >> > WHILE @.textpos <= datalength(@.list) / 2
> >> >> > BEGIN
> >> >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> >> >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> >> >> > SET @.textpos = @.textpos + @.chunklen
> >> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >> >> > WHILE @.pos > 0
> >> >> > BEGIN
> >> >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> >> >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> >> >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> >> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >> >> > END
> >> >> > SET @.leftover = @.tmpstr
> >> >> > END
> >> >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> >> >> > ltrim(rtrim(@.leftover)))
> >> >> > RETURN
> >> >> > END
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Subscribe to:
Posts (Atom)