Using value in a specific cell within an specific table as query criteria

Question

Post reply

Using value in a specific cell within an specific table as query criteria

kellyrmartin-805750

SSChasing Mays

Points: 645
More actions
September 20, 2008 at 9:36 am

#198624

Hi Guys. Would anyone know how I could refernce the contents of a certain cell in a certain table to use as the filter information for a query in a view I am writing? thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

Sure... what's the name of the table, the PK that identifies the row, and the column name of that certain cell?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kellyrmartin-805750 SSChasing Mays Points: 645 More actions · Answer 2

The table name is "TheValues" , the PK field is [Desc] and the value in the PK field needs to "ComPeriod". Then it is the value in the "TheData" field that I will want to use.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

SELECT sot.*

FROM dbo.SomeOtherTable sot

INNER JOIN dbo.TheValues tv

ON sot.TheData = tv.TheData

WHERE tv.[Desc] = 'ComPeriod'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kellyrmartin-805750 SSChasing Mays Points: 645 More actions · Answer 4

So do I place that right into the filter critera box in the query designer?

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

Heh... Sorry, I wouldn't know. I don't use query designer to design queries. What I gave you is the "core" of the query that you'd end up with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

Ok... here it is... had to look. Copy the query I gave you and paste it into the SQL pane of the query designer. Make sure the table names and columns are correct and it should automatically draw the diagram in the Diagram pane and automatically populate the Criteria Pane.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Ummm... ok... now I understand the latest question...

The answer is that isolation of the "cell" and using it as a criteria is done as a combination of a JOIN between the tables in the Diagram Pane and a criteria in the Criteria Pane. The code I gave you is what the Query Designer would return if you did those things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kellyrmartin-805750 SSChasing Mays Points: 645 More actions · Answer 8

Thanks for the awesome help. Works great. After I saw what happend in the query designer after I dropped in your sql statement I felt a little foolish asking the question. It was only a click or two away form what I had tried. Thanks for your help again.

Kelly

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

You bet. Thanks for the feedback, Kelly. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)