sql server 2008 - How to do write this Join Statement? -

-

in sql i'm select data more 1 table using join. want select multiple condition , therefor using , , or. here need if jobcode is=1 select data jobcode 1, if jobcode 2 show data jobcode 2.but error when puting jobcode=1 swoing data, 2 same data, 0 same data. i'm making mistake. me.

use [recruitment] go /****** object:  storedprocedure [dbo].[sp_advancesearch]    script date: 05/11/2011 09:47:43 ******/ set ansi_nulls on go set quoted_identifier on go alter procedure [dbo].[sp_advancesearch]  @callval int=9, @jobcode int=0, @location_id int=0, @position varchar(20)='', @flag char(1)='', @jobdesc varchar(max)='', @poststreamid varchar(10)='', @graduquali varchar(100)='', @ten2streamid varchar(10)='', @streamid varchar(10)='', @reqexp varchar(15)='', @salary varchar(25)='', @joblocation varchar(15)='', @postdate datetime='', @enddate datetime='', @c_f_name varchar(15)='', @c_m_name varchar(15)='', @c_l_name varchar(15)='', @email varchar(30)='', @personal_id int=0, @academic_id int=0, @family_id int=0, @profess_id int=0, @applyid int=0, @dtinterview int=0, @timereq int=0, @expsalary decimal(12,2)=0, @epsalary decimal(12,2)=50000000, @resume varchar(max)='', @howdid int=0, @dob datetime='1/1/1900', @do datetime='1/1/2050', @applydate datetime='', @prof_qual varchar(10)='', @prof_stream varchar(50)='0', @prof_stream1 varchar(24)='', @prof_stream2 varchar(24)='', @prof_stream3 varchar(24)='', @prof_year char(4)='2050', @grad_qual varchar(10)='bsc', @grad_stream varchar(15)='0', @grad_stream1  varchar(15)='', @grad_stream2  varchar(15)='', @grad_stream3  varchar(15)='', @grad_year char(4)='2050', @prof_year1  char(4)='0', @grad_year1 char(4)='0', @assignment1 int=0, @assignment2 int=0, @assignment3 int=0, @assignment4 int=0, @assignment5 int=0, @ass1 int=6, @ass2 int=6, @ass3 int=6, @ass4 int=6, @ass5 int=6, @ex_yyyy int=0, @e_yyyy int=100, @em_gross decimal(12,2)=50000000, @emp_gross decimal(12,2)=0, @city_id int=0, @city_name varchar(15)=''  begin  if @callval=9 begin declare @proce varchar(5000)  set @proce ='select rp_tbl_personal_details.c_f_name  +''''+c_m_name +''''+ c_l_name name, rp_tbl_personal_details.dob,rp_tbl_personal_details.email, rp_tbl_academic_details.prof_qual,rp_tbl_academic_details.grad_qual, rp_tbl_academic_details.prof_stream,rp_tbl_academic_details.prof_year, rp_tbl_academic_details.grad_stream,rp_tbl_academic_details.grad_year, rp_tbl_professional.ex_yyyy, rp_tbl_family.assignment1,rp_tbl_family.assignment2,rp_tbl_family.assignment3, rp_tbl_family.assignment4,rp_tbl_family.assignment5, rp_tbl_professional.emp_gross,rp_tbl_createnewjob.jobcode,rp_tbl_createnewjob.position, rp_tbl_applyforjob.personal_id,rp_tbl_applyforjob.timereq,rp_tbl_applyforjob.expsalary, dtinterview = case rp_tbl_applyforjob.dtinterview when 1 ''any day'' when 2 ''week day'' when 3 ''weekends'' else ''other'' end rp_tbl_createnewjob inner join rp_tbl_applyforjob on rp_tbl_createnewjob.jobcode=rp_tbl_applyforjob.jobcode inner join rp_tbl_personal_details on rp_tbl_applyforjob.personal_id=rp_tbl_personal_details.personal_id inner join rp_tbl_academic_details on rp_tbl_personal_details.personal_id=rp_tbl_academic_details.personal_id inner join rp_tbl_family on rp_tbl_academic_details.academic_id=rp_tbl_family.academic_id inner join rp_tbl_professional on rp_tbl_family.family_id=rp_tbl_professional.family_id 1=1 , rp_tbl_createnewjob.jobcode =' + convert (varchar(50),@jobcode)+  '  ,  rp_tbl_personal_details.dob between ' + ''''+ convert(varchar(50),@dob) + ''''+'  ,  ' +''''+ convert(varchar(50),@do)+'''' if (@prof_qual!='') begin set @proce =@proce + ' , rp_tbl_academic_details.prof_qual in ('+'''' + convert (varchar (50),@prof_qual)+''')' end if (@prof_stream!='0') begin set @proce =@proce + ' , rp_tbl_academic_details.prof_stream in (' + @prof_stream +')' end set @proce =@proce + ' , rp_tbl_academic_details.prof_year between ' + ''''+ convert(varchar(50),@prof_year1 ) + '''' + '  , ' + '''' + convert(varchar(50),@prof_year )+ ''''   if (@grad_qual!='') begin set @proce =@proce + ' , rp_tbl_academic_details.grad_qual in (''' + @grad_qual +''')' end if (@grad_stream!='0') begin set @proce =@proce + ' , rp_tbl_academic_details.grad_stream in (' + @grad_stream +')' end set @proce =@proce + ' or rp_tbl_academic_details.grad_year between ' + ''''+ convert(varchar(50),@grad_year1 ) + '''' + '  , ' + '''' + convert(varchar(50),@grad_year )+ ''''   if (@assignment1 != 0) begin set @proce =@proce + ' or rp_tbl_family.assignment1 = ' + convert(varchar(50),@assignment1 ) end if (@assignment2 != 0) begin set @proce =@proce + ' or rp_tbl_family.assignment2 = ' + convert(varchar(50),@assignment2 ) end if (@assignment3 != 0) begin set @proce =@proce + ' or rp_tbl_family.assignment3 = ' + convert(varchar(50),@assignment3 ) end if (@assignment4 != 0) begin set @proce =@proce + ' or rp_tbl_family.assignment4 = ' + convert(varchar(50),@assignment4 ) end if (@assignment5 != 0) begin set @proce =@proce + ' or rp_tbl_family.assignment5 = ' + convert(varchar(50),@assignment5 ) end if (@ex_yyyy !=0 , @e_yyyy!=100) begin set @proce =@proce + ' , rp_tbl_professional.ex_yyyy between ' + ''''+ convert(varchar(50),@ex_yyyy ) + '''' + '  , ' + '''' + convert(varchar(50),@e_yyyy )+ ''''   end if (@emp_gross !=0 , @em_gross!=50000000) begin set @proce =@proce + ' or rp_tbl_professional.emp_gross between ' + ''''+ convert(varchar(50),@emp_gross ) + '''' + '  , ' + '''' + convert(varchar(50),@em_gross )+ ''''   end if (@expsalary !=0 , @epsalary !=50000000) begin set @proce =@proce + ' or rp_tbl_applyforjob.expsalary between ' + ''''+ convert(varchar(50),@expsalary ) + '''' + '  , ' + '''' + convert(varchar(50),@epsalary )+ ''''   end print (@proce) exec(@proce) end end

i test code getting output query , got. omitted other statements statement , made alias table.

where 1=1     , cnj.jobcode =0     , pd.dob between 'jan  1 1900 12:00am' , 'jan  1 2050 12:00am'      , ad.prof_qual in ('')      , ad.prof_stream in (0)      , ad.prof_year between '0   '  , '2050'      , ad.grad_qual in ('bsc')      , ad.grad_stream in (0)      or ad.grad_year between '0   '  , '2050'      or fa.assignment1 = 0      or fa.assignment2 = 0      or fa.assignment3 = 0      or fa.assignment4 = 0      or fa.assignment5 = 0      , pr.ex_yyyy between '0'  , '100'      or pr.emp_gross between '0'  , '50000000'      or apr.expsalary between '0'  , '50000000'

from obsetvation, i'm not sure why include '1=1'. becomes true. , reason why getting all/same data because of or statements. might need group or statements inside parenthesis. 

and (         ad.grad_year between '0   '  , '2050'      or fa.assignment1 = 0      or fa.assignment2 = 0      or fa.assignment3 = 0      or fa.assignment4 = 0      or fa.assignment5 = 0     )     , pr.ex_yyyy between '0'  , '100'      , (        pr.emp_gross between '0'  , '50000000'      or apr.expsalary between '0'  , '50000000')

also noticed spaces on output of variables @ betweeen statement, might need deal it.


Comments

Post a Comment

Popular posts from this blog

Cursor error with postgresql, pgpool and php -

-
hey, struggling bit determine exact cause of error has been popping in our release environment. there not seem dealing particular error on google. this error message getting: sqlstate[34000]: invalid cursor name: 7 error: portal "" not exist the error pops when using pdo prepared statements. this setup our release environment: pgpool 3.0.1 (the postgresql backend in streaming replication mode!) php 5.3.5 postgresql 9.0 edit: architecture 64bit. the same error not manifest in our test environment (edit: forgot mention, standard test environment uses postgresql 9.0 without pgpool). thus, led suspect pgpool @ least partly suspect. does know probable causes error are? edit: ok, here example of kind of code causes error. $sql = 'select * '; $sql .= 'from "mytable" "mystuff" '; $sql .= 'where "mytable"."status" = 1 '; $sql .= 'and "mytable"."mytableid" = :table
Read more

c# - how to write client side events functions for the combobox items -

-
<dx:aspxcombobox id="aspxcombobox2" runat="server" clientidmode="autoid" selectedindex="-1" onselectedindexchanged="aspxcombobox1_selectedindexchanged1" > <clientsideevents selectedindexchanged="function(s, e) { grid.selectrows();}" /> <items> <dx:listedititem text="selecte rows" /> <dx:listedititem text="unselectall" /> </items> </dx:aspxcombobox> i want write event "unselect all" item in combobox. how can this? you'll need write javascript function iterates thru combo-box , marks selected items false. see select/unselect checkbox fields using pure javascript , html sample code msdn:
Read more

ssh client with paramiko (python) -

-
# sshpy v1 s0urd # simple ssh client # irc.gonullyourself.org 6667 #code import paramiko import os ssh = paramiko.sshclient() ssh.set_missing_host_key_policy(paramiko.autoaddpolicy()) privatekey = os.path.expanduser('/home/rabia/private') mkey = paramiko.rsakey.from_private_key_file(privatekey) ssh.connect('78.46.172.47', port=22, username='s0urd', password=none, pkey=mkey) while true: pick = raw_input("sshpy: ") stdin, stdout, stderr = ssh.exec_command(pick) print stdout.readlines() ssh.close() when try run more 1 command error: attributeerror: 'nonetype' object has no attribute 'open_session' looks it's because @ end of while loop ssh.close() (thus closing session).
Read more