I'm having trouble with the correct syntax for 'encrypted with' and
'union'

  select h.HHid GenID
    into junk
    from households h
    encrypted with 'junk'

creates an encrypted table.

but

  select h.HHid GenID
    into junk
    from households h
  union
  select i.Indid GenID
    from individuals i
  encrypted with 'junk'


creates an unencrypted table.  The encrypted with phrase seems to be
ignored in the union case. It's probably something obvious


JonD

Jon,

This is just by looking at manual so might not work.

However the manual it says "The WHERE, GROUP BY, HAVING, LOCALE, ENCRYPTED, and NOJOINOPTIMIZE clauses can be specified for all or some of the individual SELECT statements being joined with a UNION/EXCEPT/INTERSECT clause"

Therefore it seems you're only encrypting the 2nd select - try

select h.HHid GenID
    into junk
    from households h
  encrypted with 'junk'
  union
  select i.Indid GenID
    from individuals i
  encrypted with 'junk'

Raul,

I'll give that a try... thanks

JonD

On 2011-09-24 7:39 PM, Raul wrote:
> Jon,
>
> This is just by looking at manual so might not work.
>
> However the manual it says "The WHERE, GROUP BY, HAVING, LOCALE, ENCRYPTED, and NOJOINOPTIMIZE clauses can be specified for all or some of the individual SELECT statements being joined with a UNION/EXCEPT/INTERSECT clause"
>
> Therefore it seems you're only encrypting the 2nd select - try
>
> select h.HHid GenID
>      into junk
>      from households h
>    encrypted with 'junk'
>    union
>    select i.Indid GenID
>      from individuals i
>    encrypted with 'junk'
>

Raul

The simple solutions are the best!

That worked

Thanks,
  JonD

On 2011-09-26 7:02 PM, Jon Lloyd Duerdoth wrote:
> Raul,
>
> I'll give that a try... thanks
>
> JonD
>
> On 2011-09-24 7:39 PM, Raul wrote:
>> Jon,
>>
>> This is just by looking at manual so might not work.
>>
>> However the manual it says "The WHERE, GROUP BY, HAVING, LOCALE, ENCRYPTED, and NOJOINOPTIMIZE clauses can be specified for all or some of the individual SELECT statements being joined with a UNION/EXCEPT/INTERSECT clause"
>>
>> Therefore it seems you're only encrypting the 2nd select - try
>>
>> select h.HHid GenID
>>      into junk
>>      from households h
>>    encrypted with 'junk'
>>    union
>>    select i.Indid GenID
>>      from individuals i
>>    encrypted with 'junk'
>>

Jon,

<< creates an unencrypted table.  The encrypted with phrase seems to be
ignored in the union case. It's probably something obvious >>

For UNION/EXCEPT/INTERSECT queries where you're dumping the contents into a
table via INTO, you need to specify all result table modifiers in the
*first* SQL statement where the INTO resides.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

So a second 'encrypted with' is ignored and is redundant?

JonD

On 2011-10-03 9:35 AM, Tim Young [Elevate Software] wrote:
> Jon,
>
> << creates an unencrypted table.  The encrypted with phrase seems to be
> ignored in the union case. It's probably something obvious >>
>
> For UNION/EXCEPT/INTERSECT queries where you're dumping the contents
> into a table via INTO, you need to specify all result table modifiers in
> the *first* SQL statement where the INTO resides.
>

Jon,

<< So a second 'encrypted with' is ignored and is redundant? >>

Correct.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks,

JonD

On 2011-10-11 2:19 PM, Tim Young [Elevate Software] wrote:
> Jon,
>
> << So a second 'encrypted with' is ignored and is redundant? >>
>
> Correct.
>