Question
SQL Like and Access database
i have to retrieve data from access based on Emp_ID extracted from Sql DB. the queries looks like the following:
Can i do 3 queries and do something like the following:
<cfquery datasource="MasterSQL" username="***" password="***" name="SQLQRY">
Select Employee.Emp_Name, Salary.Emp_ID
From Employee inner join Salary on (Employee.Emp_ID = Salary.Emp_ID) AND (Employee.SectionName = Salary.SectionName)
Where Emp_Name like <cfqueryparam value="%#arguments.search#%" cfsqltype="Sql_VarChar">
</cfquery>
Select Employee.Emp_Name, Salary.Emp_ID
From Employee inner join Salary on (Employee.Emp_ID = Salary.Emp_ID) AND (Employee.SectionName = Salary.SectionName)
Where Emp_Name like <cfqueryparam value="%#arguments.search#%" cfsqltype="Sql_VarChar">
</cfquery>
<cfquery datasource="MasterDB" name="AccessQRY">
Select Emp_Pro.Emp_ID
From Emp_Pro
WHERE Emp_Pro.Emp_ID like ("%#ValueList(SQLQRY.Emp_ID)#%")
</cfquery>
Select Emp_Pro.Emp_ID
From Emp_Pro
WHERE Emp_Pro.Emp_ID like ("%#ValueList(SQLQRY.Emp_ID)#%")
</cfquery>
<cfquery datasource="MasterDB" name="AccessQRY2">
Select Emp_Info.Desc,
From Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)
WHERE Emp_Info.Emp_ID IN
Select Emp_Info.Desc,
From Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)
WHERE Emp_Info.Emp_ID IN
this block will change too...
( <cfqueryparam
value="#ValueList(SQLQRY.Emp_ID)#"
cfsqltype="cf_sql_integer"
list="true">
)
( <cfqueryparam
value="#ValueList(SQLQRY.Emp_ID)#"
cfsqltype="cf_sql_integer"
list="true">
)
</cfquery>
What do i have to change to make this query work...
AdChoices