Help with SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with SQL query

Author

Topic

berr
Starting Member

9 Posts

Posted - 2007-11-14 : 16:25:43

I wasn't sure where to post this topic so I hope I chose the right spot. I have a problem doing a query for a specific field. I have a table called CustomFieldValue and there is a field in this table called Value. This field is mixed with all sorts of data which makes it hard for me to query it.

Here are two field in this table that are important. The first column is a unique ID number that I need to be able to pull the appropriate information. The second column is the Value field which is the data I want to show for my report

CustomFieldID Value
132 1900-01-01
134 2007-01-11
136 208-555-2454
140 1
142 False
142 Joe Smith
144 45
129 78

So as you can see it's a mess, it's because of this program that allows users to create custom fields and all the data gets dumped in here. In my query I state this:

SELECT *
FROM CustomFieldValue
WHERE (CustomFieldId IN (132, 134, 136, 138, 140, 142, 144, 129))

This pulls up the information no problem. The problem is I would like to be able to pull all of this information AND I want it to pull only the information related to 144.

When I create a parameter that is CustomFieldValue = {@144} it clears out all the information and only gives me the results related to ID 144. I don't think a join would fix this because all the data is in the same table. So I end up filtering what I have already filtered. I hope this makes sense because I have tried the Crystal Reports forum and everyone ends leaving me without an answer. I would merely just be fine with "This cannot be done" but I don't even get that. I did some reading and playing around with a SELECT WHERE EXISTS statement but I haven't had any luck, it just pulls up everything from the table. Is there some type of sub query I could use? Any feedback would be most appreciated.

Thanks for your time!

Brian

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 16:32:05

If you are pulling that, doesn't that INCLUDE the 144 ID information?

I am not sure what you are getting at.. if your filtering for the ID, how are not able to get "all of the information for ID=144" --especially if it is already included?

Where does the parameter fit in?

What would be the INCORRECT results of your posted query, and what would be the CORRECT (or sample) of the correct results needed?

Is there more to this table? or are you joining to another table?

What you need can be done, but you have to be alittle clearer about what the problem is, and what you actually need from the data.

Poor planning on your part does not constitute an emergency on my part.

berr
Starting Member

9 Posts

Posted - 2007-11-14 : 16:52:21

Yes, it does include the information. Here is the complete selection criteria:

{CustomFieldValue.Value} <> "1900-01-01" and
{People2File.RoleId} = 17 and
{CustomFieldValue.CustomFieldId} in [132, 134, 136, 138, 140, 142, 144, 129]
{CustomFieldValue.Value} = {?ReferralSource}

I have 7 tables that are linked together, all inner joins. I am able to get all the information I need, I just want to filter it out even more. Here is what the report displays without the paramter:

Smith, Joe
PIF Mailed 2007-09-27
PIF Received
Engagement Received
Newsletter Start Date
Referral Source John Doe

Smith, Joe
PIF Mailed 2007-10-01
PIF Received 2007-10-15
Engagement Received
Newsletter Start Date 2007-10-30
Referral Source Jane Doe

Smith, Joe
PIF Mailed 2007-08-11
PIF Received 2007-08-30
Engagement Received
Newsletter Start Date 2007-09-01
Referral Source Jane Doe

When I use the parameter, here is what happens.

Smith, Joe
Referral Source Jane Doe

I lose all my dates but I get all of my referral sources (144). I can understand why it is only showing 144 because in my parameter I say {CustomFieldValue.Value} = {?ReferralSource} which is 144. I hope I explained it better, sorry for leaving out details.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 17:08:13

Yes, you are clear as mud.

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 17:09:30

quote:

SELECT *
FROM CustomFieldValue
WHERE (CustomFieldId IN (132, 134, 136, 138, 140, 142, 144, 129))

This pulls up the information no problem. The problem is I would like to be able to pull all of this information AND I want it to pull only the information related to 144.

went to :

quote:

I have 7 tables that are linked together, all inner joins

Hope you can understand why I asked for more info...there is a big difference in the potential answer with part your last response. The way you have to think about it is, YOU have ALL the data in your head or in front of you. We have nothing. If you have it all in front of you and can't figure it out...how can we?

Anyways, you have a clusterf*&^ of data in 1 column, and you need to filter that column for the referral source parameter = 144 in that table.

Without seeing the whole structure and whole query, I would say add a subquery with an alias and INNER join to that as well but limit the subquery to the parameterized requirement:

(Select CustomfieldID,Value
FROM CustomFieldValue
Where Value = {?ReferralSource} ) as Referral144
on CustomFieldValue.CustomFieldID = Referral144.CustomFieldID

quote:

{CustomFieldValue.Value} <> "1900-01-01" and
{People2File.RoleId} = 17 and
{CustomFieldValue.CustomFieldId} in [132, 134, 136, 138, 140, 142, 144, 129]
{CustomFieldValue.Value} = {?ReferralSource}

by default, that parameter is hosed. You are saying where {CustomFieldValue.Value} <> "1900-01-01" and
{CustomFieldValue.Value} = {?ReferralSource}

BOTH would be true in all cases for any returns (it would NOT equal that 0 date but would be 144). So maybe rethinking your criteria is in order.

Perhaps an OR instead of an AND somewhere in there...

Poor planning on your part does not constitute an emergency on my part.

berr
Starting Member

9 Posts

Posted - 2007-11-14 : 17:39:27

I can see where I confused you. I clearly can't figure it out because I don't even know SQL. This is all new to me. I'm just trying to understand it.

quote:
(Select CustomfieldID,Value
FROM CustomFieldValue
Where Value = {?ReferralSource} ) as Referral144
on CustomFieldValue.CustomFieldID = Referral144.CustomFieldID

You said a subquery, but where should I place this? I tried entering it in the SQL Command function in Crystal Reports but that didn't work and I tried adding it to my current query...still didn't work. Yes, I lack the SQL skills. If you have an email I can send you some snapshots of my report and tables, I don't know if this would be any help though.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 17:47:04

Look at the other option. Fix your filter. You can't have <> '1/1/1900' AND ={?reportparameter}. Experiment with OR in the criteria as I also posted.

Poor planning on your part does not constitute an emergency on my part.

berr
Starting Member

9 Posts

Posted - 2007-11-14 : 17:53:24

Ok, well I give it a shot, thanks for trying.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 18:00:33

Since it is in crystal, it may require a more crystal specific expertise...my profile here has an email if you want to send the whole query..I can take a look.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources