Solved

Sybase query returning only one row

Posted on 2008-06-23
9
726 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 19

Expert Comment

by:grant300
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:rbhargaw
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks a lot Bill..You are awesome!!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Marketing can be an uncomfortable undertaking, especially if your material is technology based. Luckily, we’ve compiled some simple and (relatively) painless tips to put an end to your trepidation and start your path to success.
In this article, I will show you HOW TO: Create your first Windows Virtual Machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, the Windows OS we will install is Windows Server 2016.
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now