<beersh@[EMAIL PROTECTED]
> wrote in message
news:1109089174.956248.87500@[EMAIL PROTECTED]
>I need to duplicate rows in a table with unique index using sql.
> I try to run this statement:
>
> insert into
> MINUY_MADRICHIM(TAARICH_MINUY_ME,TAARICH_MINUY_AD,SUG_TEUDA,
> ZEHUT_MORE,TAARICH_HAFAKAT_MINUY,MAKOR_TAKZIV,SAL_HADRACHA,MINHAL,
> YECHIDA,TCHUM,ZEHUT_MEFAKEACH,STATUS_MINUY_HADRACHA,YOM_HADRACHA,
> SEVEV_HADRACHA,MOSAD_HADRACHA,SHAOT_HADRACHA,BASIS_MISRA,
> PITZUL_MISRA,TAA_IDKUN,SUG_MAKOR_MEDA,MAKOR_MEDA,MACHOZ_MEADKEN,
> MACHOZ_MAAVIR,TAARICH_HAAVARA_ME_MACHOZ,TAHALICH_MAAVIR)
> values ('1-sep-2004', '31-aug-2005',(select SUG_TEUDA,ZEHUT_MORE,
> TAARICH_HAFAKAT_MINUY,MAKOR_TAKZIV,SAL_HADRACHA,MINHAL,YECHIDA,
> TCHUM,ZEHUT_MEFAKEACH,STATUS_MINUY_HADRACHA,YOM_HADRACHA,
> SEVEV_HADRACHA,MOSAD_HADRACHA,SHAOT_HADRACHA,BASIS_MISRA,
> PITZUL_MISRA,TAA_IDKUN,SUG_MAKOR_MEDA ,MAKOR_MEDA,MACHOZ_MEADKEN,
> MACHOZ_MAAVIR,TAARICH_HAAVARA_ME_MACHOZ,TAHALICH_MAAVIR from
> MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
> TAARICH_MINUY_AD = '31-aug-2004'));
>
> And I get this error message:
>
> %SQL-F-SUBTOOVAL, Column select expression specifies too many columns.
>
> Did any one have an idea how can I fix this problem?
>
The problem is the nested SELECT statement: It does not return an atomic
list value
for the third argument of the INSERT statement's VALUES list.
The nested SELECT returns a ROW of values as the third argument,
when only one atomic value can be accepted.
Here's a possible - though ugly - fix:
SQL> insert into
MINUY_MADRICHIM
(TAARICH_MINUY_ME,
TAARICH_MINUY_AD,
SUG_TEUDA,
ZEHUT_MORE,
TAARICH_HAFAKAT_MINUY,
MAKOR_TAKZIV,
SAL_HADRACHA,
MINHAL,
YECHIDA,
TCHUM,
ZEHUT_MEFAKEACH,
STATUS_MINUY_HADRACHA,
YOM_HADRACHA,
SEVEV_HADRACHA,
MOSAD_HADRACHA,
SHAOT_HADRACHA,
BASIS_MISRA,
PITZUL_MISRA,
TAA_IDKUN,
SUG_MAKOR_MEDA,
MAKOR_MEDA,
MACHOZ_MEADKEN,
MACHOZ_MAAVIR,
TAARICH_HAAVARA_ME_MACHOZ,
TAHALICH_MAAVIR)
values
('1-sep-2004',
'31-aug-2005',
(select SUG_TEUDA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select ZEHUT_MORE from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select TAARICH_HAFAKAT_MINUY from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select MAKOR_TAKZIV from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select SAL_HADRACHA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select MINHAL from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select YECHIDA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select TCHUM from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select ZEHUT_MEFAKEACH from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select
STATUS_MINUY_HADRACHA
from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select YOM_HADRACHA
from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select SEVEV_HADRACHA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select MOSAD_HADRACHA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'),
(select SHAOT_HADRACHA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select BASIS_MISRA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select PITZUL_MISRA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select TAA_IDKUN from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select SUG_MAKOR_MEDA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select MAKOR_MEDA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select MACHOZ_MEADKEN from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select MACHOZ_MAAVIR from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select
TAARICH_HAAVARA_ME_MACHOZ
from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select TAHALICH_MAAVIR
from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row)
);
|
