Help with sproc with one parameter that can contain multiple values

I am trying to get the following procedure to work and I am getting hung up
on the @.strClaim parameter, this could be either 1 or more claim numbers
for one terminal number. I want to be able to get all the claim detail
information for, say, terminal # 1222222abc that are in claims 521, 522,
523, 530.
I don't know how to handle the @.strClaim so that the procedure will for all
claim numbers in that list.
Any help appreciated.
TIA
Nancy
Create Procedure usp_GetClaims
(@.strClaim as Char(10),
@.strTerminal as Char(30))
as
Select X_CLAIMS_NO,X_TERMINAL_NUMBER
from
dbo.X_HCFA_CLAIM
where
Cast(X_CLAIMS_NO as char(10)) IN @.strClaim
AND
X_TERMINAL_NUMBER = @.strTerminal
exec usp_GetClaims '574, 573', 'RMFAHESSSXYHLLLX'To pass a CSV list as a VARCHAR(n) parameter, you will have to use something
different. For various alternatives, refer to:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||Thanks, I think I found what I needed. Great site too!
Nancy
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OVgTMF1oFHA.3316@.tk2msftngp13.phx.gbl...
> To pass a CSV list as a VARCHAR(n) parameter, you will have to use
> something different. For various alternatives, refer to:
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Anith
>

Help with Sending file to server using ftp task

Doe anyone know how to do this. I keep getting an error remote path missing "/" but it doesn't contain a "/".

Mike

If you could give more detail, that would help.|||

I'm trying to connect to a FTP server running on z/OS and has MvS operating system. The remoter parameters In the FTP task editor in ssis calls for a "/" at the beginning of the remote path but the remote path I am uploading to starts out with a single quote and not a forward slash. Is there a way around this?

thanks,

Mike

|||Did you try putting a "/" in front?|||

I figured it out using a ftp script.

|||That's what I have had to resort to at times also. Some of the FTP task functionality just doesn't seem to function like it should.

Help with Select statement

Help me with the following query.
I have employee table as follows
EmpID (Number)
FullName (Text)
ReportTo(Number)
ReportTo field contain number from EmpID
Sample Data from the table
EmpIDFullNameReports To
1Nancy Davolio2
2Andrew Fuller
3Janet Leverling2
4Margaret Peacock2
5Steven Buchanan2
6Michael Suyama5
7Robert King5
8Laura Callahan2
9Anne Dodsworth5
10Andrew Leverling3
11Michael Miller5
12Robert Davolio3
13Nancy Suyama6
14Margaret King7
I would like to create a single query (T-SQL), when I pick an
employee ID, I would like to have the entire list of employees come
under that employee including that employee and all below him/her.
Example. If I pick ID = 5, should get the following list.
EmpIDFullNameReports To
5Steven Buchanan2
6Michael Suyama5
7Robert King5
9Anne Dodsworth5
11Michael Miller5
13Nancy Suyama6
14Margaret King7
The list above shows all staff who report to ID 5 directly, But I want very
who are reporting to staff reporting to staff report to ID 5 and below in the
hieratical organization.
How do I do that query?
Thanks
Harry
Harry J Nathan
see
http://www.mindsdoor.net/SQLTsql/Ret...Hierarchy.html
"Harry J Nathan" wrote:

> Help me with the following query.
> I have employee table as follows
> EmpID (Number)
> FullName (Text)
> ReportTo(Number)
> ReportTo field contain number from EmpID
> Sample Data from the table
> EmpIDFullNameReports To
> 1Nancy Davolio2
> 2Andrew Fuller
> 3Janet Leverling2
> 4Margaret Peacock2
> 5Steven Buchanan2
> 6Michael Suyama5
> 7Robert King5
> 8Laura Callahan2
> 9Anne Dodsworth5
> 10Andrew Leverling3
> 11Michael Miller5
> 12Robert Davolio3
> 13Nancy Suyama6
> 14Margaret King7
>
> I would like to create a single query (T-SQL), when I pick an
> employee ID, I would like to have the entire list of employees come
> under that employee including that employee and all below him/her.
> Example. If I pick ID = 5, should get the following list.
> EmpIDFullNameReports To
> 5Steven Buchanan2
> 6Michael Suyama5
> 7Robert King5
> 9Anne Dodsworth5
> 11Michael Miller5
> 13Nancy Suyama6
> 14Margaret King7
> The list above shows all staff who report to ID 5 directly, But I want very
> who are reporting to staff reporting to staff report to ID 5 and below in the
> hieratical organization.
> How do I do that query?
> Thanks
> Harry
> --
> Harry J Nathan

Help with query that only returns filds with certain characters

Hi,
I need to write a sql statement that only returns records if
a certain field DOESN'T contain a letter between a and d, a number, a #,
and a asterisk. If a different character is found there, it should
return that record.
For example. If we have these records
1. 43242#
2. %3499
3. $$#
4. ak
5. abd43#
6. 4242#44z
7. abc_
8. 342#ab*
9. *(
My query should return 2, 3, 4, 6, 7 and 9
I tried the following:
select * from Table1
where MyField LIKE '%[^0-9]%'
and MyField like '%[^a-d]%'
and it works fine for the numbers and letters, but I don't how to
include the # and the *
Thanks a lot.You can negate the LIKE clause by putting NOT in front of it.
So
SELECT * FROM Table1
WHERE MyField LIKE '%[^0-9]%'
AND MyField LIKE '%[^a-d]%'
AND MyField NOT LIKE '%#%'
AND MyField NOT LIKE '%*%'
One note though, the criteria you mentioned and the examples you gave do not
seem to match up.

But you said you should return rows (and your sample code that you said
works as intended) that do NOT contain a number. Some of those rows clearly
contain a number. Just not sure where you were going with that.
HTH,
John Scragg
"Star" wrote:
> Hi,
> I need to write a sql statement that only returns records if
> a certain field DOESN'T contain a letter between a and d, a number, a #,
> and a asterisk. If a different character is found there, it should
> return that record.
> For example. If we have these records
> 1. 43242#
> 2. %3499
> 3. $$#
> 4. ak
> 5. abd43#
> 6. 4242#44z
> 7. abc_
> 8. 342#ab*
> 9. *(
>
> My query should return 2, 3, 4, 6, 7 and 9
> I tried the following:
> select * from Table1
> where MyField LIKE '%[^0-9]%'
> and MyField like '%[^a-d]%'
> and it works fine for the numbers and letters, but I don't how to
> include the # and the *
> Thanks a lot.
>|||John,
Yes, I think my explanation was a little bit confusing.
I will try to rephrase it.
The query should return rows if the field
does not contain one of these characters:
- Numbers
- Letters (a-d)
- #
- *
For example, the query should return %3499 because
there is a % symbol there and that symbol is not on that list.
I had already tried what you suggested, but doesn't work for me.
If I run it, I wouldn't get 4242#44z back, and I should because it
contains a 'z'
John Scragg wrote:
> You can negate the LIKE clause by putting NOT in front of it.
> So
> SELECT * FROM Table1
> WHERE MyField LIKE '%[^0-9]%'
> AND MyField LIKE '%[^a-d]%'
> AND MyField NOT LIKE '%#%'
> AND MyField NOT LIKE '%*%'
> One note though, the criteria you mentioned and the examples you gave do n
ot
> seem to match up.
>
>
> But you said you should return rows (and your sample code that you said
> works as intended) that do NOT contain a number. Some of those rows clear
ly
> contain a number. Just not sure where you were going with that.
> HTH,
> John Scragg
>
> "Star" wrote:
>|||
You may also need to consider the escape character
e.g.
select * from (select 'a%b' col1 union select 'cde') x
where col1 LIKE '%\%%' ESCAPE ''
returns only 'a%b'|||Thanks, Steven. I will keep in mind.
However, I still haven't found a solution for this problem...
If I do this
select myfield from mytable
where myfield LIKE '%[^0-9]%'
and myfield like '%[^a-d]%'
and myfield not LIKE '%*%'
and myfield not LIKE '%#%'
and myfield LIKE '%\%%' ESCAPE ''
I only get %3499 back.
I really don't know what else to try...|||> I really don't know what else to try...
I've finally worked out (I think) what it is that you need
All records that contain one (or more) characters that are not in
(1234567890abcd#*)
Would that be a fair assumption ?|||
> I really don't know what else to try...
Is this SQL 2000 or SQL2005 ?
You could use a regex match
OR:
select col1 , patindex('%[^abcd1234567890#*]%',col1)
from
(
select
'43242#' col1
UNION SELECT
'%3499' UNION SELECT
'$$#' UNION SELECT
'ak' UNION SELECT
'abd43#' UNION SELECT
'4242#44z' UNION SELECT
'abc_' UNION SELECT
'342#ab*' UNION SELECT
'*('
) x
where patindex('%[^abcd1234567890#*]%',col1) <>0|||On Wed, 02 Nov 2005 14:05:46 -0500, Star wrote:

>John,
>Yes, I think my explanation was a little bit confusing.
>I will try to rephrase it.
>The query should return rows if the field
>does not contain one of these characters:
>- Numbers
>- Letters (a-d)
>- #
>- *
>For example, the query should return %3499 because
>there is a % symbol there and that symbol is not on that list.
Hi Star,
You write "does not contain one of these characters", but your example
suggests that you mean "contains at least one character not in this
list". For '%3499' does contain a number (even four!), yet you want it
returned.
SELECT MyField, other columns
FROM MyTable1
WHERE MyField LIKE '%[^0-9a-d#*]%'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes... I apologize again. I had a really bad day. I should have thought
twice my question.
Sorry about that and thanks for your help.|||Awesome!
That worked. To be honest, I didn't know about that patindex function. I
won't forget next time.
I really really appreciate your help and time.

Help with query NOT IN

I have a view containing column X and column Y and a foreign key F. I
want to filter the view so that it does not contain any rows which are
in the foreign table, which also contain columns X and Y.
I want to do something like this:
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t ON t.f = v.f
WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
ThanksTry,
SELECT * FROM vView v
LEFT OUTER JOIN Tbl t
ON v.x = t.x and v.y = t.y
WHERE t.X is null and t.Y is null
AMB
"larzeb" wrote:

> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
>|||larzeb wrote:
> I have a view containing column X and column Y and a foreign key F. I
> want to filter the view so that it does not contain any rows which are
> in the foreign table, which also contain columns X and Y.
> I want to do something like this:
> SELECT * FROM vView v
> LEFT OUTER JOIN Tbl t ON t.f = v.f
> WHERE X and Y NOT IN (SELECT X, Y FROM Tbl)
> Thanks
Not sure I understand youtr specs. Are you saying you want to see all
rows from the view that do not have a match of all columns (key, x, and
y) in the foregn key table? I don't understand what you mean by "which
also contain columns X and Y" - I assume you mean the same values in x
and y?
Select col1, col2, col3
From vView v
Where Not Exists (
Select *
From Table1 t
On v.f = t.f
and v.x = t.x
and v.y = t.y)
David Gugick
Imceda Software
www.imceda.com

Help with query formatting

Hi all,
I have one table that contains, among other fields, a NAME and 14 SERVICE
fields. The ID number is obvious and the SERVICE fields contain a "Y" if
that particular person (NAME) carries out the service. A person can carry
out 1 or more services or the person can carry no service.
I am trying to write a search screen where the user will enter a partial
name and select 1 or more services to be included. The query will then
return and person who's name is LIKE the entry AND who has at least 1 of the
entered services.
I have the following: "SELECT * FROM PROVIDERS WHERE [first name] LIKE
'%TOM%' AND srv1 = 'Y' OR srv2 = 'Y' OR srv3='Y'.
What I am getting from the above is all people with TOM in their name AND
service 1. I am also getting people - no matter what their name is - with
either service 2 or service 3.
I need all people with TOM in their name and EITHER service 1, service 2 or
service 3.
Any help is appreciated.
George
George,
You need to add parentheses around the (this OR that OR other...).
The priority of boolean operators is that AND is evaluated before OR.
SELECT *
FROM PROVIDERS
WHERE [first name] LIKE '%TOM%'
AND (
srv1 = 'Y' OR srv2 = 'Y' OR srv3='Y'
)
(Ultimately, you will probably have more luck with any sizeable
application if you do not use 14 different Y/N columns. Almost
always, it is a better design to maintain a separate table to record
that information. My suggestion should get you moving forward,
but there is always the risk that it moves you forward closer to the
quicksand.)
Steve Kass
Drew University
George wrote:

>Hi all,
>I have one table that contains, among other fields, a NAME and 14 SERVICE
>fields. The ID number is obvious and the SERVICE fields contain a "Y" if
>that particular person (NAME) carries out the service. A person can carry
>out 1 or more services or the person can carry no service.
>I am trying to write a search screen where the user will enter a partial
>name and select 1 or more services to be included. The query will then
>return and person who's name is LIKE the entry AND who has at least 1 of the
>entered services.
>I have the following: "SELECT * FROM PROVIDERS WHERE [first name] LIKE
>'%TOM%' AND srv1 = 'Y' OR srv2 = 'Y' OR srv3='Y'.
>What I am getting from the above is all people with TOM in their name AND
>service 1. I am also getting people - no matter what their name is - with
>either service 2 or service 3.
>I need all people with TOM in their name and EITHER service 1, service 2 or
>service 3.
>Any help is appreciated.
>George
>