The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Help with query builder
Posted: 21 Jan 09 9:30 AM
|
Good morning! I am in need of some assistance. I am trying to develop a group in Saleslogix and have only managed to get it to work partially. What I have is an integer field called WPS in a custom file called WPS. What I need to do is create a group in a certain range and have only the WPS records that have the number 12 in the last two positions select. My query looks like this:
select accountid
from account
where accountid in(select accountid
from sysdba.wps
where wps between '7000000' and '7900000' and right(rtrim(cast(wps as char)), 2) = '12')
What returns is the whole WPS list. Can someone tell me what it doesn't like about my statement? is it the right and rtrim and cast?
Thanks for all your help,
Renee |
|
|
|
Re: Help with query builder
Posted: 13 Feb 09 5:22 PM
|
Try replacing "wps between '7000000' and '7900000' with
wps > 7000000 and wps < 7900000
Also you don't want quotes if it's an integer field. |
|
|
|
Re: Help with query builder
Posted: 17 Feb 09 7:35 AM
|
Thanks very much for your help. I tried what you suggested but I am still not getting the correct result. My resulting group contains the entire list of WPS records. Even the ones that are just 5 digits such as 73439. What I am trying to accomplish is a list of wps's that range between the 7000000 and 7900000 and the last two numbers end with 12.
Any other ideas will be greatly appreciated!
Thanks,
Renee |
|
|
|
Re: Help with query builder
Posted: 17 Feb 09 10:52 PM
|
Try removing a layer of complexity by first making the query work in Management Studio - much faster than pratting around with the Query Builder.
Once you have it working there, it should be easier to build your QB group. Remember that you can 'View SQL' in QB to check that you are getting close to what worked in SSMS.
Phil |
|
|
|
Re: Help with query builder
Posted: 17 Feb 09 11:00 PM
|
I just read this again. If it's an integer field, you do not need quotes around the numbers in the WHERE clause.
Getting the 'ends with 12' bit is more of a challenge. If the number is x, you need something like
x/100 - int(x/100) = 0.12
in your WHERE clause - will take some trial and error to get the syntax right. Post again if you need more help with this.
Phil |
|
|
|
Re: Help with query builder
Posted: 18 Feb 09 8:21 AM
|
| I am trying to just get the WPS select to work in Management Studio and I have removed the part about the '12'. For some reason, when I try just selecting the wps's, I am getting all of the records. I did remove the single quotes from around the numbers because they are set up as integers. For example, it is returning 84109 and 61526. Any ideas???? |
|
|
|
Re: Help with query builder
Posted: 18 Feb 09 9:55 AM
|
So this query
select accountid, WPS
from wps
where wps between 7000000 and 7900000
Returns a list of all AccountIDs and WPSs on the WPS table, regardless of the value of the WPS field?
Phil |
|
|
|