Payroll_problem_SQL/Dynamic_Search.sql at master · anik112/Payroll_problem_SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
SELECT  info.sectionnm, info.cardno, info.secreteno, info.empname, info.designation, pic.emppicture
FROM     TB_PERSONAL_INFO info, TB_PERSONAL_INFO_PICTURE pic
WHERE  info.company  = :p_company
AND    info.company      = pic.company
AND    info.cardno         = pic.cardno
AND    info.cardno                     LIKE '%'||:card||'%'
AND    info.secreteno                LIKE DECODE (NVL(:secrete,'all'),'all','%',:secrete)
AND    UPPER(info.empname)  LIKE '%'||UPPER(:ename)||'%'
AND    info.ac_no                      LIKE DECODE (NVL(:p_bankac,'all'),'all','%',:p_bankac)
ORDER BY info.cardno ASC


--------------------------------


SELECT info.sectionnm, info.cardno, info.secreteno, info.empname, info.designation, pic.emppicture
FROM TB_PERSONAL_INFO info, TB_PERSONAL_INFO_PICTURE pic
WHERE  info.company  = :p_company
AND    info.company    = pic.company
AND    info.cardno       = pic.cardno
AND    info.cardno                     LIKE '%'||:card||'%'
AND    info.secreteno                LIKE DECODE (NVL(:secrete,'all'),'all','%',:secrete)
AND    UPPER(info.empname)  LIKE '%'||UPPER(:ename)||'%'
AND    info.mft_acno                      LIKE DECODE (NVL(:p_bankac,'all'),'all','%',:p_bankac)
ORDER BY info.cardno ASC


---------------------------------


SELECT info.sectionnm, info.cardno, info.secreteno, info.empname, info.designation, pic.emppicture
FROM TB_PERSONAL_INFO info, TB_PERSONAL_INFO_PICTURE pic, TB_PERSONAL_INFO_OTHER other
WHERE  info.company  = :p_company
AND    info.company    = pic.company
AND    info.company    = other.company
AND    info.cardno       = pic.cardno
AND    info.cardno       = other.cardno
AND    info.cardno                     LIKE '%'||:card||'%'
AND    info.secreteno                LIKE DECODE (NVL(:secrete,'all'),'all','%',:secrete)
AND    UPPER(info.empname)  LIKE '%'||UPPER(:ename)||'%'
AND    other.own_cellno LIKE DECODE (NVL(:p_cellno,'all'),'all','%',:p_cellno)
ORDER BY info.cardno ASC


---------------------------------------


SELECT info.sectionnm, info.cardno, info.secreteno, info.empname, info.designation, pic.emppicture
FROM   TB_PERSONAL_INFO info, TB_PERSONAL_INFO_PICTURE pic, TB_PERSONAL_INFO_OTHER other
WHERE  info.company  = :p_company
AND    info.company    = pic.company
AND    info.company    = other.company
AND    info.cardno       = pic.cardno
AND    info.cardno       = other.cardno
AND    info.cardno                     LIKE '%'||:card||'%'
AND    info.secreteno                LIKE DECODE (NVL(:secrete,'all'),'all','%',:secrete)
AND    UPPER(info.empname)  LIKE '%'||UPPER(:ename)||'%'
AND    other.nominee_cellno LIKE DECODE (NVL(:p_cellno,'all'),'all','%',:p_cellno)
ORDER BY info.cardno ASC


-----------------------


SELECT info.sectionnm, info.cardno, info.secreteno, info.empname, info.designation, pic.emppicture
FROM TB_PERSONAL_INFO info, TB_PERSONAL_INFO_PICTURE pic
WHERE  info.company  = :p_company
AND    info.company    = pic.company
AND    info.cardno       = pic.cardno
AND    info.cardno                     LIKE '%'||:card||'%'
AND    info.secreteno                LIKE DECODE (NVL(:secrete,'all'),'all','%',:secrete)
AND    UPPER(info.empname)  LIKE '%'||UPPER(:ename)||'%'
ORDER BY info.cardno ASC