none
Question abaut sql query

    Question

  • Good day

    I have a select command like this:

    SELECT sal.salmutzar, sal.salkamut, sal.DateTime, sal.nupak, sal.shulam, sal.salshemlakuach, mutzarim.teur, mutzarim.mishkal, mutzarim.erech_kalory, mutzarim.kategory, mutzarim.lelotosefetsucar, mutzarim.paeil, mutzarim.barcode, mutzarim.barcodelelo, mutzarim.achuz_amala FROM sal INNER JOIN mutzarim ON sal.salmutzar = mutzarim.mutzar WHERE (sal.nupak = 0) ORDER BY sal.salmutzar

    I need to get saperatly all the records-(I will explain!)

    the field sal.salkamut is real type and when its value is mor than 1 i need the record to be retrieved the same times like

    the sal.salkamut field for example: if i have...

     

     

     

    Sal.salkamut

    Sal.salmutzar

     

     

     

    2

    Smartphon AB

     

     

     

    3

    BB

     

     

     

    1

    A
















                                     I need to get....

     

     

     

    Sal.salkamut

    Sal.salmutzar

     

     

     

    2

    Smartphon AB

     

     

     

    2

    Smartphon AB

     

     

     

    3

    BB

     

     

     

    3

    BB

     

     

     

    3

    BB

     

     

     

    1

    AA

     

     

     

     

     

     

    Please help me change the SQL statment to get the result

    Yair

    nehamale@walla.com

    Sunday, August 25, 2013 11:35 AM

Answers

  • I could not see anything wrong in your structure. May be you try with ;WITH

    ;WITH 
    	t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    	,nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num 
    	FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    SELECT 
    	  sal.salmutzar
    	, sal.salkamut
    	, sal.DateTime
    	, sal.nupak
    	, sal.shulam
    	, sal.salshemlakuach
    	, mutzarim.teur
    	, mutzarim.mishkal
    	, mutzarim.erech_kalory
    	, mutzarim.kategory
    	, mutzarim.lelotosefetsucar
    	, mutzarim.paeil
    	, mutzarim.barcode
    	, mutzarim.barcodelelo
    	, mutzarim.achuz_amala 
    FROM sal 
    INNER JOIN mutzarim ON sal.salmutzar = mutzarim.mutzar 
    INNER JOIN nums ON nums.num BETWEEN 1 AND sal.salkamut
    WHERE sal.nupak = 0 
    ORDER BY sal.salmutzar;


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, August 26, 2013 6:56 AM
  • Incorrect syntax near the word 'VALUES'.

    It looks like you are running an old version of SQL Server (SQL Server 2005).  It's a good idea to specify your SQL Server version when asking questions in order to expedite an answer.  You can replace the VALUES row constructor of the t4 CTE with UNION ALL:

    t4 AS (SELECT n FROM (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0) t(n))

    As Latheesh suggested, also make sure the previous statement is terminated with a semicolon, which is need when using statement with newer keywords.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, August 26, 2013 10:40 AM

All replies

  • Hi ,

    Try like below sample code :

    CREATE  TABLE tmp(Id INT IDENTITY(1,1),salsalkamut VARCHAR(20) , salsalkamuts  INT)
    INSERT tmp SELECT 'Smartphon AB',2
    INSERT tmp SELECT 'BB',3
    INSERT tmp SELECT 'A',1
    SELECT * FROM tmp
    CREATE  TABLE finaltmp(salsalkamut VARCHAR(20) , salsalkamuts  INT)
    DECLARE @i INT = 1 ,@Gocnt INT ,@j INT = 1
    WHILE @i <= (SELECT MAX(Id) FROM tmp)
    BEGIN
    SELECT @Gocnt = salsalkamuts FROM tmp WHERE id = @i
    SET @j = 1
    WHILE @j <= @Gocnt
    BEGIN
    INSERT finaltmp SELECT salsalkamut,salsalkamuts FROM tmp WHERE Id = @i
    SET @j = @j + 1
    END
    SET @i = @i + 1
    END
    SELECT * FROM finaltmp
    --if you want to insert manually using Go 
    CREATE  TABLE testinsert(Id INT IDENTITY(1,1),salsalkamut VARCHAR(20) , salsalkamuts  INT)
    GO
    INSERT testinsert SELECT 'Smartphon AB',2
    GO 2
    SELECT * FROM testinsert
    --DROP TABLE finaltmp
    --DROP TABLE tmp
    --DROP TABLE testinsert


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Sunday, August 25, 2013 12:14 PM
  • the field sal.salkamut is real type and when its value is mor than 1 i need the record to be retrieved the same times like

    So you just want to repeat the rows according to the salkamut value?  One method is with a CTE like the example below.  This example returns up to 256 numbers but it can be extended if you have a higher count.  .  Alternatively, you could join to a materialized table of numbers.

    WITH 
    	t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    	,nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    SELECT 
    	  sal.salmutzar
    	, sal.salkamut
    	, sal.DateTime
    	, sal.nupak
    	, sal.shulam
    	, sal.salshemlakuach
    	, mutzarim.teur
    	, mutzarim.mishkal
    	, mutzarim.erech_kalory
    	, mutzarim.kategory
    	, mutzarim.lelotosefetsucar
    	, mutzarim.paeil
    	, mutzarim.barcode
    	, mutzarim.barcodelelo
    	, mutzarim.achuz_amala 
    FROM sal 
    INNER JOIN mutzarim ON sal.salmutzar = mutzarim.mutzar 
    INNER JOIN nums ON nums.num BETWEEN 1 AND sal.salkamut
    WHERE sal.nupak = 0 
    ORDER BY sal.salmutzar;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, August 25, 2013 12:38 PM
  • WITH t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n)) ,nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d) SELECT sal.salmutzar , sal.salkamut , sal.DateTime , sal.nupak , sal.shulam , sal.salshemlakuach , mutzarim.teur , mutzarim.mishkal , mutzarim.erech_kalory , mutzarim.kategory , mutzarim.lelotosefetsucar , mutzarim.paeil , mutzarim.barcode , mutzarim.barcodelelo , mutzarim.achuz_amala FROM sal INNER JOIN mutzarim ON sal.salmutzar = mutzarim.mutzar INNER JOIN nums ON nums.num BETWEEN 1 AND sal.salkamut WHERE sal.nupak = 0 ORDER BY sal.salmutzar;

    it gives me:

    Incorrect syntax near the word 'VALUES'.

    Tank you very mach for the help

    yair

    Monday, August 26, 2013 6:28 AM
  • I could not see anything wrong in your structure. May be you try with ;WITH

    ;WITH 
    	t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    	,nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num 
    	FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    SELECT 
    	  sal.salmutzar
    	, sal.salkamut
    	, sal.DateTime
    	, sal.nupak
    	, sal.shulam
    	, sal.salshemlakuach
    	, mutzarim.teur
    	, mutzarim.mishkal
    	, mutzarim.erech_kalory
    	, mutzarim.kategory
    	, mutzarim.lelotosefetsucar
    	, mutzarim.paeil
    	, mutzarim.barcode
    	, mutzarim.barcodelelo
    	, mutzarim.achuz_amala 
    FROM sal 
    INNER JOIN mutzarim ON sal.salmutzar = mutzarim.mutzar 
    INNER JOIN nums ON nums.num BETWEEN 1 AND sal.salkamut
    WHERE sal.nupak = 0 
    ORDER BY sal.salmutzar;


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, August 26, 2013 6:56 AM
  • Incorrect syntax near the word 'VALUES'.

    It looks like you are running an old version of SQL Server (SQL Server 2005).  It's a good idea to specify your SQL Server version when asking questions in order to expedite an answer.  You can replace the VALUES row constructor of the t4 CTE with UNION ALL:

    t4 AS (SELECT n FROM (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0) t(n))

    As Latheesh suggested, also make sure the previous statement is terminated with a semicolon, which is need when using statement with newer keywords.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, August 26, 2013 10:40 AM