evening and morning

hello everybody

I am using mssql server

I have a stored procedure and I am doing some modifications

The Stored Procedure is huge and so not posting the code. I am sharing some dummy codes.

Lets say

declare @bName VARCHAR(200),
select a.id,@bName=b.name from tableA a join tableB b

any help will be appreciated

@SpringLearner you need to assign a.id to a variable too

or remove it from the query

But that code seems quite risky, what happens when the query returns multiple rows?

@SpringLearner If the query is likely to return multiple rows and you want each b.name to be used in the other query, create a temporary table or declare a table variable and then insert the results of the first query in that temporary storage. You will be able to re-use it as many times as necessary

DECLARE @namelist TABLE (id int, name varchar(200));

INSERT INTO @namelist (id, name)
select a.id, b.name from tableA a join tableB b;

SELECT
  ... /* whatever */
FROM
  something
  INNER JOIN @namelist AS list ON ...
WHERE
  ...
;

I think I need to chill a little on the site, probably being a little too honest with some newbies. Just seems to be a big influx of zero-effort questions recently

Morning

@Philᵀᴹ Could be worse: dba.stackexchange.com/a/215358/39384

@TomV :O omg

that may be true, but that answer is.. ouch :)

Morning

@TomV Worse than a 404? :-D

@Philᵀᴹ Know that feeling. But wouldn't it be nice to have a "close --> shows zero effort" ?

@TomV Haha, that would get an upvote for honesty from me! :-D

And prevent the OP from deleting the question

Another member of the new generation Heapsters:

@AndriyM Thanks for replying.

but will your sql statement work if I want to insert and retrieve both

for example

select a.id, b.name from tableA a join tableB b; id I want to return and name to be passed to next result set

In my actual stored procedure there 30 columns. Out of 30, I want 29 to return to api and 1 to be passed to next query inside stored procedure

SELECT id FROM @namelist;

SELECT ... FROM ... @namelist ... ;

As I said, you can re-use @namelist many times

So I think I need to insert all the 30 columns inside the temp table

and to return to API, I will searh 29 columsn from the temp table

and again another query to pass to next result set

@SpringLearner If I understand your situation well, I think that's what I would do too

@dezso Congrats

@AndriyM Thanks a lot

@dezso congrats

cute baby

(I am waiting to say 'Thanks' so spare resources)

congrats, @dezso! hope the baby looks like the mother :)

@dezso Grattis!!

You'd think someone would have edited Contributor Victim by now

@Marian I do, too

and thanks to all

@dezso Congratulations! Welcome to World! It's the best place to be.

2 messages deleted

please use flags for that sort of thing; there's nothing to be gained by public shaming

I know it can be frustrating.

I forget this place is public, sorry

quick question on postgres

When I'm running this

WITH d as
  ( SELECT *
   FROM report.get_sa001('2013-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
          a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Axis_Reference_All"."Site", "Reference_internal", "Customer_code") IN
       ( SELECT d."Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code";

np, I've got my results

when I'm trying to create a view on it like that

CREATE VIEW toto AS WITH d as
  ( SELECT *
   FROM report.get_sa001('2013-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
                           a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Axis_Reference_All"."Site", "Reference_internal", "Customer_code") IN
       ( SELECT d."Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code";

It gives me the following error

ERROR:  The column « Site » has been specified more than once
********** Erreur **********

ERROR: The column « Site » has been specified more than once
SQL State :42701

any ideas?

@AndyK when you run the sql that works, are there two columns with the same name ("Site") in the output? That's OK for a query but not if you are creating a view.

you'll have to explicitly name your columns rather than using "SELECT *" so you can alias one of them. Or use JOIN USING rather than JOIN ON for the columns which are named the same in both tables

@JackDouglas just tried the join using

no luck

which columns used USING in your attempt?

and is the error message indicating a different column this time

@JackDouglas it is a different message

can't tell from your SQL what columns appear in your output

@AndyK ok then fix that one too :)

left join a USING d."Site" = a."Site" AND d."Internal_reference" = a."Reference_internal" AND d."Customer_code" = a."Customer_code";

Meh, that got deleted as I was doing an awesome answer

@AndyK ^^^

@Philᵀᴹ please answer

@AndyK USING ("Site","Customer_code")

@JackDouglas brilliant!

it works

my answer

cya

@AndyK I don't see why you need the a CTE. Why don't you just use a LEFT JOIN in the main query? What purpose does "a" serve?