3 replies to this topic

[anamaricar]

hi, has anyone tried to join tables in a dynamic query? here's what I wrote which is not working


$TABLES = TABLE1 + ' T1, ' + TABLE2 + ' T2 '
$CONDITION = 'T1.ID = T2.ID'

SQL DYNAMIC
SELECT T1.*
FROM $TABLES
WHERE :$CONDITION
ENDSQL

I may be missing some codes or is this not really allowed? by the way, this is in a default logic of a report, if this info helps

[Chris Mackenzie]

hi, has anyone tried to join tables in a dynamic query? here's what I wrote which is not working


$TABLES = TABLE1 + ' T1, ' + TABLE2 + ' T2 '
$CONDITION = 'T1.ID = T2.ID'

SQL DYNAMIC
SELECT T1.*
FROM $TABLES
WHERE :$CONDITION
ENDSQL

I may be missing some codes or is this not really allowed? by the way, this is in a default logic of a report, if this info helps

Pro-IV doesn't like that much.
You can compose your string in pro, like this

$SQLSTRING = "SELECT " + $WFVAR_ALT_ID(#I) + " FROM " + $WFVAR_TABLE(#I)
+ " WHERE REF = :ref"

NB the host variable, :ref and this example's single column selection here

Then, create PL/SQL code something like this

create or replace procedure execSql(vDynamicStatement in varchar2, vRef in varchar2) is

data_item varchar2(250);

begin

execute immediate vDynamicStatement INTO data_item USING vPersonRef;
/*
note the host var is populated by the USING clause and and the result comes back to the INTO clause
this example only show one of each.


And this bit is where we put the result into a temp table
*/
begin
insert into TEMPTABLE VALUES (sys_guid(), NVL(data_item,' '), 'N');
exception when dup_val_on_index then
update W325M set work_data = NVL(data_item,' ') where work_operator_id = vOperatorID and [...];
end;
commit;
end execsql


So then execute the procedure from pro


SQL
BEGIN
EXECSQL(:$SQLSTRING, :$REF);
END;
ENDSQL

Then just read the temp table in pro-iv


I'm open to any suggestions for improvements.

Edited by Chris Mackenzie, 30 January 2007 - 11:14 AM.

[anamaricar]

if this is not allowed we will just retain the codes we have in selecting records, the attempt to embed SQL is to make the report work faster, thanks anyway Chris

[mdexter]

The problem might be that PRO-IV adds some text to SQL when it is used in the selection portion of a cycle (e.g., in the cycle entry logic). For example, in SQL Server, if the target table is in LOOKUP mode, PRO-IV will add the query hint "WITH(NOLOCK)" to the SQL. This is fine as long as the SQL command only contains one table. However, if you are trying to do a sort or select on more than one table, PRO-IV doesn't add this query hint correctly, so the SQL command fails.

The only work-around that we have found is to build a view in SQL and then build a PRO-IV file spec that references the view. A typical case is where you need to sort or select on columns not in the primary table. So if you build a view that joins the tables you need, create a PRO-IV file spec that references this view, and then use that as the primary file in the cycle, it works. It's more work, but in some cases it can make a dramatic improvement in performance (e.g., in a case where otherwise you are doing a lot of read / DSEL).

A simple fix for PRO-IV would be to have an option in logic to tell PRO-IV not to alter the SQL at all (e.g., something like SQL DYNAMIC RAW). This would seem to be fairly easy for them to implement and would help us out a lot. With that, you could create any type of select statement you wanted, with as many joins as needed. The developer would just need so ensure that the column list returned by the SELECT statement matched the PRO-IV file spec for the primary file in the cycle, which is easy to do.

Hope this helps. Mark Dexter