Table merge - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Table merge

Author

Topic

sureshprpt
Starting Member

33 Posts

Posted - 2010-08-28 : 02:55:11

I try to pull data from two table and the fields , the issue is the first table have two fields customer no and consignee no, but the address for both no are in same table. If I merge the both table only one name will appear. Kindly help me to download the data and the format are enclosed for your ready reference.

Table1
ADDRESS No(CUSTOMER), ADDRESS No(CONSIGNEE), Order no, Order no.
Table 2
ADDRESS NO, ADDRESS NAME

I Want the output of the reports is
Address name(Customer), ADDRESS NAME (CONSIGNEE), Order no, Order no

Thanks & Regards
Suresh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-28 : 03:09:17

join it two times


SELECT t2.ADDRESSNAME AS CustAddName,
t3.ADDRESSNAME AS ConsAddName,
t1.OrderNo,
t1.Orerno...
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.ADDRESSNO = t1.[ADDRESSNO(CUSTOMER)]
LEFT JOIN Table2 t3
ON t3.ADDRESSNO = t1.[ADDRESSNo(CONSIGNEE)]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sureshprpt
Starting Member

33 Posts

Posted - 2010-09-18 : 01:27:33

But i have only two tables(tabl1 & table2), How i mention the table 3 . Kindly help me

quote:
Originally posted by visakh16

join it two times

SELECT t2.ADDRESSNAME AS CustAddName,
t3.ADDRESSNAME AS ConsAddName,
t1.OrderNo,
t1.Orerno...
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.ADDRESSNO = t1.[ADDRESSNO(CUSTOMER)]
LEFT JOIN Table2 t3
ON t3.ADDRESSNO = t1.[ADDRESSNo(CONSIGNEE)]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks & Regards
Suresh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-18 : 09:06:54

its same table itself with different alias see Table2 t3 you're refering same table again as another alias

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sureshprpt
Starting Member

33 Posts

Posted - 2010-09-20 : 03:27:09

I tried , it shows error . Kindly send the full query

Thanks & Regards
Suresh

sureshprpt
Starting Member

33 Posts

Posted - 2010-09-20 : 04:52:19

quote:
Originally posted by sureshprpt

I tried , it shows error . Kindly send the full query

The error message show below
CWBDB0036 - Server returned SQL error

SQL0212 - Duplicate table designator F00365 not valid.

Cause . . . . . : One of the following has occurred: -- More than one table in a FROM clause of a subselect has a table designator with the name F00365. If a correlation name is specified, the correlation name is the table designator. If one is not specified, the table name or view name is the table designator. If SQL naming is specified, the table name consists of the implicit or explicit schema name followed by the actual table name. If system naming is specified, the table name itself is used without a qualifier as the table designator. The table designator must be unique on each level of a subselect. -- Names specified in the REFERENCING clause of a CREATE TRIGGER statement are not unique. The names specified for the NEW and OLD correlation name and the NEW_TABLE and OLD_TABLE names must be unique and must not be the same as the table on which the trigger is being defined. Recovery . . . : Make certain there is a unique table designator for every table in a FROM clause for the same level of a subselect. Since schema-name/table-name cannot be used to qualify a column, the table name must be unique or a correlation name must be specified. Specify unique names in the REFERENCING clause of the CREATE TRIGGER statement. Correct any errors and try the request again.

The Query are
SELECT F0311.RPAN8"CUST.NO",F0311.RPALPH"CUST NAME",F0311.RPSDOC"ORDER NO",F0311.RPSDCT"ORDER TY",F58421A.CCLGL1"CUST.PONO",F0311.RPDOC"INVOICE NO" ,F0311.RPDCT"TYPE",F00365.ONDATE "INVOICE DATE",F00365.ONDATE,F0311.RPDDJ"DUE DATE",F0311.RPAG/1000"ORIGINAL AMT",F0311.RPAAP/100 "OPEN AMT"FROM P2DTAA/F0311 LEFT OUTER JOIN F58421A ON F58421A.CCDOCO = F0311.RPSDOC AND F58421A.CCDCTO = F0311.RPSDCT LEFT OUTER JOIN F00365 ON F00365. ONDTEJ = F0311.RPDIVJ LEFT OUTER JOIN F00365 ON F00365.ONDTEJ = F0311.RPDDJ WHERE AND (F0311.RPAAP > 0) AND (F0311.RPTRTC <> 'G')
The tables are F0311,F58421a,F00365

Thanks & Regards
Suresh

Thanks & Regards
Suresh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-21 : 12:30:09

are you really using MS SQL server? i doubt seeing the error message

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sureshprpt
Starting Member

33 Posts

Posted - 2010-09-22 : 08:34:57

I use Iseries Navigator from JDESoftware . In this software SQL query is there , thro' this i downloaded the data

quote:
Originally posted by visakh16

are you really using MS SQL server? i doubt seeing the error message

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks & Regards
Suresh

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-22 : 08:44:53

I think that's DB2

There's a forum over at dbforums
http://www.dbforums.com/db2/

It's a completely different product. This forum is for Microsoft SQL Server so we won't be able to help unless a passing DB2 wizard cross posts.

Good luck.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-22 : 08:51:28

from db2 docs:


SQL0212N
name is a duplicate table designator or is specified more than once in the REFERENCING clause of a trigger definition.
Explanation:
The exposed table, view, alias, or correlation name specified by name is identical to another exposed table, view, alias, or correlation name in the same FROM clause.

If the statement is a CREATE TRIGGER, the REFERENCING clause may have specified the same name as the subject table or may have the same name for more than one of the OLD or NEW correlation names or the NEW_TABLE or OLD_TABLE identifiers.

The statement cannot be processed.

User response:
Rewrite the FROM clause of the SELECT statement. Associate correlation names with table, view, or alias names so no exposed table, view, alias, or correlation name is identical to any other exposed table, view, alias, or correlation name in the FROM clause.

For a CREATE TRIGGER statement, change the names in the REFERENCING clause so that there are no duplicates.

sqlcode: -212

sqlstate: 42712

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources