multi-value parameter problem - please help

I get an error (Incorrect syntax near ','.) when I choose more than one value
for any of the multi-valued report parameters in the below query. All
parameters, except scheduled_date and tracking_number, are defined as
multi-value. It may be a problem with strings appending. Any suggestions?
SELECT convert(varchar(20),D.deployment_id) AS deployment_id,
D.tracking_number, B.alternate_id, D.name,
CASE D.status WHEN 'PARTIAL_SENT' THEN 'SENDING' ELSE D.status END AS
status, D.sent_date, D.scheduled_date, D.owner_user_id
FROM o_dpl_deployment AS D INNER JOIN
o_bas_brand AS B ON D.environment_id = B.environment_id
INNER JOIN
o_dpl_deployment_type AS DT ON D.deployment_type_id = DT.deployment_type_id
AND B.environment_id = DT.environment_id
WHERE ((D.name IN (@.name)) OR (@.name = '-1'))
AND ((D.tracking_number LIKE @.tracking_number) OR
(@.tracking_number = ''))
AND ((D.promo_code IN (@.promo_code)) or (@.promo_code = '-1'))
AND ((D.scheduled_date >= @.scheduled_date AND
D.scheduled_date < DATEADD(month,1,@.scheduled_date))
OR (@.scheduled_date = convert(datetime,'9499-12-31') AND D.scheduled_date
>= CAST(CONVERT(varchar,GETDATE(),110) as Datetime))
OR (@.scheduled_date = convert(datetime,'9499-12-30') AND D.scheduled_date
>= GETDATE() - 1 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-29') AND D.scheduled_date
>= GETDATE() - 3 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-28') AND D.scheduled_date
>= GETDATE() - 7 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-27') AND D.scheduled_date
>= GETDATE() - 14 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-26') AND D.scheduled_date
>= GETDATE() - 30 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-25') AND D.scheduled_date
>= GETDATE() - 60 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9599-12-31')))
AND ((DT.name IN (@.deployment_type_name)) OR
(@.deployment_type_name = '-1'))
AND ((B.name IN (@.brand_name)) OR (@.brand_name = '-1'))
AND ((D.owner_user_id IN (@.owner_user_id)) OR
(@.owner_user_id = '-1'))
AND ((D.created_by IN (@.created_by)) OR (@.created_by = '-1'))
AND ((D.status IN (@.status)) OR (@.status = '-1'))
AND (D.status = 'SENT' OR D.status = 'PARTIAL_SENT')
UNION
SELECT '-1', '-1', '-1', '-1', '-1', '1753-12-31', '1753-12-31', '-1'
ORDER BY D.scheduled_date DESCYou may want to search in the programming discussion group too. I had a
similar problem.
"Stephanie" wrote:
> I get an error (Incorrect syntax near ','.) when I choose more than one value
> for any of the multi-valued report parameters in the below query. All
> parameters, except scheduled_date and tracking_number, are defined as
> multi-value. It may be a problem with strings appending. Any suggestions?
> SELECT convert(varchar(20),D.deployment_id) AS deployment_id,
> D.tracking_number, B.alternate_id, D.name,
> CASE D.status WHEN 'PARTIAL_SENT' THEN 'SENDING' ELSE D.status END AS
> status, D.sent_date, D.scheduled_date, D.owner_user_id
> FROM o_dpl_deployment AS D INNER JOIN
> o_bas_brand AS B ON D.environment_id = B.environment_id
> INNER JOIN
> o_dpl_deployment_type AS DT ON D.deployment_type_id = DT.deployment_type_id
> AND B.environment_id = DT.environment_id
> WHERE ((D.name IN (@.name)) OR (@.name = '-1'))
> AND ((D.tracking_number LIKE @.tracking_number) OR
> (@.tracking_number = ''))
> AND ((D.promo_code IN (@.promo_code)) or (@.promo_code = '-1'))
> AND ((D.scheduled_date >= @.scheduled_date AND
> D.scheduled_date < DATEADD(month,1,@.scheduled_date))
> OR (@.scheduled_date = convert(datetime,'9499-12-31') AND D.scheduled_date
> >= CAST(CONVERT(varchar,GETDATE(),110) as Datetime))
> OR (@.scheduled_date = convert(datetime,'9499-12-30') AND D.scheduled_date
> >= GETDATE() - 1 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-29') AND D.scheduled_date
> >= GETDATE() - 3 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-28') AND D.scheduled_date
> >= GETDATE() - 7 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-27') AND D.scheduled_date
> >= GETDATE() - 14 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-26') AND D.scheduled_date
> >= GETDATE() - 30 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-25') AND D.scheduled_date
> >= GETDATE() - 60 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9599-12-31')))
> AND ((DT.name IN (@.deployment_type_name)) OR
> (@.deployment_type_name = '-1'))
> AND ((B.name IN (@.brand_name)) OR (@.brand_name = '-1'))
> AND ((D.owner_user_id IN (@.owner_user_id)) OR
> (@.owner_user_id = '-1'))
> AND ((D.created_by IN (@.created_by)) OR (@.created_by = '-1'))
> AND ((D.status IN (@.status)) OR (@.status = '-1'))
> AND (D.status = 'SENT' OR D.status = 'PARTIAL_SENT')
> UNION
> SELECT '-1', '-1', '-1', '-1', '-1', '1753-12-31', '1753-12-31', '-1'
> ORDER BY D.scheduled_date DESC
>

Multiple while fetch cursor code

I seem to have a few problems with the below double cursor procedure. Probably due to the fact that I have two while loops based on fetch status. Or?

What I want to do is select out a series of numbers in medlemmer_cursor(currently set to only one number, for which I know I get results) and for each of these numbers select their MCPS code and gather these in a single string.

For some reason the outpiut (the insert into statement) returns the correct number 9611 but the second variable @.instrumentlinje remains empty.

If I test the select clause for 9611, it gets 4 lines. So to me its like the "SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument" statement doesn't execute.

DELETE FROM ALL_tbl_instrumentkoder

DECLARE @.medlem int
DECLARE @.instrument varchar(10)
DECLARE @.instrumentlinje varchar(150)

DECLARE medlemmer_cursor CURSOR FOR
SELECT medlemsnummer
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

DECLARE instrumenter_cursor CURSOR FOR
SELECT [MCPS Kode]
FROM Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

OPEN medlemmer_cursor

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

WHILE @.@.FETCH_STATUS = 0
BEGIN

OPEN instrumenter_cursor
FETCH NEXT FROM instrumenter_cursor INTO @.instrument

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument
FETCH NEXT FROM instrumenter_cursor INTO @.instrument
END

CLOSE instrumenter_cursor

INSERT INTO ALL_tbl_instrumentkoder VALUES(@.medlem, @.instrumentlinje)

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

END

CLOSE medlemmer_cursor
DEALLOCATE medlemmer_cursor
DEALLOCATE instrumenter_cursorWell, I suspect the problem is related to referencing a variable in your cursor definition, but you shouldn't be using a cursor anyway.

Here is a simpler (non-cursor) method:

First, create this function:
create function dbo.instrumentlinje(@.medlem int)
returns varchar(4000) as
begin
declare @.instrumentlinje
select @.instrumentlinje = isnull(@.instrumentlinje + ' ', '') + MCPS Kode
from Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

return @.instrumentlinje
end

Then, run this code:
insert into ALL_tbl_instrumentkoder
(medlem,
instrumentlinje)
select medlemsnummer,
dbo.instrumentlinje(medlem)
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

Warning! Not tested for syntax errors, and you may need to edit object ownership.|||Basically the same way I did it in Access.. just a greenhorn when it comes to SQL-server.

Thanks man :-)|||TSQL is similar to Access SQL, though there are a few syntactical differences. The concept of avoiding cursors and loops in favor of set-based operations is the same, though.

Multiple Where Clause on One Report

Hello People, Please help. I have a basic report with a parameter in the 'Where" clause called (@.Stat) from the statement below:

" WHERE contractinfo.termdate >= GETDATE()
AND provider.status= 'Active' AND provider.credentialstatus = (@.Stat)"

This variable has one of two values: 'A' or 'B' that the user selects, how do I set it up so that if user selects say 'A' then the Where clause would go to one set of constraints ie

"WHERE contract.description NOT LIKE 'NON%' "

But if the user selects 'B' then the Where clause would go to a different set of constraint ie

"WHERE contract.description LIKE 'NON%' "

Thanks

You might be able to change your WHERE clause from this:

Code Snippet

WHERE contractinfo.termdate >= GETDATE()

AND provider.status= 'Active' AND provider.credentialstatus = (@.Stat)

to this:

Code Snippet

WHERE contractinfo.termdate >= GETDATE()

AND provider.status= 'Active'

AND ( @.stat = 'A' AND contact.description not like 'NON%' OR

@.stat = 'B' AND contact.description like 'NON%'

)

|||Thanks for the help Kent that worked great, the only thing I did different for my report was leaving the original (@.Stat) parameter in also otherwise it would not have filtered it by the "A or B" condition and then adding your script suggestion.. Thanks again

Multiple Table names in result data set.

I have the below query. Basically it takes from a table the names of other tables. This seems to work but the result data set has tables named as Table, Table1, etc. How can I change the names of the tables in the result set?

Thank you.

Kevin

BEGIN

SET NOCOUNT ON;

DECLARE @.TableName VARCHAR(50)

DECLARE ReasonCategory_Cursor CURSOR FOR

SELECT CATEGORY

FROM CaseNoteReasonCategories

SELECT CATEGORY

FROM CaseNoteReasonCategories

OPEN ReasonCategory_Cursor;

FETCH NEXT FROM ReasonCategory_Cursor INTO @.TableName;

WHILE @.@.FETCH_STATUS = 0

BEGIN

EXEC ('SELECT * FROM ' + @.TableName + ' AS ' + @.TableName);

FETCH NEXT FROM ReasonCategory_Cursor INTO @.TableName;

END;

CLOSE ReasonCategory_Cursor;

DEALLOCATE ReasonCategory_Cursor;

END

Your query returns the names of the tables as stored in the CATEGORY column of the CaseNoteReasonCategories table.

If you want different table names, you might try changing the values in the Category column.

|||

The problem is that the name(s) of the tables do not correspond to the names in the category column. It seems that the names of the tables is fixed at Table, Table1, Table2, etc. This seems to be the case no matter what the names of the tables are in the CATEGORY column.

I have started to look some more into this and this may be an aritfact of ADO.NET. But I am not sure how to override what it is doing so I get the names of the tables corresponding to the names in the CATEGORY column.

|||

I don't believe this is an issue with ADO.NET. Your query is pulling the actual values out of the Category column of the CaseNoteReasonCategories. I suspect that there is some 'test' data in the table and that is what you are seeing.

Your query does not have a WHERE clause, so it will be gathering all rows from CaseNoteReasonCategories, and I suspect Table1, Table2, etc., are the first rows in the table.

Also, in the line below, the + ' AS ' + @.TableName has no value.

EXEC ('SELECT * FROM ' + @.TableName + ' AS ' + @.TableName);

|||

Arnie Rowland wrote:

I don't believe this is an issue with ADO.NET. Your query is pulling the actual values out of the Category column of the CaseNoteReasonCategories. I suspect that there is some 'test' data in the table and that is what you are seeing.

Your query does not have a WHERE clause, so it will be gathering all rows from CaseNoteReasonCategories, and I suspect Table1, Table2, etc., are the first rows in the table.

Also, in the line below, the + ' AS ' + @.TableName has no value.

EXEC ('SELECT * FROM ' + @.TableName + ' AS ' + @.TableName);

The values in the Category column are the names of the tables. These values are NOT Table1, Table2, etc.

I don't have a WHERE clause because I want all of the rows from CaseNoteReasonCategories. Table1, Table2 are not the first rows in this table.

Adding the 'AS' to the end of the query seems to have not effect. I have also tried

EXEC ('SELECT * FROM ' + @.TableName + ' ' + @.TableName);

|||

It is quite a mystery where those table names are being 'automagically' created...

My point was that assigning an alias for the table (with or without using 'AS') has no value since you are not using the alias in your code. It is wasted effort -albeit of little consequence.

multiple statements on insert trigger (mssql2000)

Can I have more than 1 statement fire on an insert trigger as I have
illustrated below, or do I use 2 insert triggers?
.....or is there a better solution?
Thanks Soc.
++++++++++++++++++++++++++++++++++++++++
+++++
CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
FOR INSERT
AS
update table1 set column1=column2 where column3='GREEN' and column4 is null
update table1 set column5='peter' where column3='GREEN' where
column5<>'john'
++++++++++++++++++++++++++++++++++++++++
+++++soc
I'm not sure I inderstand you.
Triggers are fired per statement not per Insert. Why do you perform two
updating on the same table within a trigger, can explain what are you trying
to do?
"soc" <zxc0@.yahoo.com> wrote in message
news:%23al7PS2OFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Can I have more than 1 statement fire on an insert trigger as I have
> illustrated below, or do I use 2 insert triggers?
> .....or is there a better solution?
> Thanks Soc.
> ++++++++++++++++++++++++++++++++++++++++
+++++
> CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
> FOR INSERT
> AS
> update table1 set column1=column2 where column3='GREEN' and column4 is
null
> update table1 set column5='peter' where column3='GREEN' where
> column5<>'john'
> ++++++++++++++++++++++++++++++++++++++++
+++++
>
>|||Can an insert trigger do 2 updates along the lines of the trigger below?
"soc" <zxc0@.yahoo.com> wrote in message
news:%23al7PS2OFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Can I have more than 1 statement fire on an insert trigger as I have
> illustrated below, or do I use 2 insert triggers?
> .....or is there a better solution?
> Thanks Soc.
> ++++++++++++++++++++++++++++++++++++++++
+++++
> CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
> FOR INSERT
> AS
> update table1 set column1=column2 where column3='GREEN' and column4 is
> null
> update table1 set column5='peter' where column3='GREEN' where
> column5<>'john'
> ++++++++++++++++++++++++++++++++++++++++
+++++
>
>|||On Thu, 7 Apr 2005 12:25:52 +0100, soc wrote:

>Can I have more than 1 statement fire on an insert trigger as I have
>illustrated below, or do I use 2 insert triggers?
>.....or is there a better solution?
>Thanks Soc.
> ++++++++++++++++++++++++++++++++++++++++
+++++
>CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
>FOR INSERT
>AS
>update table1 set column1=column2 where column3='GREEN' and column4 is null
>update table1 set column5='peter' where column3='GREEN' where
>column5<>'john'
> ++++++++++++++++++++++++++++++++++++++++
+++++
>
Hi soc,
You can use as many statements as you wish in the code of a trigger. But
you can't use two WHERE clauses in one UPDATE statement (as you do in
your second update).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Multiple Row Headers/Detail?

Can't seem to figure out how to create a staggered, multi-row header and
details like below.
Header Last Name First Name
DOB City
Detail Doe John
1/1/80 San Francisco
Is this possible?
Thanks.Never mind ;) Right-clicking the icons on the left let you add/delete rows.
"Don Miller" <nospam@.nospam.com> wrote in message
news:ekoiAIAEIHA.536@.TK2MSFTNGP06.phx.gbl...
> Can't seem to figure out how to create a staggered, multi-row header and
> details like below.
> Header Last Name First Name
> DOB City
> Detail Doe John
> 1/1/80 San Francisco
> Is this possible?
> Thanks.
>