Se connecter
 
AccueilLibrairieFormationTéléchargementsSupport techniqueCommunautésForums
Pour les professionnels de l’informatique >
> 
> 
SQL Script Error

Traitée SQL Script Error

  • mardi 26 juin 2012 05:39
     
      A du code
    Connectez-vous pour voter
    0

    Hi, i have a syntax Error in this, please advise 

    SELECT  #temp.*,

    SELECT  DISTINCT Inv_ID,
                (
                     SELECT    '  ' + Inv_ID + ' (' + Inv_Type + ') '
                     FROM OTH_INV_DETAILS a
                     WHERE    a.Inv_ID = b.Inv_ID
                     FOR XML PATH('')
                 )Inv_Type_Description,
                 SUM(Inv_Qty) OVER(PARTITION BY Inv_ID) AS TotalQty
    INTO temp
    FROM  OTH_INV_QTY_LOC b;
    SELECT temp.*,
            CASE WHEN a.INV_USAGE = 0 THEN 0            
            ELSE cast ((t.TotalQTY) / a.INV_USAGE as int)     
            END AS DSB
     FROM   OTH_INV_DETAILS a
     JOIN temp t ON a.INV_ID = t.INV_ID
           where 1=1  or t.TotalQty <= A.INV_ROL

     

Toutes les réponses

  • mardi 26 juin 2012 06:01
     
     
    Connectez-vous pour voter
    0
    temp.* wont work correct that.. Is that temporary table?

    Tamilselvan

     
  • mardi 26 juin 2012 06:39
     
     
    Connectez-vous pour voter
    0

    HI Tamil,

    Yes, Any advise ?

     
  • mardi 26 juin 2012 06:47
     
     Traitée A du code
    Connectez-vous pour voter
    2

    Try something like the following...

    SELECT  DISTINCT Inv_ID,
             (
                  SELECT    '  ' + Inv_ID + ' (' + Inv_Type + ') '
                  FROM OTH_INV_DETAILS a
                  WHERE    a.Inv_ID = b.Inv_ID
                  FOR XML PATH('')
              ) As Inv_Type_Description,
              SUM(Inv_Qty) OVER(PARTITION BY Inv_ID) AS TotalQty
INTO #temp
FROM OTH_INV_QTY_LOC b;

SELECT t.*,
       CASE WHEN a.INV_USAGE = 0 THEN 0            
       ELSE cast ((t.TotalQTY) / a.INV_USAGE as int)     
       END AS DSB
FROM   OTH_INV_DETAILS a 
JOIN #temp t ON a.INV_ID = t.INV_ID
where 1=1  or t.TotalQty <= A.INV_ROL

-- Clean-up temp table
drop table #temp

    Thanks

    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem


    • Modifié moort mardi 26 juin 2012 06:48
    • Marqué comme réponse Caulson mardi 26 juin 2012 06:49
    •  
     
  • mardi 26 juin 2012 06:47
     
     Traitée A du code
    Connectez-vous pour voter
    1

    Hi,

    Change the temp.* to t.*. As you have given an alias t to temp temp.* will not work

    SELECT  DISTINCT Inv_ID,
            (
                 SELECT    '  ' + Inv_ID + ' (' + Inv_Type + ') '
                 FROM OTH_INV_DETAILS a
                 WHERE    a.Inv_ID = b.Inv_ID
                 FOR XML PATH('')
             )Inv_Type_Description,
             SUM(Inv_Qty) OVER(PARTITION BY Inv_ID) AS TotalQty
INTO temp
FROM  OTH_INV_QTY_LOC b;
SELECT t.*,
        CASE WHEN a.INV_USAGE = 0 THEN 0            
        ELSE cast ((t.TotalQTY) / a.INV_USAGE as int)     
        END AS DSB
 FROM   OTH_INV_DETAILS a 
 JOIN temp t ON a.INV_ID = t.INV_ID
       where 1=1  or t.TotalQty <= A.INV_ROL
    Regards
    satheesh
    • Marqué comme réponse Caulson mardi 26 juin 2012 06:52
    •  
     
  • mardi 26 juin 2012 06:50
     
     
    Connectez-vous pour voter
    0
    it's work. thank you moort