Post reply

Syntex Error Help needed, real bad

  • September 15, 2005 at 11:58 pm

    #89051

    OK I have following SQL which I need a help with. I can however solve the problem using CURSOR in a long run but since I have 5000000 records I would like not to use that if possible.

    SELECT TOP 1 TD.*, P.*,

      (SELECT UID FROM dbo.GetSpecifiedSlumpInfo(P.RegionID, TD.SpecifiedSlump)) AS UID1,

      (SELECT UID FROM dbo.GetSpecifiedSlumpInfo(P.RegionID, TD.SpecifiedSecondSlump)) AS UID2

    FROM tblTruckDocket TD

    LEFT JOIN tblPlant P ON TD.PlantID = P.PlantID

    I am getting following errors

    Server: Msg 170, Level 15, State 1, Line 3

    Line 3: Incorrect syntax near '.'.

    Server: Msg 170, Level 15, State 1, Line 5

    Line 5: Incorrect syntax near '.'.

    Please help..!

    Kindest Regards,

    WRACK
    CodeLake

  • September 16, 2005 at 1:21 am

    #590442

    Can you tell what your function dbo.GetSpecifiedSlumpInfo does ?  does it return just the UID or something else ?

     

    Regards,

    Meghana

  • September 16, 2005 at 3:58 am

    #590476

    it's a table function and it returns:

    UID

    Description

    UpperLimit

    LowerLimit

    UpperTolerance

    LowerTolerance

    Label

    ToleranceMarker

    Kindest Regards,

    WRACK
    CodeLake

  • September 16, 2005 at 4:58 am

    #590492

    What values do you get when you run,

    SELECT TOP 1 P.RegionID, TD.SpecifiedSlump, TD.SpecifiedSecondSlump
FROM tblTruckDocket TD
LEFT JOIN tblPlant P ON TD.PlantID = P.PlantID

    Then what results do you get if you run the function with those specific parameters?

    SELECT UID FROM dbo.GetSpecifiedSlumpInfo(<insert regionID value>, <insert SpecifiedSlump value&gt
    SELECT UID FROM dbo.GetSpecifiedSlumpInfo(<insert regionID value>, <insert SpecifiedSecondSlumpvalue&gt

    Also, instead of posting just the return from the function, posting the entire function could help

     

    --------------------
    Colt 45 - the original point and click interface

  • September 16, 2005 at 5:33 am

    #590504

    OK my bad. I have the function code at office but I can answer other questions.

    What values do you get when you run,

    SELECT TOP 1 P.RegionID, TD.SpecifiedSlump, TD.SpecifiedSecondSlump FROM tblTruckDocket TDLEFT JOIN tblPlant P ON TD.PlantID = P.PlantID

    RegionID SpecifiedSlump SpecifiedSecondSlump

    NSW       80                     75

    Then what results do you get if you run the function with those specific parameters?

    SELECT UID FROM dbo.GetSpecifiedSlumpInfo(,

    SELECT UID FROM dbo.GetSpecifiedSlumpInfo(,

    I get UID 2 for the first one and 3 for the second one.

    Kindest Regards,

    WRACK
    CodeLake

  • September 16, 2005 at 7:00 am

    #590543

    These are subselects:

    (SELECT UID FROM dbo.GetSpecifiedSlumpInfo(P.RegionID, TD.SpecifiedSlump)) AS UID1,

      (SELECT UID FROM dbo.GetSpecifiedSlumpInfo(P.RegionID, TD.SpecifiedSecondSlump)) AS UID2

    and as subselects, the alias' don't exist. Spell out the tablenames in subselects.

    -SQLBill

  • September 16, 2005 at 9:57 pm

    #590784

    OK I will try that out and see what happens.

    Kindest Regards,

    WRACK
    CodeLake

  • September 19, 2005 at 4:07 am

    #590868

    It would still be nice to see the code of the function.

    Depending on what functions does, they may force row-by-row operations, in effect creating an 'implicit cursor' on you, even if you don't code a cursor yourself. If you find yourself in that situation, you may have to (in a case like this with a very large amount of rows) take out the function calls and do it inline instead (if that is possible, of course)

    /Kenneth

  • September 19, 2005 at 10:30 am

    #591014

    Several things

    1.

    SELECT TOP 1 TD.*, P.*,

      (SELECT UID FROM dbo.GetSpecifiedSlumpInfo(P.RegionID, TD.SpecifiedSlump)) AS UID1,

      (SELECT UID FROM dbo.GetSpecifiedSlumpInfo(P.RegionID, TD.SpecifiedSecondSlump)) AS UID2

    FROM tblTruckDocket TD

    LEFT JOIN tblPlant P ON TD.PlantID = P.PlantID

    The Highlighted part is incorrect in the sense that you can get more than one PlantID. (TD.PlantId and P.PlantID). You should differentiate them

     It is also a bad Practice to use * simply put you should specify column names!

    2. Does your function can take NULL values as Paramenters? 

    Just Run this:

    SELECT dbo.GetSpecifiedSlumpInfo(NULL,NULL)  What Happens?

    3. It will be alot better if we could see the Function Code!

     


    * Noel

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply