need help for a 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

SQL Server 2005 Forums

Transact-SQL (2005)

need help for a query

Author

Topic

adam
Starting Member

4 Posts

Posted - 2008-02-03 : 08:59:35

Hi All,

I have two tables.
First is CAT_TABLE_LIST ;
FIELD_NAME OPPOSITE_NAME TBL_NAME
Height (mm) , COL_1 , TBL_13
Cylinder volume (cm³) , COL_5 , TBL_13
Torgue (daN.m) , COL_7 , TBL_13
Cylinder weight(kg) , COL_13 , TBL_13
Pump Type , COL_9 , TBL_13
Fuel Consumption , Col_22 , TBL_22
Color , Col_41 , TBL_09
--------------------------------------------------------------------
Second is TBL_13 (there are a few tables for each distict value for CAT_TABLE_LIST.TBL_NAME column );
STOCK_CODE COL_1 COL_5 COL_7 COL_13 COL_9
FC-25 , 12,5 , 5 , 15.000 , 45 , DP 75
FT-45 , 27 , 9 , 40.000 , 35 , DP 101
--------------------------------------------------------------------

the CAT_TABLE_LIST.OPPOSITE_NAME column holds columns names of tables(for
TBL_NAME).

How can i get a result with specific TBL_NAME and STOCK_CODE ?

expected result is (for TBL_NAME = 'TBL_13' AND STOCK_CODE ='FC-25' ) .....

STOCK_CODE OPPOSITE_NAME FIELD_NAME VALUE
FC-25 , COL_1 , Height (mm) , 12,5
FC-25 , COL_5 , Cylinder volume (cm³), 5
FC-25 , COL_7 , Torgue (daN.m) , 15.000
FC-25 , COL_13 , Cylinder weight(kg) , 45
FC-25 , COL_9 , Pump Type , DP 75
----------------------------------------------------------

Thanks in advance...

PS: i use ,(comma) for separator in data rows..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 10:22:45

You need to use dynamic sql for achieveing as your your second tables info itself is coming from a column of first table.

CREATE FUNCTION GetValues
( @StockCode varchar(5) )
RETURNS  @Results TABLE
(
STOCK_CODE varchar(5),
OPPOSITE_NAME varchar(5),
FIELD_NAME varchar(15),
VALUE int
)
AS

BEGIN
DECLARE @PK int,@JoinTable varchar(10),@ReqdCol varchar(10),@Sql varchar(8000)


SELECT @PK = MIN(PKCol)
FROM CAT_TABLE_LIST 

WHILE @PK IS NOT NULL
BEGIN
SELECT @JoinTable =TBL_NAME,
       @ReqdCol=OPPOSITE_NAME 
FROM CAT_TABLE_LIST 
WHERE PKCol=@PK

SELECT @Sql='SELECT j.STOCK_CODE,c.OPPOSITE_NAME,c.FIELD_NAME,j.' + @ReqdCol + ' FROM  CAT_TABLE_LIST c CROSS JOIN ' + @JoinTable +' j WHERE c.OPPOSITE_NAME =' + @ReqdCol + ' AND c.TBL_NAME=' + @JoinTable + ' AND j.STOCK_CODE=' + @StockCode 

INSERT INTO @Results
EXEC (@Sql)

SELECT @PK = MIN(PKCol)
FROM CAT_TABLE_LIST 
WHERE PKCol > @PK
END
END
GO

invoke the function like this
SELECT * FROM GetValues('FC-25')

also note that PKCol is the primary key column of CAT_TABLE_LIST table

adam
Starting Member

4 Posts

Posted - 2008-02-03 : 10:59:47

hi visakh16,

I am getting these error.

Msg 443, Level 16, State 14, Procedure GetValues, Line 28
Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function.
Msg 455, Level 16, State 2, Procedure GetValues, Line 31
The last statement included within a function must be a return statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 03:23:11

make it a procedure

CREATE TABLE CAT_TABLE_LIST 
(
FIELD_NAME varchar(50),
OPPOSITE_NAME varchar(20),
TBL_NAME varchar(20)
)
INSERT INTO CAT_TABLE_LIST  VALUES ('Height (mm)' , 'COL_1' , 'TBL_13')
INSERT INTO CAT_TABLE_LIST  VALUES ('Cylinder volume (cm³)' , 'COL_5' , 'TBL_13')
INSERT INTO CAT_TABLE_LIST  VALUES ('Torgue (daN.m)' , 'COL_7' , 'TBL_13')
INSERT INTO CAT_TABLE_LIST  VALUES ('Cylinder weight(kg)' , 'COL_13' , 'TBL_13')
INSERT INTO CAT_TABLE_LIST  VALUES ('Pump Type' , 'COL_9' , 'TBL_13')
INSERT INTO CAT_TABLE_LIST  VALUES ('Fuel Consumption' , 'Col_22' , 'TBL_22')
INSERT INTO CAT_TABLE_LIST  VALUES ('Color' , 'Col_41' , 'TBL_09')



select * from TBL_13
CREATE TABLE TBL_13
(
STOCK_CODE varchar(10),
COL_1 decimal(10,1),
COL_5 int,
COL_7 decimal(10,3),
COL_13 int,
COL_9 varchar(10)
)
INSERT INTO TBL_13 VALUES ('FC-25' , 12.5 , 5 , 15.000 , 45 , 'DP 75')
INSERT INTO TBL_13 VALUES ('FT-45' , 27 , 9 , 40.000 , 35 , 'DP 101')

CREATE TABLE TBL_09
(
STOCK_CODE varchar(10),
COL_41 decimal(10,1),
COL_5 int,
COL_7 decimal(10,3),
COL_13 int,
COL_9 varchar(10)
)
INSERT INTO TBL_09 VALUES ('KC-27' , 10.23 , 5 , 15.000 , 33 , 'KR 168')


CREATE TABLE TBL_22
(
STOCK_CODE varchar(10),
COL_11 decimal(10,1),
COL_15 int,
COL_22 decimal(10,3),
COL_23 int,
COL_19 varchar(10)
)
INSERT INTO TBL_22 VALUES ('KM-29' , 12.5 , 5 , 15.000 , 45 , 'DP 75')
INSERT INTO TBL_22 VALUES ('FC-25' , 123 , 2 , 40.250 , 35 , 'KK 324')

DROP table Results
CREATE TABLE Results --table to get results
(
STOCK_CODE varchar(10),
OPPOSITE_NAME varchar(20),
FIELD_NAME varchar(50),
[VALUE] varchar(50)
)
CREATE PROCEDURE GetValues
 @StockCode varchar(5)

AS

BEGIN
DECLARE @PK varchar(50),@JoinTable varchar(10),@ReqdCol varchar(10),@Sql varchar(8000)


SELECT @PK=MAX(FIELD_NAME)
FROM CAT_TABLE_LIST 


WHILE @PK IS NOT NULL
BEGIN
SELECT @JoinTable =TBL_NAME,
       @ReqdCol=OPPOSITE_NAME 
FROM CAT_TABLE_LIST 
WHERE FIELD_NAME=@PK
 
SET @Sql='INSERT INTO Results
SELECT j.STOCK_CODE,c.OPPOSITE_NAME,c.FIELD_NAME ,j.' + @ReqdCol + ' FROM  CAT_TABLE_LIST
 c CROSS JOIN ' + @JoinTable +' j WHERE c.OPPOSITE_NAME =''' + @ReqdCol + ''' AND c.TBL_NAME=''' + @JoinTable + ''' AND j.STOCK_CODE=''' + @StockCode +'''' 


EXEC (@Sql)

SELECT @PK = MAX(FIELD_NAME)
FROM CAT_TABLE_LIST 
WHERE FIELD_NAME < @PK
END
RETURN
END
GO

GetValues 'FC-25'
select * from Results

Output
-----------------
STOCK_CODE	OPPOSITE_NAME	FIELD_NAME	VALUE
FC-25	COL_7	Torgue (daN.m)	15.000
FC-25	COL_9	Pump Type	DP 75
FC-25	COL_1	Height (mm)	12.5
FC-25	Col_22	Fuel Consumption	40.250
FC-25	COL_13	Cylinder weight(kg)	45
FC-25	COL_5	Cylinder volume (cm³)	5

adam
Starting Member

4 Posts

Posted - 2008-02-04 : 08:31:14

Hi visakh16,

Thanks for your sample code.

is it possible to format values of j.' + @ReqdCol + ' column. When inserting to Results table ?

I mean if it is decimal, discarding unnecessary zero after the decimal separator will be great. And exactly the same for DateTime values.

if j.' + @ReqdCol + ' colums value decimal lie;
23.2000 --> 23.2
23.0100 --> 23.01
23.000 -->23
and for datetime, small date time will be enough like '27/01/2009'

What do u think ?

thanks again ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 11:27:58

try using ROUND() function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources