Solved

Sybase query returning only one row

Posted on 2008-06-23
9
737 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
  • 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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 500 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dynamically Growing Sybase Database 3 444
InterSystems Caché OPEN QUERY 4 483
Cluster Resource error 3 61
Default Read Only User Sybase DB 1 127
For months I had no idea how to 'discover' the IP address of the other end of a link (without asking someone who knows), and it drove me batty. Think about it. You can't use Cisco Discovery Protocol (CDP) because it's not implemented on the ASAs.…
Knowing where your website is hosted is as important as the features you receive, the monthly fee, and the support you receive. Due diligence should be done when choosing your next hosting provider.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

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