Home » Infrastructure » Windows » temporary table
| temporary table [message #681846] |
Fri, 04 September 2020 00:13  |
 |
kamuran.canakli
Messages: 7
Registered: September 2020
|
Junior Member |
|
|
Hi,
This query working on pl/sql developer tools. But I cant work in c# with OracleManagedDataAccess control. What can I do ?
create global temporary table IBRLotlar on commit preserve rows
AS
SELECT h.part_no, h.lot_batch_no, h.location_no, r.warehouse_id, h.quantity, h.qty_reversed,
h.direction, h.date_created,
case when h.direction = '+' then h.quantity else 0 end Giren,
case when h.direction = '-' then h.quantity else 0 end Cikan
FROM INVENTORY_TRANSACTION_HIST H, WAREHOUSE_BAY_BIN r
WHERE h.location_no = r.location_no
AND h.contract = r.contract
AND INVENTORY_PART_API.Get_Type_Code_Db(H.contract, H.part_no) IN
('1', '2')
AND H.quantity - H.qty_reversed > 0
AND r.warehouse_id = ('&AMBAR')
and inventory_part_api.Get_Part_Product_Family(h.contract, h.part_no) LIKE
NVL('&URUN_AILESI', '%')
AND r.location_no LIKE NVL('&LOKASYON', '%')
AND H.date_created BETWEEN to_date('&BASLANGIC_TARIH', 'DD.MM.YYYY') AND NVL(to_date('&BITIS_TARIH', 'DD.MM.YYYY'), SYSDATE)
order by 1,2;
|
|
|
|
|
|
|
|
| Re: temporary table [message #681864 is a reply to message #681847] |
Fri, 04 September 2020 11:13  |
|
kamuran.canakli
Messages: 7
Registered: September 2020
|
Junior Member |
|
|
Hi again,
I apologize for not explaining the problem.
query works without problem on pl/sql developer tools.
query dont works in c# I get Ora-933
string qry = string.Format(@"
create global temporary table IFSAPP.IBRLotlar on commit preserve rows
AS
SELECT h.part_no, h.lot_batch_no, h.location_no, r.warehouse_id, h.quantity, h.qty_reversed,
h.direction, h.date_created,
case when h.direction = '+' then h.quantity else 0 end Giren,
case when h.direction = '-' then h.quantity else 0 end Cikan
FROM IFSAPP.INVENTORY_TRANSACTION_HIST H, IFSAPP.WAREHOUSE_BAY_BIN r
WHERE h.location_no = r.location_no
AND h.contract = r.contract
AND IFSAPP.INVENTORY_PART_API.Get_Type_Code_Db(H.contract, H.part_no) IN ('1', '2')
AND H.quantity - H.qty_reversed > 0
AND r.warehouse_id = ('H')
and IFSAPP.inventory_part_api.Get_Part_Product_Family(h.contract, h.part_no) LIKE NVL('HORTM', '%')
AND r.location_no LIKE NVL('', '%')
AND H.date_created BETWEEN to_date('01.08.2020', 'DD.MM.YYYY') AND NVL(to_date('31.08.2020', 'DD.MM.YYYY'), SYSDATE);
select * from IFSAPP.IBRLotlar;
");
DataTable dtTemp = new DataTable();
if (LIB.et.ERPConn.State ConnectionState.Broken || LIB.et.ERPConn.State ConnectionState.Closed) LIB.et.ERPConn.Open();
OracleCommand cmd = new OracleCommand(qry, LIB.et.ERPConn);
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 300;
cmd.Parameters.Add(new OracleParameter("ambar", ambar.ToString()));
cmd.Parameters.Add(new OracleParameter("lokasyon", lokasyon.ToString()));
cmd.Parameters.Add(new OracleParameter("urunailesi", urunailesi.ToString()));
cmd.Parameters.Add(new OracleParameter("bastar", Convert.ToDateTime(bastar)));
cmd.Parameters.Add(new OracleParameter("bittar", Convert.ToDateTime(bittar)));
dtTemp.Load(cmd.ExecuteReader());
cmd.Dispose();
LIB.et.ERPConn.Close();
return dtTemp;
|
|
|
|
|
|
| Re: temporary table [message #681868 is a reply to message #681864] |
Fri, 04 September 2020 13:03 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
One more time. With emphasis. Please format your code and preserve that formattng by enclosing it in 'code' tags. Took me more time to write this sentence that it did you reformat your code for you.
CREATE global TEMPORARY TABLE ifsapp.ibrlotlar
ON COMMIT preserve ROWS AS
SELECT h.part_no,
h.lot_batch_no,
h.location_no,
r.warehouse_id,
h.quantity,
h.qty_reversed,
h.direction,
h.date_created,
CASE
WHEN h.direction = '+' THEN h.quantity
ELSE 0
END Giren,
CASE
WHEN h.direction = '-' THEN h.quantity
ELSE 0
END Cikan
FROM ifsapp.inventory_transaction_hist H,
ifsapp.warehouse_bay_bin r
WHERE h.location_no = r.location_no
AND h.contract = r.contract
AND ifsapp.inventory_part_api.Get_type_code_db(H.contract, H.part_no)
IN
( '1', '2' )
AND H.quantity - H.qty_reversed > 0
AND r.warehouse_id = ( 'H' )
AND ifsapp.inventory_part_api.Get_part_product_family(h.contract,
h.part_no)
LIKE
Nvl('HORTM', '%')
AND r.location_no LIKE Nvl('', '%')
AND H.date_created BETWEEN To_date('01.08.2020', 'DD.MM.YYYY') AND
Nvl(To_date('31.08.2020', 'DD.MM.YYYY'),
SYSDATE);
SELECT *
FROM ifsapp.ibrlotlar;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: temporary table [message #681883 is a reply to message #681874] |
Sat, 05 September 2020 08:33 |
|
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
kamuran.canakli wrote on Sat, 05 September 2020 00:40Hi,
I am not an expert on this subject. It has been 3 months since I started Oracle. We use IFS. One of our reports was giving results in 61 minutes. I tried to rewrite it with GTT and now it has decreased to 5 minutes. Perhaps the first report technique was different.
Indeed it was.
I suspect that what you really need, instead of a GTT, is technique known as 'sub-query factoring'. Using your information, something like this:
with temp_data as
(SELECT h.part_no,
h.lot_batch_no,
h.location_no,
r.warehouse_id,
h.quantity,
h.qty_reversed,
h.direction,
h.date_created,
CASE
WHEN h.direction = '+' THEN h.quantity
ELSE 0
END Giren,
CASE
WHEN h.direction = '-' THEN h.quantity
ELSE 0
END Cikan
FROM ifsapp.inventory_transaction_hist H,
ifsapp.warehouse_bay_bin r
WHERE h.location_no = r.location_no
AND h.contract = r.contract
AND ifsapp.inventory_part_api.Get_type_code_db(H.contract, H.part_no)
IN
( '1', '2' )
AND H.quantity - H.qty_reversed > 0
AND r.warehouse_id = ( 'H' )
AND ifsapp.inventory_part_api.Get_part_product_family(h.contract,
h.part_no)
LIKE
Nvl('HORTM', '%')
AND r.location_no LIKE Nvl('', '%')
AND H.date_created BETWEEN To_date('01.08.2020', 'DD.MM.YYYY') AND
Nvl(To_date('31.08.2020', 'DD.MM.YYYY'),
SYSDATE
)
SELECT *
FROM temp_data
;
One problem with your approach is that once you create the GTT for one particular session, you'd need to drop it when you are finished with that session. And that negates the entire purpose and advantage of GTTs.
One thing you need to keep in mind is that every database product (Oracle, MS Sql Server, MySQL, etc) are architecturally very different. They are not just the same thing with different names. In addition to fundamental differences in design and architecture, they each have their own variations in implementation of SQL, and they each have their own unique procedural languages (PL/SQL for Oracle, T-sql for MSSQL, etc). The bottom line of this is that standard/best practices in one can very well be worst practices in others - and the implementation of "temporary" tables is a textbook example of that. I like to say that the similarities begin AND END with 'select * from emp_table'. Do not assume that what you learned in one product is good in another.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Dec 21 19:03:06 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
