Author |
Topic |
chulz90
Starting Member
26 Posts |
Posted - 2013-05-17 : 04:49:51
|
hai guys,i've some trouble to execute this query. i want to insert from two tablei'm using PL/SQLcan you help me to correct my queryinsert into rspi_mutation(MT_SEC,MT_ITEM_ID,MT_TAGNUM,MT_SERIAL_NO,MT_ITEM_STATUS,MT_STOREROOM,MT_DOC_ID,MT_DOC_STATUS,MT_STATUS_DATE,MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)select max(mt_sec)+1 from rspi_mutation,SELECT dn_itemnum,dn_tagnum,dn_serialnumber,eq_status,STORE_ROOM,dn_header_id,dn_status,dn_status_date,dn_from_locationid,dn_to_locationid,dn_qtyFROM V_INS_SCARDwhere dn_header_id='46'note:-in rspi_mutation table contained data's-mt_sec is a primary key in rspi_mutation table-dn_header_id ='46' contain two data'sthank's for helping sorry for my bad english |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-17 : 04:53:57
|
--Try thisinsert into rspi_mutation(MT_SEC,MT_ITEM_ID,MT_TAGNUM,MT_SERIAL_NO,MT_ITEM_STATUS,MT_STOREROOM,MT_DOC_ID,MT_DOC_STATUS,MT_STATUS_DATE,MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)SELECT (select max(mt_sec)+1 from rspi_mutation), dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qtyFROM V_INS_SCARDwhere dn_header_id='46'--Chandu |
|
|
chulz90
Starting Member
26 Posts |
Posted - 2013-05-17 : 05:05:25
|
quote: Originally posted by bandi --Try thisinsert into rspi_mutation(MT_SEC,MT_ITEM_ID,MT_TAGNUM,MT_SERIAL_NO,MT_ITEM_STATUS,MT_STOREROOM,MT_DOC_ID,MT_DOC_STATUS,MT_STATUS_DATE,MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)SELECT (select max(mt_sec)+1 from rspi_mutation), dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qtyFROM V_INS_SCARDwhere dn_header_id='46'--Chandu
thank you so much bandi. u're always helping me.. but mt_sec result have a same value???how to make it different |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-17 : 06:20:22
|
it should be thisinsert into rspi_mutation(MT_SEC,MT_ITEM_ID,MT_TAGNUM,MT_SERIAL_NO,MT_ITEM_STATUS,MT_STOREROOM,MT_DOC_ID,MT_DOC_STATUS,MT_STATUS_DATE,MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)SELECT (select max(mt_sec) from rspi_mutation) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)),dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qtyFROM V_INS_SCARDwhere dn_header_id='46' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-17 : 06:22:57
|
quote: Originally posted by chulz90
quote: Originally posted by bandi --Try thisinsert into rspi_mutation(MT_SEC,MT_ITEM_ID,MT_TAGNUM,MT_SERIAL_NO,MT_ITEM_STATUS,MT_STOREROOM,MT_DOC_ID,MT_DOC_STATUS,MT_STATUS_DATE,MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)SELECT (select max(mt_sec)+1 from rspi_mutation), dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qtyFROM V_INS_SCARDwhere dn_header_id='46'--Chandu
thank you so much bandi. u're always helping me.. but mt_sec result have a same value???how to make it different
Welcome... Visakh already provided solution--Chandu |
|
|
chulz90
Starting Member
26 Posts |
Posted - 2013-05-17 : 07:59:18
|
quote: Originally posted by visakh16 it should be thisinsert into rspi_mutation(MT_SEC,MT_ITEM_ID,MT_TAGNUM,MT_SERIAL_NO,MT_ITEM_STATUS,MT_STOREROOM,MT_DOC_ID,MT_DOC_STATUS,MT_STATUS_DATE,MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)SELECT (select max(mt_sec) from rspi_mutation) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)),dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qtyFROM V_INS_SCARDwhere dn_header_id='46' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
it show an error messageORA-00923: FROM keywordnot found where expectedwhat should i do ??thanks before FIXEDROW_NUMBER() OVER (ORDER BY (select max(mt_sec) from rspi_mutation)descthanks visakh for helping |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-17 : 08:05:15
|
quote: Originally posted by chulz90
quote: Originally posted by visakh16 it should be thisinsert into rspi_mutation(MT_SEC,MT_ITEM_ID,MT_TAGNUM,MT_SERIAL_NO,MT_ITEM_STATUS,MT_STOREROOM,MT_DOC_ID,MT_DOC_STATUS,MT_STATUS_DATE,MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)SELECT (select max(mt_sec) from rspi_mutation) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)),dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qtyFROM V_INS_SCARDwhere dn_header_id='46' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
it show an error messageORA-00923: FROM keywordnot found where expectedwhat should i do ??thanks before
Post in a Oracle forum This is MS SQL Server forum and we dont have much expertise on OracleFor Oracle specific help try your luck @ Oracle forums like www.orafaq.com------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-17 : 08:15:34
|
I think you can use ROWNUM pseudo column in Oracle--Check thisinsert into rspi_mutation(MT_SEC,MT_ITEM_ID,MT_TAGNUM,MT_SERIAL_NO,MT_ITEM_STATUS,MT_STOREROOM,MT_DOC_ID,MT_DOC_STATUS,MT_STATUS_DATE,MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)SELECT (select max(mt_sec) from rspi_mutation) + ROWNUM,dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qtyFROM V_INS_SCARDwhere dn_header_id='46'NOTE: You can get quick response by posting in relevant forums (dbforums.com for Oracle)--Chandu |
|
|
chulz90
Starting Member
26 Posts |
Posted - 2013-05-17 : 08:22:21
|
sorry visakh, i don't know this is ms sql forum.. but it's fixed.thanks for helping all |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-17 : 08:27:57
|
quote: Originally posted by chulz90 sorry visakh, i don't know this is ms sql forum.. but it's fixed.thanks for helping all
WelcomeI think you can do that by using pseudo column ROWNUM in Oracle...--Chandu |
|
|
|
|
|