I am new at this parameter thing. I have a paramater that uses a drop down
list and can select multiple values. How can I let the user choose all of
the values without having to click on all the values individually. And also
How can I get the parameter to import the values into the dropdown list
automatically instead of me having to type them all in?
ThanksThe easiest way I have found to solve your problem is to create a new dataset
to select the values for the parameter. If you create a union for the value
(ALL), and order by the values, (ALL) will be at the top of the list - for
example (SELECT DISTINCT(CUSTNMBR)
, RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
LEN(CUSTNMBR))) AS CUSTNAME
FROM RM00101 (NOLOCK)
UNION
SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
ORDER BY CUSTNAME ASC) . In the report parameters, select available values
from query, and chose your new dataset and the appropriate label and value.
If you want, you can set the nonqueried default to (ALL). In the main
dataset, use a where clause to set the field value equal to the parameter, OR
the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
OR
@.CUSTOMER IS NULL).
I hope this helps.
"Benw" wrote:
> I am new at this parameter thing. I have a paramater that uses a drop down
> list and can select multiple values. How can I let the user choose all of
> the values without having to click on all the values individually. And also
> How can I get the parameter to import the values into the dropdown list
> automatically instead of me having to type them all in?
> Thanks|||Yes that helped ALOT!!! Here is my query for the parameter. I still need to
add the ALL function.
SELECT Empid
FROM smServFault
WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
GROUP BY Empid
ORDER BY Empid
"Joe" wrote:
> The easiest way I have found to solve your problem is to create a new dataset
> to select the values for the parameter. If you create a union for the value
> (ALL), and order by the values, (ALL) will be at the top of the list - for
> example (SELECT DISTINCT(CUSTNMBR)
> , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> LEN(CUSTNMBR))) AS CUSTNAME
> FROM RM00101 (NOLOCK)
> UNION
> SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> from query, and chose your new dataset and the appropriate label and value.
> If you want, you can set the nonqueried default to (ALL). In the main
> dataset, use a where clause to set the field value equal to the parameter, OR
> the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> OR
> @.CUSTOMER IS NULL).
> I hope this helps.
> "Benw" wrote:
> > I am new at this parameter thing. I have a paramater that uses a drop down
> > list and can select multiple values. How can I let the user choose all of
> > the values without having to click on all the values individually. And also
> > How can I get the parameter to import the values into the dropdown list
> > automatically instead of me having to type them all in?
> >
> > Thanks|||Try this. I don't think you need the "GROUP BY", and I normally use
DISTINCT, but I don't know your situation.
SELECT Empid AS EMPLOYEE
FROM smServFault
WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
UNION
SELECT '(ALL)' AS EMPLOYEE
GROUP BY EMPLOYEE
ORDER BY EMPLOYEE
"Benw" wrote:
> Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> add the ALL function.
> SELECT Empid
> FROM smServFault
> WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> GROUP BY Empid
> ORDER BY Empid
>
> "Joe" wrote:
> > The easiest way I have found to solve your problem is to create a new dataset
> > to select the values for the parameter. If you create a union for the value
> > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > example (SELECT DISTINCT(CUSTNMBR)
> > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > LEN(CUSTNMBR))) AS CUSTNAME
> > FROM RM00101 (NOLOCK)
> > UNION
> > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > from query, and chose your new dataset and the appropriate label and value.
> > If you want, you can set the nonqueried default to (ALL). In the main
> > dataset, use a where clause to set the field value equal to the parameter, OR
> > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > OR
> > @.CUSTOMER IS NULL).
> >
> > I hope this helps.
> > "Benw" wrote:
> >
> > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > list and can select multiple values. How can I let the user choose all of
> > > the values without having to click on all the values individually. And also
> > > How can I get the parameter to import the values into the dropdown list
> > > automatically instead of me having to type them all in?
> > >
> > > Thanks|||That worked awesome. Thank you so much for the tips. I have one more
question and I will leave you alone. LOL. I want the user to be able to
choose a blank parameter from the EMPLOYEE. It works fine in the report
writer but on the report server it tells me to enter a value. What should I
do?
"Joe" wrote:
> Try this. I don't think you need the "GROUP BY", and I normally use
> DISTINCT, but I don't know your situation.
> SELECT Empid AS EMPLOYEE
> FROM smServFault
> WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> UNION
> SELECT '(ALL)' AS EMPLOYEE
> GROUP BY EMPLOYEE
> ORDER BY EMPLOYEE
>
>
> "Benw" wrote:
> > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > add the ALL function.
> >
> > SELECT Empid
> > FROM smServFault
> > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > GROUP BY Empid
> > ORDER BY Empid
> >
> >
> > "Joe" wrote:
> >
> > > The easiest way I have found to solve your problem is to create a new dataset
> > > to select the values for the parameter. If you create a union for the value
> > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > example (SELECT DISTINCT(CUSTNMBR)
> > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > LEN(CUSTNMBR))) AS CUSTNAME
> > > FROM RM00101 (NOLOCK)
> > > UNION
> > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > from query, and chose your new dataset and the appropriate label and value.
> > > If you want, you can set the nonqueried default to (ALL). In the main
> > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > OR
> > > @.CUSTOMER IS NULL).
> > >
> > > I hope this helps.
> > > "Benw" wrote:
> > >
> > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > list and can select multiple values. How can I let the user choose all of
> > > > the values without having to click on all the values individually. And also
> > > > How can I get the parameter to import the values into the dropdown list
> > > > automatically instead of me having to type them all in?
> > > >
> > > > Thanks|||I'm afraid I'll have to defer that one to someone else. What results would
you expect to get?
"Benw" wrote:
> That worked awesome. Thank you so much for the tips. I have one more
> question and I will leave you alone. LOL. I want the user to be able to
> choose a blank parameter from the EMPLOYEE. It works fine in the report
> writer but on the report server it tells me to enter a value. What should I
> do?
> "Joe" wrote:
> > Try this. I don't think you need the "GROUP BY", and I normally use
> > DISTINCT, but I don't know your situation.
> >
> > SELECT Empid AS EMPLOYEE
> > FROM smServFault
> > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > UNION
> > SELECT '(ALL)' AS EMPLOYEE
> > GROUP BY EMPLOYEE
> > ORDER BY EMPLOYEE
> >
> >
> >
> >
> > "Benw" wrote:
> >
> > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > add the ALL function.
> > >
> > > SELECT Empid
> > > FROM smServFault
> > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > GROUP BY Empid
> > > ORDER BY Empid
> > >
> > >
> > > "Joe" wrote:
> > >
> > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > to select the values for the parameter. If you create a union for the value
> > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > FROM RM00101 (NOLOCK)
> > > > UNION
> > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > from query, and chose your new dataset and the appropriate label and value.
> > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > OR
> > > > @.CUSTOMER IS NULL).
> > > >
> > > > I hope this helps.
> > > > "Benw" wrote:
> > > >
> > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > list and can select multiple values. How can I let the user choose all of
> > > > > the values without having to click on all the values individually. And also
> > > > > How can I get the parameter to import the values into the dropdown list
> > > > > automatically instead of me having to type them all in?
> > > > >
> > > > > Thanks|||I want to ba able to choose a blank value so I can get all rows with EMPLOYEE
that is blank. I want to choose blank and get a rows that the EMPLOYYEE is
blank. Like I said its weird, it works in in the report writer but not on
the report server.
"Joe" wrote:
> I'm afraid I'll have to defer that one to someone else. What results would
> you expect to get?
> "Benw" wrote:
> > That worked awesome. Thank you so much for the tips. I have one more
> > question and I will leave you alone. LOL. I want the user to be able to
> > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > writer but on the report server it tells me to enter a value. What should I
> > do?
> >
> > "Joe" wrote:
> >
> > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > DISTINCT, but I don't know your situation.
> > >
> > > SELECT Empid AS EMPLOYEE
> > > FROM smServFault
> > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > UNION
> > > SELECT '(ALL)' AS EMPLOYEE
> > > GROUP BY EMPLOYEE
> > > ORDER BY EMPLOYEE
> > >
> > >
> > >
> > >
> > > "Benw" wrote:
> > >
> > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > add the ALL function.
> > > >
> > > > SELECT Empid
> > > > FROM smServFault
> > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > GROUP BY Empid
> > > > ORDER BY Empid
> > > >
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > to select the values for the parameter. If you create a union for the value
> > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > FROM RM00101 (NOLOCK)
> > > > > UNION
> > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > OR
> > > > > @.CUSTOMER IS NULL).
> > > > >
> > > > > I hope this helps.
> > > > > "Benw" wrote:
> > > > >
> > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > the values without having to click on all the values individually. And also
> > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > automatically instead of me having to type them all in?
> > > > > >
> > > > > > Thanks|||I just noticed something with the "ALL". It wont pull anything. I have to
select the individual Employee for it to pull anything. The ALL wont work.
Thanks, you have been a huge help.
"Joe" wrote:
> I'm afraid I'll have to defer that one to someone else. What results would
> you expect to get?
> "Benw" wrote:
> > That worked awesome. Thank you so much for the tips. I have one more
> > question and I will leave you alone. LOL. I want the user to be able to
> > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > writer but on the report server it tells me to enter a value. What should I
> > do?
> >
> > "Joe" wrote:
> >
> > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > DISTINCT, but I don't know your situation.
> > >
> > > SELECT Empid AS EMPLOYEE
> > > FROM smServFault
> > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > UNION
> > > SELECT '(ALL)' AS EMPLOYEE
> > > GROUP BY EMPLOYEE
> > > ORDER BY EMPLOYEE
> > >
> > >
> > >
> > >
> > > "Benw" wrote:
> > >
> > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > add the ALL function.
> > > >
> > > > SELECT Empid
> > > > FROM smServFault
> > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > GROUP BY Empid
> > > > ORDER BY Empid
> > > >
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > to select the values for the parameter. If you create a union for the value
> > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > FROM RM00101 (NOLOCK)
> > > > > UNION
> > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > OR
> > > > > @.CUSTOMER IS NULL).
> > > > >
> > > > > I hope this helps.
> > > > > "Benw" wrote:
> > > > >
> > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > the values without having to click on all the values individually. And also
> > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > automatically instead of me having to type them all in?
> > > > > >
> > > > > > Thanks|||It should. Did you add the @.employee=null to your SQL statement?
"Benw" wrote:
> I just noticed something with the "ALL". It wont pull anything. I have to
> select the individual Employee for it to pull anything. The ALL wont work.
> Thanks, you have been a huge help.
> "Joe" wrote:
> > I'm afraid I'll have to defer that one to someone else. What results would
> > you expect to get?
> >
> > "Benw" wrote:
> >
> > > That worked awesome. Thank you so much for the tips. I have one more
> > > question and I will leave you alone. LOL. I want the user to be able to
> > > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > > writer but on the report server it tells me to enter a value. What should I
> > > do?
> > >
> > > "Joe" wrote:
> > >
> > > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > > DISTINCT, but I don't know your situation.
> > > >
> > > > SELECT Empid AS EMPLOYEE
> > > > FROM smServFault
> > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > UNION
> > > > SELECT '(ALL)' AS EMPLOYEE
> > > > GROUP BY EMPLOYEE
> > > > ORDER BY EMPLOYEE
> > > >
> > > >
> > > >
> > > >
> > > > "Benw" wrote:
> > > >
> > > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > > add the ALL function.
> > > > >
> > > > > SELECT Empid
> > > > > FROM smServFault
> > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > GROUP BY Empid
> > > > > ORDER BY Empid
> > > > >
> > > > >
> > > > > "Joe" wrote:
> > > > >
> > > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > > to select the values for the parameter. If you create a union for the value
> > > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > > FROM RM00101 (NOLOCK)
> > > > > > UNION
> > > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > > OR
> > > > > > @.CUSTOMER IS NULL).
> > > > > >
> > > > > > I hope this helps.
> > > > > > "Benw" wrote:
> > > > > >
> > > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > > the values without having to click on all the values individually. And also
> > > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > > automatically instead of me having to type them all in?
> > > > > > >
> > > > > > > Thanks|||I am so lost now. Here is my EmpId Parameter
SELECT Empid AS EMPLOYEE
FROM smServFault
WHERE (TaskStatus <> 'C')
UNION
SELECT '(ALL)' AS EMPLOYEE
ORDER BY EMPLOYEE
And here is my main query
SELECT smServCall.ShiptoId, smServFault.FaultCodeId, smServFault.Empid,
smServFault.TaskStatus, smServFault.StartDate, smServFault.WarrStart,
smServFault.Notes, smServFault.CauseID,
smServFault.ResolutionID, smServCall.ServiceCallCompleted
FROM smServCall INNER JOIN
smServFault ON smServCall.ServiceCallID =smServFault.ServiceCallId
WHERE (smServFault.TaskStatus <> 'C') AND
(smServCall.ServiceCallCompleted = 0) AND (smServFault.Empid IN (@.vendor))
AND
(smServFault.StartDate = @.date)
ORDER BY smServFault.StartDate
"Joe" wrote:
> It should. Did you add the @.employee=null to your SQL statement?
> "Benw" wrote:
> > I just noticed something with the "ALL". It wont pull anything. I have to
> > select the individual Employee for it to pull anything. The ALL wont work.
> > Thanks, you have been a huge help.
> >
> > "Joe" wrote:
> >
> > > I'm afraid I'll have to defer that one to someone else. What results would
> > > you expect to get?
> > >
> > > "Benw" wrote:
> > >
> > > > That worked awesome. Thank you so much for the tips. I have one more
> > > > question and I will leave you alone. LOL. I want the user to be able to
> > > > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > > > writer but on the report server it tells me to enter a value. What should I
> > > > do?
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > > > DISTINCT, but I don't know your situation.
> > > > >
> > > > > SELECT Empid AS EMPLOYEE
> > > > > FROM smServFault
> > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > UNION
> > > > > SELECT '(ALL)' AS EMPLOYEE
> > > > > GROUP BY EMPLOYEE
> > > > > ORDER BY EMPLOYEE
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Benw" wrote:
> > > > >
> > > > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > > > add the ALL function.
> > > > > >
> > > > > > SELECT Empid
> > > > > > FROM smServFault
> > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > GROUP BY Empid
> > > > > > ORDER BY Empid
> > > > > >
> > > > > >
> > > > > > "Joe" wrote:
> > > > > >
> > > > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > > > to select the values for the parameter. If you create a union for the value
> > > > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > > > FROM RM00101 (NOLOCK)
> > > > > > > UNION
> > > > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > > > OR
> > > > > > > @.CUSTOMER IS NULL).
> > > > > > >
> > > > > > > I hope this helps.
> > > > > > > "Benw" wrote:
> > > > > > >
> > > > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > > > the values without having to click on all the values individually. And also
> > > > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > > > automatically instead of me having to type them all in?
> > > > > > > >
> > > > > > > > Thanks|||Try this.
(smServFault.Empid = @.vendor OR @.vendor IS NULL)
"Benw" wrote:
> I am so lost now. Here is my EmpId Parameter
> SELECT Empid AS EMPLOYEE
> FROM smServFault
> WHERE (TaskStatus <> 'C')
> UNION
> SELECT '(ALL)' AS EMPLOYEE
> ORDER BY EMPLOYEE
> And here is my main query
> SELECT smServCall.ShiptoId, smServFault.FaultCodeId, smServFault.Empid,
> smServFault.TaskStatus, smServFault.StartDate, smServFault.WarrStart,
> smServFault.Notes, smServFault.CauseID,
> smServFault.ResolutionID, smServCall.ServiceCallCompleted
> FROM smServCall INNER JOIN
> smServFault ON smServCall.ServiceCallID => smServFault.ServiceCallId
> WHERE (smServFault.TaskStatus <> 'C') AND
> (smServCall.ServiceCallCompleted = 0) AND (smServFault.Empid IN (@.vendor))
> AND
> (smServFault.StartDate = @.date)
> ORDER BY smServFault.StartDate
> "Joe" wrote:
> > It should. Did you add the @.employee=null to your SQL statement?
> >
> > "Benw" wrote:
> >
> > > I just noticed something with the "ALL". It wont pull anything. I have to
> > > select the individual Employee for it to pull anything. The ALL wont work.
> > > Thanks, you have been a huge help.
> > >
> > > "Joe" wrote:
> > >
> > > > I'm afraid I'll have to defer that one to someone else. What results would
> > > > you expect to get?
> > > >
> > > > "Benw" wrote:
> > > >
> > > > > That worked awesome. Thank you so much for the tips. I have one more
> > > > > question and I will leave you alone. LOL. I want the user to be able to
> > > > > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > > > > writer but on the report server it tells me to enter a value. What should I
> > > > > do?
> > > > >
> > > > > "Joe" wrote:
> > > > >
> > > > > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > > > > DISTINCT, but I don't know your situation.
> > > > > >
> > > > > > SELECT Empid AS EMPLOYEE
> > > > > > FROM smServFault
> > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > UNION
> > > > > > SELECT '(ALL)' AS EMPLOYEE
> > > > > > GROUP BY EMPLOYEE
> > > > > > ORDER BY EMPLOYEE
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Benw" wrote:
> > > > > >
> > > > > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > > > > add the ALL function.
> > > > > > >
> > > > > > > SELECT Empid
> > > > > > > FROM smServFault
> > > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > > GROUP BY Empid
> > > > > > > ORDER BY Empid
> > > > > > >
> > > > > > >
> > > > > > > "Joe" wrote:
> > > > > > >
> > > > > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > > > > to select the values for the parameter. If you create a union for the value
> > > > > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > > > > FROM RM00101 (NOLOCK)
> > > > > > > > UNION
> > > > > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > > > > OR
> > > > > > > > @.CUSTOMER IS NULL).
> > > > > > > >
> > > > > > > > I hope this helps.
> > > > > > > > "Benw" wrote:
> > > > > > > >
> > > > > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > > > > the values without having to click on all the values individually. And also
> > > > > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > > > > automatically instead of me having to type them all in?
> > > > > > > > >
> > > > > > > > > Thanks|||is this for the ALL not working. Because it still wont.
"Joe" wrote:
> Try this.
> (smServFault.Empid = @.vendor OR @.vendor IS NULL)
> "Benw" wrote:
> > I am so lost now. Here is my EmpId Parameter
> >
> > SELECT Empid AS EMPLOYEE
> > FROM smServFault
> > WHERE (TaskStatus <> 'C')
> > UNION
> > SELECT '(ALL)' AS EMPLOYEE
> > ORDER BY EMPLOYEE
> >
> > And here is my main query
> >
> > SELECT smServCall.ShiptoId, smServFault.FaultCodeId, smServFault.Empid,
> > smServFault.TaskStatus, smServFault.StartDate, smServFault.WarrStart,
> > smServFault.Notes, smServFault.CauseID,
> > smServFault.ResolutionID, smServCall.ServiceCallCompleted
> > FROM smServCall INNER JOIN
> > smServFault ON smServCall.ServiceCallID => > smServFault.ServiceCallId
> > WHERE (smServFault.TaskStatus <> 'C') AND
> > (smServCall.ServiceCallCompleted = 0) AND (smServFault.Empid IN (@.vendor))
> > AND
> > (smServFault.StartDate = @.date)
> > ORDER BY smServFault.StartDate
> >
> > "Joe" wrote:
> >
> > > It should. Did you add the @.employee=null to your SQL statement?
> > >
> > > "Benw" wrote:
> > >
> > > > I just noticed something with the "ALL". It wont pull anything. I have to
> > > > select the individual Employee for it to pull anything. The ALL wont work.
> > > > Thanks, you have been a huge help.
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > I'm afraid I'll have to defer that one to someone else. What results would
> > > > > you expect to get?
> > > > >
> > > > > "Benw" wrote:
> > > > >
> > > > > > That worked awesome. Thank you so much for the tips. I have one more
> > > > > > question and I will leave you alone. LOL. I want the user to be able to
> > > > > > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > > > > > writer but on the report server it tells me to enter a value. What should I
> > > > > > do?
> > > > > >
> > > > > > "Joe" wrote:
> > > > > >
> > > > > > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > > > > > DISTINCT, but I don't know your situation.
> > > > > > >
> > > > > > > SELECT Empid AS EMPLOYEE
> > > > > > > FROM smServFault
> > > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > > UNION
> > > > > > > SELECT '(ALL)' AS EMPLOYEE
> > > > > > > GROUP BY EMPLOYEE
> > > > > > > ORDER BY EMPLOYEE
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Benw" wrote:
> > > > > > >
> > > > > > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > > > > > add the ALL function.
> > > > > > > >
> > > > > > > > SELECT Empid
> > > > > > > > FROM smServFault
> > > > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > > > GROUP BY Empid
> > > > > > > > ORDER BY Empid
> > > > > > > >
> > > > > > > >
> > > > > > > > "Joe" wrote:
> > > > > > > >
> > > > > > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > > > > > to select the values for the parameter. If you create a union for the value
> > > > > > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > > > > > FROM RM00101 (NOLOCK)
> > > > > > > > > UNION
> > > > > > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > > > > > OR
> > > > > > > > > @.CUSTOMER IS NULL).
> > > > > > > > >
> > > > > > > > > I hope this helps.
> > > > > > > > > "Benw" wrote:
> > > > > > > > >
> > > > > > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > > > > > the values without having to click on all the values individually. And also
> > > > > > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > > > > > automatically instead of me having to type them all in?
> > > > > > > > > >
> > > > > > > > > > Thanks|||maybe I am missing something here but in RS2005 if you define the parameters
as a multi-value parameter <Select All> is an option isnt it?
"Joe" wrote:
> The easiest way I have found to solve your problem is to create a new dataset
> to select the values for the parameter. If you create a union for the value
> (ALL), and order by the values, (ALL) will be at the top of the list - for
> example (SELECT DISTINCT(CUSTNMBR)
> , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> LEN(CUSTNMBR))) AS CUSTNAME
> FROM RM00101 (NOLOCK)
> UNION
> SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> from query, and chose your new dataset and the appropriate label and value.
> If you want, you can set the nonqueried default to (ALL). In the main
> dataset, use a where clause to set the field value equal to the parameter, OR
> the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> OR
> @.CUSTOMER IS NULL).
> I hope this helps.
> "Benw" wrote:
> > I am new at this parameter thing. I have a paramater that uses a drop down
> > list and can select multiple values. How can I let the user choose all of
> > the values without having to click on all the values individually. And also
> > How can I get the parameter to import the values into the dropdown list
> > automatically instead of me having to type them all in?
> >
> > Thanks|||Yes, your are right, but when I put it on the report server for others to
view, the <Select All> function disappears. I actually figured out what I
was doing wrong.
I fixed it by saying
WHERE field = @.vendor or @.vendor = 'All'
"MJT" wrote:
> maybe I am missing something here but in RS2005 if you define the parameters
> as a multi-value parameter <Select All> is an option isnt it?
> "Joe" wrote:
> > The easiest way I have found to solve your problem is to create a new dataset
> > to select the values for the parameter. If you create a union for the value
> > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > example (SELECT DISTINCT(CUSTNMBR)
> > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > LEN(CUSTNMBR))) AS CUSTNAME
> > FROM RM00101 (NOLOCK)
> > UNION
> > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > from query, and chose your new dataset and the appropriate label and value.
> > If you want, you can set the nonqueried default to (ALL). In the main
> > dataset, use a where clause to set the field value equal to the parameter, OR
> > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > OR
> > @.CUSTOMER IS NULL).
> >
> > I hope this helps.
> > "Benw" wrote:
> >
> > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > list and can select multiple values. How can I let the user choose all of
> > > the values without having to click on all the values individually. And also
> > > How can I get the parameter to import the values into the dropdown list
> > > automatically instead of me having to type them all in?
> > >
> > > Thanks
Showing posts with label paramater. Show all posts
Showing posts with label paramater. Show all posts
Friday, March 23, 2012
paramater values inside IN clause
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesn=B4t retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) =3D NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=3D1
and (requeststate=3D'expd' or requeststate=3D'done' or
requeststate=3D'closed' or requeststate=3D'atrib')
and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
(facturationtype.code =3D request.invoicetype
and request.destinationentity =3D entity.code
and request.service =3D service.code
and request.code=3Drequestitem.request
and item.code =3D requestitem.item
and requestitem.brand =3D brand.code
and facturationtype.code =3D facturationstep.facturationtypecode
and request.campaign=3Dcampaign.code)
and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
service.state=3D1 and brand.state=3D1 and campaign.state=3D1
and entity.state=3D1 and facturationtype.state=3D1 and
request.toinvoice=3D1 and facturationstep.state =3D 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
=3D t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?Which parameter are you using to pass in multiple values? The parameters for
the stored procedure don't seem quite 'large' enough to hold many values in
a comma delimited string.
You are passing them in as a comma delimited string?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?|||For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters =
for
> the stored procedure don't seem quite 'large' enough to hold many values =
in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesn=B4t retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) =3D NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=3D1
> and (requeststate=3D'expd' or requeststate=3D'done' or
> requeststate=3D'closed' or requeststate=3D'atrib')
> and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
> efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
> (facturationtype.code =3D request.invoicetype
> and request.destinationentity =3D entity.code
> and request.service =3D service.code
> and request.code=3Drequestitem.request
> and item.code =3D requestitem.item
> and requestitem.brand =3D brand.code
> and facturationtype.code =3D facturationstep.facturationtypecode
> and request.campaign=3Dcampaign.code)
> and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
> service.state=3D1 and brand.state=3D1 and campaign.state=3D1
> and entity.state=3D1 and facturationtype.state=3D1 and
> request.toinvoice=3D1 and facturationstep.state =3D 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> =3D t2.request) as pesopedido
> from #ttemp t2
>=20
> drop table #ttemp
> GO
>=20
>=20
>=20
> Can anyone help?|||For @.strService, the comma delimited string should have single quotes around
each element. Your example [@.strService ('111,'222')] is incorrect.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters
> for
> the stored procedure don't seem quite 'large' enough to hold many values
> in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesnt retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) = NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=1
> and (requeststate='expd' or requeststate='done' or
> requeststate='closed' or requeststate='atrib')
> and client like @.strClient and efectivedate >= @.dtBeguinDate and
> efectivedate <= @.dtEndDate) and requestitem.state=1 and
> (facturationtype.code = request.invoicetype
> and request.destinationentity = entity.code
> and request.service = service.code
> and request.code=requestitem.request
> and item.code = requestitem.item
> and requestitem.brand = brand.code
> and facturationtype.code = facturationstep.facturationtypecode
> and request.campaign=campaign.code)
> and item.state=1 and requestitem.state=1 and request.state=1 and
> service.state=1 and brand.state=1 and campaign.state=1
> and entity.state=1 and facturationtype.state=1 and
> request.toinvoice=1 and facturationstep.state = 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign = @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> = t2.request) as pesopedido
> from #ttemp t2
> drop table #ttemp
> GO
>
> Can anyone help?|||I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes aro=
und
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Table DDL would help in diagnosis.
If service.code is a numeric datatype then, @.strService ('111,222') would be
correct. If service.code is a character datatype then each comma separated
element needs quotes around it.
Please verify the datatype for service.code.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes
> around
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would=
be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:
ers
ues
me
=3D1|||IN() list in SQL cannot accept a parameter. Either for scalar values use the
equality operator ( = ) or for multiple values use an appropriate technique
detailed at:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||The way I see it, @.strBrand has a length problem.
It's defined as a varchar(10), and with @.strService('dsmt','merch'), you are
trying to push 14 characters into it.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151085462.464011.201720@.c74g2000cwc.googlegroups.com...
Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would
> be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:|||Your problem is, quite simply, that you're using IN wrong. This is what
you're telling SQL Server when you pass it a @.strService value of
('dsmt,merch'):
@.strService IN ('dsmt,merch')
What you're trying to achieve is this:
@.strService IN ('dsmt', 'merch')
The way to do this is to break up your comma-delimited string into a temp
table or table variable and join/subquery on it. Here's one method:
http://groups.google.com/group/micr...de=source&hl=en
Here's more methods: http://www.sommarskog.se/arrays-in-sql.html
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?sql
the parameters inside a IN clause: it doesn=B4t retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) =3D NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=3D1
and (requeststate=3D'expd' or requeststate=3D'done' or
requeststate=3D'closed' or requeststate=3D'atrib')
and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
(facturationtype.code =3D request.invoicetype
and request.destinationentity =3D entity.code
and request.service =3D service.code
and request.code=3Drequestitem.request
and item.code =3D requestitem.item
and requestitem.brand =3D brand.code
and facturationtype.code =3D facturationstep.facturationtypecode
and request.campaign=3Dcampaign.code)
and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
service.state=3D1 and brand.state=3D1 and campaign.state=3D1
and entity.state=3D1 and facturationtype.state=3D1 and
request.toinvoice=3D1 and facturationstep.state =3D 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
=3D t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?Which parameter are you using to pass in multiple values? The parameters for
the stored procedure don't seem quite 'large' enough to hold many values in
a comma delimited string.
You are passing them in as a comma delimited string?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?|||For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters =
for
> the stored procedure don't seem quite 'large' enough to hold many values =
in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesn=B4t retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) =3D NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=3D1
> and (requeststate=3D'expd' or requeststate=3D'done' or
> requeststate=3D'closed' or requeststate=3D'atrib')
> and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
> efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
> (facturationtype.code =3D request.invoicetype
> and request.destinationentity =3D entity.code
> and request.service =3D service.code
> and request.code=3Drequestitem.request
> and item.code =3D requestitem.item
> and requestitem.brand =3D brand.code
> and facturationtype.code =3D facturationstep.facturationtypecode
> and request.campaign=3Dcampaign.code)
> and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
> service.state=3D1 and brand.state=3D1 and campaign.state=3D1
> and entity.state=3D1 and facturationtype.state=3D1 and
> request.toinvoice=3D1 and facturationstep.state =3D 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> =3D t2.request) as pesopedido
> from #ttemp t2
>=20
> drop table #ttemp
> GO
>=20
>=20
>=20
> Can anyone help?|||For @.strService, the comma delimited string should have single quotes around
each element. Your example [@.strService ('111,'222')] is incorrect.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters
> for
> the stored procedure don't seem quite 'large' enough to hold many values
> in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesnt retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) = NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=1
> and (requeststate='expd' or requeststate='done' or
> requeststate='closed' or requeststate='atrib')
> and client like @.strClient and efectivedate >= @.dtBeguinDate and
> efectivedate <= @.dtEndDate) and requestitem.state=1 and
> (facturationtype.code = request.invoicetype
> and request.destinationentity = entity.code
> and request.service = service.code
> and request.code=requestitem.request
> and item.code = requestitem.item
> and requestitem.brand = brand.code
> and facturationtype.code = facturationstep.facturationtypecode
> and request.campaign=campaign.code)
> and item.state=1 and requestitem.state=1 and request.state=1 and
> service.state=1 and brand.state=1 and campaign.state=1
> and entity.state=1 and facturationtype.state=1 and
> request.toinvoice=1 and facturationstep.state = 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign = @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> = t2.request) as pesopedido
> from #ttemp t2
> drop table #ttemp
> GO
>
> Can anyone help?|||I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes aro=
und
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Table DDL would help in diagnosis.
If service.code is a numeric datatype then, @.strService ('111,222') would be
correct. If service.code is a character datatype then each comma separated
element needs quotes around it.
Please verify the datatype for service.code.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes
> around
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would=
be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:
ers
ues
me
=3D1|||IN() list in SQL cannot accept a parameter. Either for scalar values use the
equality operator ( = ) or for multiple values use an appropriate technique
detailed at:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||The way I see it, @.strBrand has a length problem.
It's defined as a varchar(10), and with @.strService('dsmt','merch'), you are
trying to push 14 characters into it.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151085462.464011.201720@.c74g2000cwc.googlegroups.com...
Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would
> be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:|||Your problem is, quite simply, that you're using IN wrong. This is what
you're telling SQL Server when you pass it a @.strService value of
('dsmt,merch'):
@.strService IN ('dsmt,merch')
What you're trying to achieve is this:
@.strService IN ('dsmt', 'merch')
The way to do this is to break up your comma-delimited string into a temp
table or table variable and join/subquery on it. Here's one method:
http://groups.google.com/group/micr...de=source&hl=en
Here's more methods: http://www.sommarskog.se/arrays-in-sql.html
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?sql
paramater problem
is there any solution to use null values to the parameter
my sql commandtext=
"select * from info where name=@.name and surname=@.surname"
in visual basic .net
info table data=
name surname
_______ _______________
1 null
2 null
i execute my program and use this commandtext
but i cant find any solution because of the null values
what can i do at visual basic .net or at sql server to solve this
bafidi wrote:
> is there any solution to use null values to the parameter
> my sql commandtext=
> "select * from info where name=@.name and surname=@.surname"
> in visual basic .net
> info table data=
> name surname
> _______ _______________
> 1 null
> 2 null
> i execute my program and use this commandtext
> but i cant find any solution because of the null values
> what can i do at visual basic .net or at sql server to solve this
Use can use a LIKE clause instead. Always add a '%' to the end of the
parameter value and never use a NULL. You should also explicitly define
all columns you require rather than using SELECT *.
Select
Col1,
Col2
From
dbo.Info
Where
name LIKE @.Name
and
surname LIKE @.surname
I might consider using a clustered index on the surname and a
non-clustered index on name. That way, you are covered in all cases.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Use a WHERE clause, no?
SELECT * FROM tblInfo
where vchName = @.name AND vchSurName = @.surname
AND @.name IS NOT NULL
AND @.surname IS NOT NULL
Is that okay for U?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekLjiO2wFHA.2880@.TK2MSFTNGP12.phx.gbl...
> bafidi wrote:
> Use can use a LIKE clause instead. Always add a '%' to the end of the
> parameter value and never use a NULL. You should also explicitly define
> all columns you require rather than using SELECT *.
> Select
> Col1,
> Col2
> From
> dbo.Info
> Where
> name LIKE @.Name
> and
> surname LIKE @.surname
> I might consider using a clustered index on the surname and a
> non-clustered index on name. That way, you are covered in all cases.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||John Smith wrote:
> Use a WHERE clause, no?
> SELECT * FROM tblInfo
> where vchName = @.name AND vchSurName = @.surname
> AND @.name IS NOT NULL
> AND @.surname IS NOT NULL
> Is that okay for U?
Why do you need the IS NOT NULL? Nothing is equal to NULL, so you're not
going to ever get NULL values back. And what happened to the query I
posted with the LIKE clauses?
Maybe you could explain your requirements in more detail. Do you need to
be able to search for NULL values? If so, you need to use WHERE COLNAME
IS NULL syntax. If you don't need NULL values and need to search on
wildcards, then look at the example I posted.
David Gugick
Quest Software
www.imceda.com
www.quest.com
my sql commandtext=
"select * from info where name=@.name and surname=@.surname"
in visual basic .net
info table data=
name surname
_______ _______________
1 null
2 null
i execute my program and use this commandtext
but i cant find any solution because of the null values
what can i do at visual basic .net or at sql server to solve this
bafidi wrote:
> is there any solution to use null values to the parameter
> my sql commandtext=
> "select * from info where name=@.name and surname=@.surname"
> in visual basic .net
> info table data=
> name surname
> _______ _______________
> 1 null
> 2 null
> i execute my program and use this commandtext
> but i cant find any solution because of the null values
> what can i do at visual basic .net or at sql server to solve this
Use can use a LIKE clause instead. Always add a '%' to the end of the
parameter value and never use a NULL. You should also explicitly define
all columns you require rather than using SELECT *.
Select
Col1,
Col2
From
dbo.Info
Where
name LIKE @.Name
and
surname LIKE @.surname
I might consider using a clustered index on the surname and a
non-clustered index on name. That way, you are covered in all cases.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Use a WHERE clause, no?
SELECT * FROM tblInfo
where vchName = @.name AND vchSurName = @.surname
AND @.name IS NOT NULL
AND @.surname IS NOT NULL
Is that okay for U?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekLjiO2wFHA.2880@.TK2MSFTNGP12.phx.gbl...
> bafidi wrote:
> Use can use a LIKE clause instead. Always add a '%' to the end of the
> parameter value and never use a NULL. You should also explicitly define
> all columns you require rather than using SELECT *.
> Select
> Col1,
> Col2
> From
> dbo.Info
> Where
> name LIKE @.Name
> and
> surname LIKE @.surname
> I might consider using a clustered index on the surname and a
> non-clustered index on name. That way, you are covered in all cases.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||John Smith wrote:
> Use a WHERE clause, no?
> SELECT * FROM tblInfo
> where vchName = @.name AND vchSurName = @.surname
> AND @.name IS NOT NULL
> AND @.surname IS NOT NULL
> Is that okay for U?
Why do you need the IS NOT NULL? Nothing is equal to NULL, so you're not
going to ever get NULL values back. And what happened to the query I
posted with the LIKE clauses?
Maybe you could explain your requirements in more detail. Do you need to
be able to search for NULL values? If so, you need to use WHERE COLNAME
IS NULL syntax. If you don't need NULL values and need to search on
wildcards, then look at the example I posted.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Paramater in Case statement
When I enter the following query in the MSSQL 2005 Server Management Studio,
I get results, but when i enter the same query (Minus the top 2 lines) in RS,
i get an a error:
Title: Microsoft Visual Database Tools
Error: The Parameter is incorrect.
Any ideas what i'm doing wrong? This query is just a simplified example
using the northwind db.
DECLARE @.Region AS NVARCHAR(15)
SELECT @.Region = '1'
SELECT *
FROM Customers
WHERE Region = CASE
WHEN @.Region = '1'
THEN 'SP'
END
--
Lucas DargisHi Lucas,
> DECLARE @.Region AS NVARCHAR(15)
> SELECT @.Region = '1'
You are using a variable (@.Region) in your query and if you leave off the
top 2 lines, you are not declaring the variable and setting its value. The
statement needs this.
HTH!
Kind regards - Fred|||thanks Fred.
I left off the top two lines because in RS you specify the parameters in the
'Report > Report Parameters' window.
i found that this is just a bug in the design view of RS. when i Previewed
the report, it worked just fine.
thanks
--
Lucas Dargis
"Fred Block" wrote:
> Hi Lucas,
> > DECLARE @.Region AS NVARCHAR(15)
> > SELECT @.Region = '1'
> You are using a variable (@.Region) in your query and if you leave off the
> top 2 lines, you are not declaring the variable and setting its value. The
> statement needs this.
> HTH!
> Kind regards - Fred
>
>|||This is not true. The way it works, if you have not declared it then RS
knows that it is a query parameter and automatically creates a report
parameter for it. I used the below code against adventureworks and it works:
SELECT *
FROM sales.Customer
WHERE TerritoryID =CASE
WHEN @.Region = '2'
THEN 2
END
Use the generic query designer (the button to switch to generic mode is one
of the buttons to the right of the ...).
Execute the query, you should be prompted for a value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fred Block" <fblock@.no_spams.w-systems.com> wrote in message
news:utQYEFuTHHA.1636@.TK2MSFTNGP02.phx.gbl...
> Hi Lucas,
>> DECLARE @.Region AS NVARCHAR(15)
>> SELECT @.Region = '1'
> You are using a variable (@.Region) in your query and if you leave off the
> top 2 lines, you are not declaring the variable and setting its value. The
> statement needs this.
> HTH!
> Kind regards - Fred
>|||Bruce,
that is what i said... just not as well.
i just gave him the 'check' since i couldn't give it to myself.
--
Lucas Dargis
"Bruce L-C [MVP]" wrote:
> This is not true. The way it works, if you have not declared it then RS
> knows that it is a query parameter and automatically creates a report
> parameter for it. I used the below code against adventureworks and it works:
> SELECT *
> FROM sales.Customer
> WHERE TerritoryID => CASE
> WHEN @.Region = '2'
> THEN 2
> END
> Use the generic query designer (the button to switch to generic mode is one
> of the buttons to the right of the ...).
> Execute the query, you should be prompted for a value.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Fred Block" <fblock@.no_spams.w-systems.com> wrote in message
> news:utQYEFuTHHA.1636@.TK2MSFTNGP02.phx.gbl...
> > Hi Lucas,
> >
> >> DECLARE @.Region AS NVARCHAR(15)
> >> SELECT @.Region = '1'
> >
> > You are using a variable (@.Region) in your query and if you leave off the
> > top 2 lines, you are not declaring the variable and setting its value. The
> > statement needs this.
> >
> > HTH!
> >
> > Kind regards - Fred
> >
>
>
I get results, but when i enter the same query (Minus the top 2 lines) in RS,
i get an a error:
Title: Microsoft Visual Database Tools
Error: The Parameter is incorrect.
Any ideas what i'm doing wrong? This query is just a simplified example
using the northwind db.
DECLARE @.Region AS NVARCHAR(15)
SELECT @.Region = '1'
SELECT *
FROM Customers
WHERE Region = CASE
WHEN @.Region = '1'
THEN 'SP'
END
--
Lucas DargisHi Lucas,
> DECLARE @.Region AS NVARCHAR(15)
> SELECT @.Region = '1'
You are using a variable (@.Region) in your query and if you leave off the
top 2 lines, you are not declaring the variable and setting its value. The
statement needs this.
HTH!
Kind regards - Fred|||thanks Fred.
I left off the top two lines because in RS you specify the parameters in the
'Report > Report Parameters' window.
i found that this is just a bug in the design view of RS. when i Previewed
the report, it worked just fine.
thanks
--
Lucas Dargis
"Fred Block" wrote:
> Hi Lucas,
> > DECLARE @.Region AS NVARCHAR(15)
> > SELECT @.Region = '1'
> You are using a variable (@.Region) in your query and if you leave off the
> top 2 lines, you are not declaring the variable and setting its value. The
> statement needs this.
> HTH!
> Kind regards - Fred
>
>|||This is not true. The way it works, if you have not declared it then RS
knows that it is a query parameter and automatically creates a report
parameter for it. I used the below code against adventureworks and it works:
SELECT *
FROM sales.Customer
WHERE TerritoryID =CASE
WHEN @.Region = '2'
THEN 2
END
Use the generic query designer (the button to switch to generic mode is one
of the buttons to the right of the ...).
Execute the query, you should be prompted for a value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fred Block" <fblock@.no_spams.w-systems.com> wrote in message
news:utQYEFuTHHA.1636@.TK2MSFTNGP02.phx.gbl...
> Hi Lucas,
>> DECLARE @.Region AS NVARCHAR(15)
>> SELECT @.Region = '1'
> You are using a variable (@.Region) in your query and if you leave off the
> top 2 lines, you are not declaring the variable and setting its value. The
> statement needs this.
> HTH!
> Kind regards - Fred
>|||Bruce,
that is what i said... just not as well.
i just gave him the 'check' since i couldn't give it to myself.
--
Lucas Dargis
"Bruce L-C [MVP]" wrote:
> This is not true. The way it works, if you have not declared it then RS
> knows that it is a query parameter and automatically creates a report
> parameter for it. I used the below code against adventureworks and it works:
> SELECT *
> FROM sales.Customer
> WHERE TerritoryID => CASE
> WHEN @.Region = '2'
> THEN 2
> END
> Use the generic query designer (the button to switch to generic mode is one
> of the buttons to the right of the ...).
> Execute the query, you should be prompted for a value.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Fred Block" <fblock@.no_spams.w-systems.com> wrote in message
> news:utQYEFuTHHA.1636@.TK2MSFTNGP02.phx.gbl...
> > Hi Lucas,
> >
> >> DECLARE @.Region AS NVARCHAR(15)
> >> SELECT @.Region = '1'
> >
> > You are using a variable (@.Region) in your query and if you leave off the
> > top 2 lines, you are not declaring the variable and setting its value. The
> > statement needs this.
> >
> > HTH!
> >
> > Kind regards - Fred
> >
>
>
Subscribe to:
Posts (Atom)