?
Solved

Sybase query returning only one row

Posted on 2008-06-23
9
Medium Priority
?
756 Views
Last Modified: 2012-08-13
Now when I am running the below query ,it is giving me only one row.
where as if I individually do the query, all the rows are existing in the table.
It should return me all the rows.

Can any one tell me in sybase, which "combination of special characters" is making the result display only one row??
X indicates the numeric character.
 
select * from <CCtable> where id in (
".",
"idie",
"had",
"sx",
"x",
"sw mr.d wanted to ad",
"customer",
"cc",
"Cust was",
"q",
"l",
"cc asked if built in",
"w",
"S",
"c",
"cust wanted to knwo",
"-",
" h",
"XXXXXXXXX-X",
"XXXXXXXXXXXXXXXX Ric",
"]",
"`",
"XXXXXXXXXXXXXXXXx",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXX-XXXX",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX",
"XXXXXXXXXXXXXXXXf",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXXx",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXXq",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXX+XXXXXXXX",
"XXXXXXXXXXXXXX*XX",
"XXXXXXXXXXXXXXXXx",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXX`",
"XXXXXX-XXXXXXXXXX",
"XXXXXXXXXXXXXXXXHEADE",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXOXXXX",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX,",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX`",
"XXXXXX+XXXXXXXXXX",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX,",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX``",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXX/XXXXXX",
"XXXXXXXXXXXXXXXX,",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXXB",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXXq",
"XXXXXXXXXXXXXXXXb",
"XXXXXXXX XXXXXXX",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXXx",
"XXXXXXXX+XXXXXXXX",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX]",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXX",
"XXXXXXX+XXXXXXXXX",
"XXXXXXXXXXXXXX-XX",
"XXXXXXXXX/XXXXXXX",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX",
"XXXXXXXXXXXXXXXX]",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXX/XXXX",
"XXXXXXX/XXXXXXXXX",
"XXXXXXXXXXXXXXXXx",
"XXXXXXXXXXXXXXXX+",
"XXXXXXX-XXXXXXXXX",
"XXXXXXXXXXXXXVS",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXX+XXXXXXXX",
"XXXXXXXXXXXX-XXXX",
"XXXXXXXXXXXXXXXXb",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXXXXXXXXX`",
"XXXXXXXXXXXXXXXX+",
"XXXXXXXXX-XXXXXXX"
)

Open in new window

0
Comment
Question by:rbhargaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 13

Expert Comment

by:alpmoon
ID: 21850349
I don't see anything wrong in your query. It should be something in your query tool or you may have 'set rowcount 1' setting. You can try 'set rowcount 0' to see if it is due to rowcount setting.

Otherwise you can try another tool to understand whether it is because of the tool you are using.
0
 

Author Comment

by:rbhargaw
ID: 21850936
Alpmoon,

row count settings look right..I am using DBArtisan as an query tool..In this query, say you comment out "XXXXXX+XX" or "XXXXXXX'XX" it works "sometimes".But I am unable to find any pattern as to why it will return only one row in some cases and full rows in another..I need to delete 500 rows but I am doing in chunks say 50 at a time..but unable to see anything unusual in the query. I thought may be an even/odd combination of "+" or " `" but it does not help.
0
 
LVL 19

Expert Comment

by:grant300
ID: 21850979
First, you should be using single quotes not double quotes.  Double quotes are generally reserved for identifiers that happen to be reserved words.  That facility is called quoted identifiers.  Change all your double quotes to single quotes.  In the mean time, try setting the session level option for quoted identifiers to OFF.

If you have to deal with apostrophes in your strings, you can escape them by putting two single quotes in a row   'Mike''s Hard Lemonade' is an example.

Second, I hope you are not trying to use X as a wildcard or pattern.  You will get exact matches only.

Regards,
Bill
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 19

Expert Comment

by:grant300
ID: 21851008
BTW, those are very weird values to be IDs.  One and two (alpha) character IDs mixed in with long numeric sequences.  Can you share with us what the table looks like and what kind of data is actually stored in it?

Thanks,
Bill
0
 

Author Comment

by:rbhargaw
ID: 21851038
This is an Credit card table and we want to remove the cards which have any non numeric characters added to them. Somehow in "older days" these unneccesary values got added and while auditing we came across these junk credit card data.

Roop
0
 
LVL 19

Expert Comment

by:grant300
ID: 21851834
Somebody in the old days did not put any domain constraints in at all.  Sounds like you had to put your hip waders on for this job 8-)

You know, it might be easier to exclude the values that are formatted correctly rather than try and find all the ones that are not, particularly if the system still does not keep the garbage out completely.

The "right" way to store these numbers in as a NUMERIC(16).  That way no alphas or punctuation can get in at all.  It also makes for a much better (fewer bytes, more compact) index.   A domain constraint on the column could have been used as well even if the application was wide open.

Well, to answer your question...  You have two or three choices:
 - WHERE ID LIKE '%[^0-9]%'
 - WHERE patindex("%[^1234567890]%", ID) = 0
 - In v15+, you can use the ISNUMERIC function...   WHERE ISNUMERIC(ID) = 0

BTW, I don't see any need to limit the delete to 50 rows at a time if you are only deleting 500 total.  Unless you are worried about locks on a very busy system and/or you do not have nearly enough log space, you should have no trouble deleting 500 rows, even with the cascading deletes of child tables.  Shouldn't be much in those anyway since there should/can not be any transactions against the bogus card IDs.

Assuming that you have an index on the ID field of the table and that you need to minimize the transaction time because the activity against the table is heavy, I would do this in two steps.  Step one is to build a temp table using one of the WHERE clause options above.  This won't lock the table and represents the bulk of the time since you will be scanning the entire ID field index.  Step two is to delete the target rows by joining the temp table to the target table.  Something like...

SELECT ID
   INTO #BadCCs
  FROM CCtable
WHERE ID LIKE '%[^0-9]%'

DELETE CCtable
   FROM #BadCCs BC
   JOIN CCtable CC
      ON CC.ID = BC.ID



Regards,
Bill
0
 

Author Comment

by:rbhargaw
ID: 21852086
Thanks Bill..I am feeling stupid that it came out to be such a small query!
Instead I had typed all special characters in the keyboard and searched in the table ..gosh..

SELECT ID   INTO #BadCCs   FROM CCtable
WHERE ID LIKE '%[^0-9]%' looks good enough to solve the problem

But I have a doubt, I ran the above sql and I am getting few cards which looks like actual card (which should not be coming as the query is "[^0-9]%")when I see in editor but when I copy them in textpad/notepad they come up like this : 41111111111111\x09..Not sure what "\x" means and how I don't see the row as "\x" in sybase editor.

Also I found the one solution to the "original" problem.Added Rtrim(id) and the query like below and it picked up all the rows.
select * from <CCtable> where rtrim(id) in {values}


Thanks
Roop

0
 
LVL 19

Accepted Solution

by:
grant300 earned 2000 total points
ID: 21852263
The \x syntax means "Hex" so you are getting a Hex 09 character stuck on the end.

You may be getting this data from a mainframe at some point as \x09 is an EBCDIC "Tab" character.

In any event, you have to determine if having a trailing \x09 makes those valid IDs or not.  One way to tell would be to do a count of how many IDs have trailing \x09s.  You could also look for a few of them and see if they have valid transactions against them.

If they are not valid, you already have the query to delete them.

If the ARE valid, you have a much bigger problem on your hands.  Since the ID is undoubtedly a key and probably links one or more child tables, you cannot just change the value to strip the \x09.  You can exclude them from the delete by adding a SARG to the WHERE clause like...

AND ascii(right(ID,1)) != 9
  or another syntax...
AND right(ID,1) != char(9)

Regards,
Bill
0
 

Author Comment

by:rbhargaw
ID: 21852316
Thanks a lot Bill..You are awesome!!
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The well known Cerber ransomware continues to spread this summer through spear phishing email campaigns targeting enterprises. Learn how it easily bypasses traditional defenses - and what you can do to protect your data.
Hey fellow admins! This time, I have a little fairy tale for you. As many tales do, it starts boring and then gets pretty gory. I hope you like it. TL;DR: It is about an important security matter, you should read it if you run or administer Windows …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question