query help required to link two fields

[Former Member]

Hi all,

I have made an query as under :

SELECT T2.Period, T0.dept, T0.empID, T0.firstName, T0.lastName, T1.U_ebasic, T1.U_ehra, T1.U_etrspt, T1.U_ecola, T1.U_emeins, T1.U_education, T1.U_grosssal,T1.U_totduc, T1.U_oadd, T1.U_netsal, T1.U_bank, T1.U_branch, T1.U_accno FROM OHEM T0 , [dbo].[@PAYROLL_PR_DETAIL] T1, [dbo].[@PAYROLL_PROLL_HEAD] T2 where t2.docentry=t1.docentry and t1.U_ecode=t0.empid

Now I want that the Department Full NAME as mentioned in the Human Resource screen for that employee should come. Apart from it I also want to link a new field in this query user_code from OUSR table for each employee.

Can you please help me to make this query.

Regards,

kamlesh

[Former Member · Accepted solution]

Try this one:

SELECT T2.Period, T0.dept, T3.Name, T0.empID, T0.firstName, T0.lastName, T4.USER_CODE,T1.U_ebasic, T1.U_ehra, T1.U_etrspt, T1.U_ecola, T1.U_emeins, T1.U_education, T1.U_grosssal,T1.U_totduc, T1.U_oadd, T1.U_netsal, T1.U_bank, T1.U_branch, T1.U_accno

FROM dbo.OHEM T0

INNER JOIN dbo.OUDP T3 ON T3.Code = T0.dept

INNER JOIN dbo.OUSR T4 ON T4.INTERNAL_K = T0.userId

INNER JOIN dbo.@PAYROLL_PR_DETAIL T1 ON t1.U_ecode=t0.empid

INNER JION dbo.@PAYROLL_PROLL_HEAD T2 ON t2.docentry=t1.docentry

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon,

I tried your query but it giving an error when executing as - 'Query sentence was not complete QE - 3006) [Message 300-7].

Please help.

Regards,

Kamlesh

[Former Member · Accepted solution]

Sorry the last Join spelled wrong.

[Former Member · Accepted solution]

Dear Gordon,

After rectifiying the spelling mistake I used the following query as under

SELECT T2.Period, T0.dept, T3.Name, T0.empID, T0.firstName, T0.lastName, T4.USER_CODE,T1.U_ebasic, T1.U_ehra, T1.U_etrspt, T1.U_ecola, T1.U_emeins, T1.U_education, T1.U_grosssal,T1.U_totduc, T1.U_oadd, T1.U_netsal, T1.U_bank, T1.U_branch, T1.U_accno

FROM dbo.OHEM T0

INNER JOIN dbo.OUDP T3 ON T3.Code = T0.dept

INNER JOIN dbo.OUSR T4 ON T4.INTERNAL_K = T0.userId

INNER JOIN dbo.@PAYROLL_PR_DETAIL T1 ON t1.U_ecode=t0.empid

INNER JOIN dbo.@PAYROLL_PROLL_HEAD T2 ON t2.docentry=t1.docentry

but now the syntax error is coming as - 'Incorrect syntax near '@PAYROLL_PR_DETAIL'

(SEWSY) (s) could not be prepared

Please guide.

Regards,

Kamlesh

[former_member583013 · Accepted solution]

Please try to represent your tables in the query as

[dbo].[tablename]

example: [dbo].[dbo.@PAYROLL_PROLL_HEAD]

[Former Member · Accepted solution]

Dear Suda,

Thanks for guiding. Problem has been solved.

Would like to also thank gordon for the query.

regards,

kamlesh

[Former Member]

Try this one to see:

SELECT T2.Period, T0.dept, T3.Name, T0.empID, T0.firstName, T0.lastName, T4.USER_CODE,T1.U_ebasic, T1.U_ehra, T1.U_etrspt, T1.U_ecola, T1.U_emeins, T1.U_education, T1.U_grosssal,T1.U_totduc, T1.U_oadd, T1.U_netsal, T1.U_bank, T1.U_branch, T1.U_accno

FROM dbo.OHEM T0 INNER JOIN dbo.OUDP T3 ON T3.Code = T0.dept INNER JOIN dbo.OUSR T4 ON T4.INTERNAL_K = T0.userId,

dbo.@PAYROLL_PR_DETAIL T1, dbo.@PAYROLL_PROLL_HEAD T2

where t2.docentry=t1.docentry and t1.U_ecode=t0.empid

Thanks,

Gordon