March 3, 2019 at 6:22 am
I have three Tables which I Joined them , got the duplicate result as I had unique Document No. Present in slbReceivablesTrx A Table but in RM20201 B table, the document no. was present in 2 different fields, .The first field was B.APFRDCNM and the 2nd field was B.APTODCNM . that was the reason , I joined these two tables by using CUSTNMBR field , which is duplicate in both tables. and as a result I got correct data but with repeated same rows (Duplicate rows), so I thought, to use the Derived table query with Distinct function so that I will get unique record . but after this, I get SQL Error (ERRPR: CUSTNBR is used Repeatedly for Table D). So I have 2 Questions:
1--- How do I change the name of Customer NO. field when I join 2 tables . Can you let me know that How do I alias the customer No Field because even when I join 2 tables and Change the name of CUSTNBR , SQL gives me error that the CUSTNBR1 is not present anywhere..Let me show you what I write
SELECT A.CUSTNBR AS CUSTNBR1,A.DOCNUMBR AS DOCNUMBR1,A,CURTRXAMT AS[CURRENT TRANS AMOUNT] FROM slbReceivablesTrx A
JOIN RM20201 B
ON CUSTNMBR1= B.CUSTNMBR
WHEN I EXECUTE THE ABOVE SQL QUERY, SQL GIVES ME THE ERROR THAT CUSTNBR1 NOT FOUND ANY WHERE, DOCNUMBR1 NOT FOUND ANY WHERE. Can you please let me know, what ;s wrong with the above query??
2---- My 2nd Question is related to the below SQL Query, as How do I make duplicate rows as Distinct rows. I used Derived Table Query, but because of Customer Number field Present in all 3 tables , It is not allowing me to use CUSTNBR Field for Logical Table D. I used Outer Left Join as well, but it is not giving the correct result.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT D.CUSTMBR AS CUSTNMBR1, D.DOCNUMBR AS DOCUMBR1 FROM (
SELECT * FROM slbReceivablesTrx A
JOIN RM20201 B
ON A.CUSTNMBR= B.CUSTNMBR
JOIN RM20101 C
ON C.custnmbr = d.CUSTNMBR
WHERE A.DOCNUMBR <> B.APTODCNM
AND A.DOCNUMBR <> B.APFRDCNM AND A.CURTRXAM <>0
and A.DOCNUMBR <> C.DOCNUMBR
) AS D
March 4, 2019 at 5:04 am
Everything in the inline table needs to have a distinct column names. So instead of just SELECT * you need to list the columns you want.
If you want two columns that have the same name you just need to alias them in the sub-query so they have different names.
March 4, 2019 at 5:50 am
SELECT * FROM A_Table
JOIN B_table
ON A.something= B.something
All columns of A_table and of B_table are used. If there are columnnames which are the same in both tables, the result will be two columns with the same name. For direct output this is allowed. Not when this is a subquery (part of another query). So replace the * with the named columns of A and of B and for columns which appear more than once rename them.
Example
SELECT A.x,A.y.A.z,B.p,B.r,B.x as B_x FROM .....
To detect errors more easely do not use the "*" in select statements. When using specific columnnames, you get more specific errors. This makes it easier to see where something goes wrong and what goes wrong.
Ben
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply