August 28, 2006 at 2:35 pm
I am looking at reusing the results of a first query in a second select statement.
I have a first table "ACTIVE_FIELDS" in which I maintain the columns I want to query.
Table name ACTIVE_FIELDS
-----------------------------------------
Field - Status
----------------------------------------
CODE - ACTIVE
DESCRIPTION - ACTIVE
NAME - INACTIVE
So I get the list of active fields by doing :
SELECT FIELD FROM ACTIVE_FIELDS WHERE STATUS=ACTIVE
It gives me : Code and Description.
I have then a second table MATERIAL with 3 columns CODE, DESCRIPTION and NAME.
I am looking at doing a query against MATERIAL table as follows
SELECT (SELECT FIELD FROM ACTIVE_FIELDS WHERE STATUS=ACTIVE) from MATERIAL and I expect to only get columns CODE and DESCRIPTION in the result.
If I want to add the NAME field in the result of my query, I just have to change status of NAME field in the eh ACTIVE_FIELDS table..
Can anybody help me with the exact syntax, I must miss a function to convert the result of the first query..
August 28, 2006 at 4:58 pm
TRY THIS
DECLARE
CUR CURSOR FOR SELECT FIELD FROM ACTIVE_FIELDS WHERE STATUS='ACTIVE'
DECLARE
@sql NVARCHAR(400), @FIELD VARCHAR(50)
SELECT
@sql=''
OPEN
CUR
FETCH
NEXT FROM CUR INTO @FIELD
WHILE
@@FETCH_STATUS=0
BEGIN
PRINT @FIELD
SET @sql=CASE @sql WHEN '' THEN '' ELSE @sql+',' END+@FIELD
FETCH NEXT FROM CUR INTO @FIELD
END
SET
@sql=N'SELECT ' + @sql + ' FROM MATERIAL'
EXECUTE
SP_EXECUTESQL @sql
CLOSE
CUR
DEALLOCATE
CUR
August 28, 2006 at 10:57 pm
Many hanks,
before going further, woul t work on MS access ?
Thanks
August 29, 2006 at 6:41 am
If access is only showing the data, there's a way to make this work. However access cannot execute this code without sql server.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply