Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 115
  • Last Modified:

can't contibute variables together like V1 = V1 + V2

I want to contribute 2 variables into 1 variable.
pls. hav a look at the boold lines.
what i'm doing wrong?

DECLARE groupid CURSOR FOR select groupid from #Product_Pool_2
OPEN groupid
FETCH NEXT FROM groupid into @groupid
WHILE @@FETCH_STATUS = 0
BEGIN
print N'Groupid'+ @groupid
            print N'Mgroesse'+ @mgroesse;
      DECLARE groessen CURSOR FOR SELECT       REPLACE(product_Prices.ProductID, product_Definitions.GroupID, '') AS ASGroesse
                  FROM product_Prices INNER JOIN  product_Definitions ON product_Prices.ProductID = product_Definitions.ProductID
                  WHERE product_Prices.ModelID = 1 AND product_Definitions.GroupID = @groupid

      OPEN groessen
      FETCH NEXT FROM groessen into @groesse
      WHILE @@FETCH_STATUS = 0
      BEGIN
            select  @mgroesse =   N'@' + CAST(@groesse AS varchar(15)) ;
            set @mtest = convert(varchar(50),@mtest,102) + N'@'+ @groesse
            print N'Mgroesse '+ @mgroesse;
            print  N'groesse '+ @groesse;
            print  N'test '+ @mtest;

            FETCH NEXT FROM groessen into @groesse
            END
      CLOSE groessen
      DEALLOCATE groessen
FETCH NEXT FROM groupid into @groupid
END

CLOSE groupid
DEALLOCATE groupid
0
ebertsys
Asked:
ebertsys
  • 4
  • 3
  • 2
  • +2
1 Solution
 
Mike EghtebasDatabase and Application DeveloperCommented:
set @mtest = convert(varchar(50),@mtest,102) + N'@'+ @groesse

You may have to convert @groesse to the same data type explicitly.
0
 
ebertsysAuthor Commented:
i change it to

            set @mtest = convert(varchar(50),@mtest,102) + N'@'+ convert(varchar(50),@groesse,102)
same result empty string
0
 
ste5anSenior DeveloperCommented:
Of what data type are product_Prices.ProductID and product_Definitions.GroupID? Seems like they are (N)VARCHAR and your trying to strip off the group ID which seems to be part of the product ID. Which would be a violation of 1NF (atomic values).

But then, why do you need to cast it in the inner loop?

Please post a consise and complete example with table DDL and sample data INSERT statements.

And last but not least: It looks like row value concatention. In this case you can use FOR XML PATH(''). Maybe this is sufficient:

SELECT	'@'	+ REPLACE(PP2.ProductID, PD.GroupID, '') 
FROM	#Product_Pool_2 PP
	INNER JOIN product_Definitions PD ON PD.GroupID = PP.groupid
	INNER JOIN product_Prices PP2 ON PP2.ProductID = PD.ProductID
WHERE	PP2.ModelID = 1 
FOR XML PATH('');

Open in new window

0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
PortletPaulCommented:
I fully agree with ste5an, please supply sample data and expected result.

nb: If you need an nvarchar outcome, then all parts should be nvarchar (i.e. don't cast to varchar, cast to nvarchar)
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Yes, the following line are mixed type data:

 set @mtest = convert(varchar(50),@mtest,102) + N'@'+ convert(varchar(50),@groesse,102)

try

 set @mtest = convert(nvarchar(50),@mtest,102) + N'@'+ convert(nvarchar(50),@groesse,102)
0
 
Alpesh PatelAssistant ConsultantCommented:
select  @mgroesse =   N'@' + CAST(@groesse AS varchar(15)) ;
set @mtest = convert(varchar(50),@mtest,102) + N'@'+ @groesse


To set variable to second one you have to execute this statements. Otherwise values are not assigned to next variable.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
The way that I see,

convert(varchar(50),@mtest,102)     <-- this being varchar implicitly would take '@' as varchr

This means, there is no need for N'@'

and the same will happen to  @groesse.

But there will be overhead for this. Why don't we explicitly have:

convert(varchar(50),@mtest,102) + '@'+ convert(varchar(50),@groesse,102)

or

convert(nvarchar(50),@mtest,102) + N'@'+ convert(nvarchar(50),@groesse,102)

Depending what data type is required at the end.

Mike
0
 
ebertsysAuthor Commented:
@eghtebas
i did both.
convert(varchar(50),@mtest,102) + '@'+ convert(varchar(50),@groesse,102)
and
convert(nvarchar(50),@mtest,102) + N'@'+ convert(nvarchar(50),@groesse,102

emptey result!
i get a 50% proper result when i do
set @mtest = '@' + @groesse
i get :
groesse = @L
groesse = @x and so on.

but when i set
set @mtest = @mtest + '@' + @groesse -  the result is emptey
0
 
ebertsysAuthor Commented:
the sizes are a part of the productid like
[productid=[Groupid]+[size] ...so that the productid in the following sample is 2591-16; 2591-17;2591-18

Groupid2591
Mgroesse @-16
Mgroesse @-17
Mgroesse @-18
Mgroesse @-19
Mgroesse @-20
Mgroesse @-21
Mgroesse @-22
Mgroesse @-23
what i need is

2591;@-16@-17@-18@-19@-20@-21@-22@-23

so that i have 1 row with the groupid and the sizes in one field
0
 
ste5anSenior DeveloperCommented:
You need to normalized your data. Then it's a simple FOR XML PATH(), e.g.

DECLARE @Groups TABLE ( GroupID INT );

INSERT  INTO @Groups
VALUES  ( 2591 );

DECLARE @Products TABLE
    (
      ProductID NVARCHAR(255)
    );

INSERT  INTO @Products
VALUES  ( '2591-16' ),
        ( '2591-17' ),
        ( '2591-18' );

WITH    Normalized
          AS ( SELECT   PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) AS GroupID ,
                        PARSENAME(REPLACE(P.ProductID, '-', '.'), 1) AS SizeID
               FROM     @Products P
               WHERE    PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) IN ( SELECT    G.GroupID
                                                                          FROM      @Groups G )
             )
    SELECT  O.GroupID ,
            ( SELECT    '@-' + I.SizeID
              FROM      Normalized I
              WHERE     I.GroupID = O.GroupID
			  FOR XML PATH('')
            ) AS Sizes
    FROM    Normalized O
    GROUP BY O.GroupID;

Open in new window


btw, I don't like your terminology. Different sizes don't make a different product, only a different product variants. A group is a more general thing like a category.
0
 
ebertsysAuthor Commented:
tx,
that what i'm looking for
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now