Re: From with case

Thanks very much Pavel, the dynamic SQL ideia has work perfect in my
case.

Thanks so much.

--
Grato,
Mauricio Cruz
Sygecom Informática
51 3442-3975 / 3442-2345

On Mon, 25 Mar 2013 15:01:09 +0100, Pavel Stehule
<pavel(dot)stehule(at)gmail(dot)com> wrote:
> Hello
>
>
>> For rSql in select a.adiant,
>> a.desc_per
>> from case
>> when cTip='P'
>> then valapag
>> else valerec
>> end
>> where cod=2 Loop
>
>
> you can use a dynamic SQL, but it is not best solution usually. In
> this case I usually prefer
>
> IF cTip = 'P' THEN
> FOR r IN SELECT .. FROM valapag LOOP
> PERFORM proc(r);
> END LOOP;
> ELSE
> FOR r IN SELECT .. FROM valerec LOOP
> PERFORM proc(r);
> END LOOP;
> END IF;
>
> with dynamic SQL
>
> FOR r IN EXECUTE format('SELECT .. FROM %I ..', CASE WHEN ctip = 'P'
> THEN 'valapag' ELSE 'valerec' END)
> LOOP
> ..
> END LOOP;
>
> Regards
>
> Pavel Stehule
>
>
> 2013/3/25 Mauricio Cruz <cruz(at)sygecom(dot)com(dot)br>:
>> Hi everyone,
>>
>>
>>
>> I'm working in a PL/SQL and I'd like to use the same PL for 2 kinds of
>> tables...
>>
>> I have "valepag" and "valerec" both tables have the same columns, but one is
>> for debit and the other one is for credit, the PL will work for both cases
>>
>> with the unique diference for the name of the table...
>>
>>
>>
>> So I thought to use something like this:
>>
>> ...
>>
>> For rSql in select a.adiant,
>> a.desc_per
>> from case
>> when cTip='P'
>> then valapag
>> else valerec
>> end
>> where cod=2 Loop
>>
>>
>>
>> ...
>>
>>
>> But it just dont work... does some one have other solution for this case ?
>>
>>
>>
>> Thanks guys.
>>
>>
>>
>> --
>> Grato,
>> Mauricio Cruz
>> Sygecom Informática
>> 51 3442-3975 / 3442-2345