Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2014
    Posts
    8

    Unanswered: multiple variable assignment

    Hi - when I run the statement below in a function I get an error basically stating I get 2 column returned by the query

    useremailaddress = i.emailaddress,
    userusername = ws.username
    FROM
    public.tblinterimuser AS i INNER JOIN public.tblwebsiteuser AS ws ON
    i.websiteuserid = ws.websiteuserid
    WHERE
    i.websiteuserid = 12;

    Question is how do I assign multiple variable values from one statement?

    (This will help me get to the bottom of my transaction error which is heading towards user error!!)

    Thanks for any and all help

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As you posted part of a statement and a description of a message, the best thing you could do to get an accurate/usable answer would be to post the actual statement and message. If I had to guess, then I'd say that there were multiple rows returned and that triggered an error message that has confused you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2014
    Posts
    8

    Lightbulb

    Well that was the whole statement and that was the error

    This works (no select or perform just this)
    Variable = column from table

    This doesn't
    Variable = column, variable2 = column2 from table
    gives the error (2 columns returned by the query)

    so assigning 1 variable like that worked but assigning 2 doesn't. Turns out I should have used a SELECT INTO statement as follows

    SELECT INTO
    variable 1, variable 2
    column1, column2
    FROM
    table;

    so my final query should have been

    SELECT INTO
    userusername, userfirstname, usersurname, useremailaddress
    ws.username, p.firstname, p.surname, e.emailaddress
    FROM
    public.tblprofile AS p INNER JOIN public.tblemailaddress AS e ON
    p.profileid = e.profileid AND
    e.primaryemail = TRUE
    INNER JOIN public.tblwebsiteuser AS ws ON
    p.websiteuserid = ws.websiteuserid
    WHERE
    p.websiteuserid = p_websiteuserid;

    All working now.

    Thanks
    Pat

Posting Permissions

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