Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Red face Unanswered: Calling Functions

    Dear Everybody,

    Hi again!

    Supposing I have an SQL Server Function named Fxn1 which returns a VARCHAR(6) value. I want to use this returned value in another function named Fxn2 but Fxn2 accepts a parameter of type INT. Fxn2 itself returns an INT value. This has to be achieved in a single line.

    The following line of code will not work in SQL Server. How should I achieve this?

    declare @Res INT
    EXEC @Res = Fxn2 (CONVERT( INT, Fxn1 ))

    Thanx in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just curioius, but have you tried talking to the TA or your instructor? Its really important that you talk to them when you are stumped on homework, they need the feedback as much as you need the help finding the solution.

    -PatP

  3. #3
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    No Thx!

    No thx for the suggestion.

    I always thought that this forum had people who cud come up with suggestions which were technical in nature!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It does, but we still won't do your homework!

    -PatP

  5. #5
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Thumbs down

    First of all stop calling this homework. You cannot pass judgements like that. To me it was a genuine problem that's why I referred it to this forum. Might be not for u.

    If u r not interested, gr8. But keep ur thoughts to urself.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "This has to be achieved in a single line."???

    Are you running out of lines on your computer?

    You may just need to create a single function that combines the logic of the two you have.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't have any problem giving legitimate help (read a few thousand of my posts if you'd like to confirm that), but based on the attitude that you've displayed and the grammer and spelling that you've used (or more accurately failed to use), you appear to me to be a rather rude kid that is sqawking because they got caught trying to get someone to do their homework. If I've misinterpreted something, I'm sorry, but that is how you appear to me at the moment.

    Based on the way you presented the question in your original posting, it reads like homework. Are you saying that it isn't homework? If not, can you give us a little background in what you are trying to do, and more importantly why it needs to be accomplished in a single line?

    -PatP

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Are you running out of lines on your computer?
    I just hate it when that happens, don't you?

    -PatP

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What error are you getting when you execute the statement? Obviously
    EXEC @Res = Fxn2 (CONVERT( INT, Fxn1 ))
    ...will error out if Fxn1 returns a string that cannot be converted to an INT.
    Also, post the actual statement you are sending.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Exclamation Error Message I am getting...

    Dear blindman (although I hate to use that term to address u),

    First of all thx for ur concern & help. Can I show the audacity to ask your real name?

    Second of all, lets move to my actual problem..

    I have several procedures and functions in Oracle which I have to convert to SQL Server syntax. The Fxn1 and Fxn2 names that I had used was just to present the problem to the forum in a simple manner.
    In one of the functions named CREATE_TRIPS_SCHED, a SELECT statement has been included (the result of which has been used in another query) which is as follows :

    Code:
    SELECT  'P'||T.T_ID, T.S_TIME, T.END_TIME, 
                NVL(T.DESIRED_TIME, MIN_TO_AMPM( TO_MINUTES(TO_NUMBER(AMPM2FULLHRS(T.EST_TIME))))), 
                T.DISP, DECODE(T.WHEEL_CHAIR,'Y',5,2)
    FROM DISPATCH_TRIPS T
    WHERE T.R_NUM LIKE 'X%'
    In the above statement, MIN_TO_AMPM, TO_MINUTES and AMPM2FULLHRS are all UDFs, out of which MIN_TO_AMPM and TO_MINUTES fxns return INTEGER values and AMPM2FULLHRS returns a VARCHAR value.

    The conversion for SQL Server that I had done is as under :

    Code:
    SELECT 'P' + T.T_ID, T.S_TIME, T.END_TIME, 
              (CASE T.DESIRED_TIME WHEN ISNULL THEN MIN_TO_AMPM( TO_MINUTES(CONVERT (INT, AMPM2FULLHRS (T.EST_TIME)))) ELSE T.DESIRED_TIME END), 
                T.DISP, (CASE T.WHEEL_CHAIR WHEN 'Y' THEN 5 ELSE 2 END) 
    FROM DISPATCH_TRIPS T 
    WHERE T.R_NUM  LIKE 'X%'
    And the error msg that I am getting is this :

    Server: Msg 195, Level 15, State 10, Procedure CREATE_TRIPS_SCHED, Line 25
    'AMPM2FULLHRS' is not a recognized function name.
    Server: Msg 156, Level 15, State 1, Procedure CREATE_TRIPS_SCHED, Line 56
    Incorrect syntax near the keyword 'END'.
    Server: Msg 156, Level 15, State 1, Procedure CREATE_TRIPS_SCHED, Line 68
    Incorrect syntax near the keyword 'END'.


    I have to do this conversion without introducing other fxns of mine. So, this is the actual problem. I hope I have been able to get across my problem!

    Thx again!

  11. #11
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Unhappy

    Pat,

    At least you had the decency to be 'sorry' even if my turning 'rude' prompted you to do so.

    Originally Posted by Pat Phelan
    It does, but we still won't do your homework!
    Having a few thousand posts does not give you the right to be judgemental. It also does not entitle you to speak on behalf of the whole forum.

    P.S. Are u always like that?

  12. #12
    Join Date
    Feb 2004
    Posts
    492
    Perhaps adding the owner of the function when calling the function helps (db.owner.function)?

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It appears that my first impression may have been wrong. Based on your last post to blindman, you do seem to have a good grasp of SQL and a practical reason for making your original request.

    I think you should have success if you use:
    Code:
    SELECT @Res = dbo.Fxn2 (CONVERT( INT, dbo.Fxn1() ))
    This ought to safely degenerate into:
    Code:
    SELECT @Res = dbo.Fxn2 (dbo.Fxn1() )
    In answer to your other question, yes, especially when this forum is barraged with kids looking for someone to do their homework for them, I tend to react with considerable skepticism to posts that are presented that way. My intent is to "red flag" those posts for other forum members, so that they will consider whether they want to spend their time working on a problem that is likely going to be turned in by a kid with no appreciation and no clue. The other forum members can use their own judgement, I'm just trying to make sure that they think before they "burn daylight" answering a post that I perceive as looking for an opportunity to plagerize.

    I've repeatedly had my work turned in by students, whose teachers have recognized my style and contacted me. That probably makes me overly sensitive to the issue. I also think that there is a real ethical problem there too, and being in a position where ethical behavior is probably more important than technical expertise, that is very important to me.

    Based on the begining of this thread, I had no reason to suspect that you were anything other than an apprentice juvenile delinquent, trolling to find someone that would do their assignment for them. Based on your post to Blindman, it appears that my first impression was wrong, so I responded differently in this post. As I've said before, I have no problem giving legitmate technical assistance, but I outright refuse to allow someone to take advantage of me (or anyone else without their approval).

    -PatP

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Knighthassan,

    Pat, myself, and others have given literally thousands of bits of advice and help to people who have posted on these boards, all of it free. If Pat made a mistake about your situation then so be it, but he had reasons to be suspicious. People asking for free advice should be less disrespectful, especially of people like Pat who is arguably the most knowledgable SQL Server expert on this forum. The fact that he took considerable time to review your complex code and propose a solution after last few posts you made shows how gracious he is. I hope his solution was helpful.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Thumbs up Thx

    blindman and Pat,

    Thx to u both my problem is resolved. I also realize that my language has been a bit too harsh at times but something there got to me. Anyways, no hard feelings and thx again.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
    Knight says:

    You can't change the past, but you can ruin a perfectly good present by worrying about the future..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •