help me Sql Help.....

[4Vikram]

SQL Experts help please....

 

I have 4 different tables and using those tables I have to update the target table..

 

#ZAH is my target table with 5 different columns

RES is my source table 

ZAG is another table to extract values for column in #ZAH

ZAU is a customizing table to extract the required data from the RES Table...

 

Now I want to extract data from source (RES) with using customizing table and then update ZAH table

 

my output is coming through this code but the thing is customizing table is not used in this.. 

 

CREATE TABLE #ZAH

(

[ZAH_ID] [int]  NOT NULL IDENTITY(1,1),

[ZAH_RES_ID] [int] NULL,

[ZAH_ZAG_ID] [int] NULL,

[ZAH_START] [datetime] NULL,

[ZAH_END] [datetime] NULL

)

 

INSERT INTO #ZAH ([ZAH_RES_ID],[ZAH_ZAG_ID],[ZAH_START],[ZAH_END] )

SELECT [ZAH_RES_ID],[ZAH_ZAG_ID],[ZAH_START],[ZAH_END] FROM ZAH

 

--SELECT * FROM ZAH WHERE ZAH_RES_ID = 16106

 

Merge #ZAH as T 

using RES as S

on S.RES_ID=T.ZAH_RES_ID 

When Not Matched by Target 

Then insert (ZAH_RES_ID)

Values (S.RES_ID);

 

SELECT ZAH_RES_ID 

INTO #CntResID

FROM #ZAH GROUP BY ZAH_RES_ID HAVING COUNT(*)=1

ORDER BY 1

 

Select * FROM ZAU

 

UPDATE #ZAH

SET ZAH_ZAG_ID = 2000

,ZAH_START = '2015-01-01'

FROM #ZAH Z

JOIN #CntResID R

ON Z.ZAH_RES_ID = R.ZAH_RES_ID

 

please sql experts help... urgent help

 

 

 

[spidereddy]

prob ekada boss..

[spidereddy]

u mean ZAU is a view?

[okkamagaduny]

u mean ZAU is a view?

you  sql expert?

[4Vikram]

u mean ZAU is a view?

Yeah kind of,,, it has 5-6 different columns using those I have to extract RES_ID....Experts help please LTT

[VenkatD]

Help

[spidereddy]

Yeah kind of,,, it has 5-6 different columns using those I have to extract RES_ID....Experts help please LTT

when u're getting the required output..how does it really matter to use the customizing table?

[4Vikram]

when u're getting the required output..how does it really matter to use the customizing table?

the point is we cannot extract the RES_ID just from the RES table... it has to be matching with records that belongs to ZAU as well..

[vikuba]

they told u to ...involve ZAU to get RES_ID ?

if yes..

can specifiy the relation between RES and ZAU?

[mtkr]

K

[4Vikram]

they told u to ...involve ZAU to get RES_ID ?

if yes..

can specifiy the relation between RES and ZAU?

Yes, ZAU has some set of columns and these columns are used as criteria to get the RES-ID

 

It means for RES-ID=5 it should be satified with the columns in ZAU which belong to RES-ID=5. ZAU table is something like this

 

 

FN PR GL    BU  RES-ID

4    3   1002  3    4         

4    3    540   0    5

[pachimirchi]

based on what I understand..

 

Merge #ZAH as T 

using (select RES.RES_ID from RES join

ZAU on RES.RES_ID=ZAU.RES_ID)

 as S

on S.RES_ID=T.ZAH_RES_ID 

When Not Matched by Target 

Then insert (ZAH_RES_ID)

Values (S.RES_ID);

 

[4Vikram]

 

based on what I understand..

 

Merge #ZAH as T 

using (select RES.RES_ID from RES join

ZAU on RES.RES_ID=ZAU.RES_ID)

 as S

on S.RES_ID=T.ZAH_RES_ID 

When Not Matched by Target 

Then insert (ZAH_RES_ID)

Values (S.RES_ID);

 

this I already tried but didnt work..

[4Vikram]

LTT for experts please help...... urgent situation

[pachimirchi]

this I already tried but didnt work..

what didn't work? error ochinda? RESID column ZAU lo FK ayundali and RES table lo PK.. neku kavalsindi RESID common values between RES and ZAU kada?