Problem with SQL query in Oracle [closed]

Question

Problem with SQL query in Oracle [closed]

Navigation

#1 by (2 votes)

0

I have a system which performs the query of a product in a store. That is, in my interface I select todas and then I enter the product code and I should throw all the stores with that product available. The query I modified it for that purpose and testing it in Toad works fine. That is, if I enter the code and todas brings me a product in all stores and if I put the store number, example 22 , it brings me a single product. But when I passed the query to Java, it does not work the same. What can I be doing wrong?

Then the query in Toad:

SELECT DISTINCT  * from   (select tmp_barra_primaria.codigoPrincipal,
                 tmp_barra_primaria.descripcionCorta,
                 --vi.active_date,
                 tmp_barra_primaria.descripcionLarga,
                 s.store,
                 im.dept,
                 vi.vat_rate,
                 rfr2.selling_retail,
                 TO_CHAR (rpile.detail_start_date, 'DD/MM/YYYY'),
                 TO_CHAR (rpile.detail_end_date, 'DD/MM/YYYY'),
                 rpile.promo_comp_id,
                 rpile.promo_id,
                 DECODE (uil.uda_value,
                         '0',
                         'Normal',
                         '4',
                         'Pesable',
                         '7',
                         'Procesado')
                    estado,
                 rfr2.simple_promo_retail,
                 NULL,
                 im.standard_uom,
                 MFG_REC_RETAIL
          FROM   item_master im,
                 vat_item vi,
                 store s,
                 uda_item_lov uil,
                 rpm_future_retail rfr2,
                          (SELECT    DECODE (im.item_number_type,
                                            'ITEM', im.item,
                                            im.item_parent)
                                       codigoPrincipal,
                                    im.item_desc descripcionLarga,
                                    im.short_desc descripcionCorta
                             FROM   item_master im
                            WHERE   im.item = :item)                     -- DISTINTO
                          tmp_barra_primaria
                       LEFT OUTER JOIN
                          (item_loc il)
                       ON (tmp_barra_primaria.codigoPrincipal = il.item
                           AND il.loc = DECODE(:2,'todas',il.loc))
                    LEFT OUTER JOIN
                       (rpm_future_retail rfr)
                    ON (tmp_barra_primaria.codigoPrincipal = rfr.item
                        AND rfr.location = DECODE(:2,'todas',rfr.location)
                        AND TO_DATE (SYSDATE, 'DD/MM/YYYY') <=
                              TO_DATE (rfr.action_date, 'DD/MM/YYYY')
                        AND (TO_DATE (SYSDATE) - TO_DATE (rfr.action_date)) >=
                              '0')
                 LEFT OUTER JOIN
                    (rpm_promo_item_loc_expl rpile)
                 ON (tmp_barra_primaria.codigoPrincipal = rpile.item
                     AND rpile.location = DECODE(:2,'todas',rpile.location)
                     AND TO_DATE (SYSDATE, 'DD/MM/YYYY') BETWEEN TO_DATE (
                                                                    rpile.detail_start_date,
                                                                    'DD/MM/YYYY'
                                                                 )
                                                             AND  TO_DATE (
                                                                     rpile.detail_end_date,
                                                                     'DD/MM/YYYY'
                                                                  ))
         WHERE       im.item = tmp_barra_primaria.codigoPrincipal
                 AND VI.VAT_TYPE IN ('R', 'B')
                 AND vi.active_date in (select max(active_date)
                                         from vat_item vi1 
                                         where vi.item = vi1.item
                                         and vi.vat_type = vi1.vat_type
                                         and vi1.create_date <= trunc(sysdate)
                                         and vi.vat_region = vi1.vat_region)
                 AND vi.item = im.item
                 AND s.store = DECODE(:2,'todas',s.store,:2)
                 AND s.vat_region = vi.vat_region
                 AND uil.item = im.item
                 AND uil.uda_id = '3'
                 AND rfr2.item = tmp_barra_primaria.codigoPrincipal
                 AND rfr2.location = DECODE(:2,'todas',rfr2.location,:2)
                 AND (TO_DATE (SYSDATE) - TO_DATE (rfr2.action_date)) >= '0'
ORDER BY s.store)

Here it works fine.

Now the query passed to Java:

String stringQuery = "SELECT DISTINCT * " + "FROM ( "
        + "SELECT tmp_barra_primaria.codigoPrincipal, "
        + "tmp_barra_primaria.descripcionCorta, "
        + "tmp_barra_primaria.descripcionLarga, " + "im.dept, "
        + "vi.vat_rate, " + "rfr2.selling_retail, "
        + "TO_CHAR(rpile.detail_start_date,'yyyymmdd'), " + "TO_CHAR(rpile.detail_end_date,'yyyymmdd'), "
        + "rpile.promo_comp_id, " + "rpile.promo_id, "
        + "DECODE(uil.uda_value, " + "'0', " + "'Normal', " + "'4', "
        + "'Pesable', " + "'7', " + "'Procesado') estado, "
        + "rfr2.simple_promo_retail,null,im.standard_uom,MFG_REC_RETAIL  " + "FROM item_master       im, "
        + "vat_item          vi, " + "store             s, "
        + "uda_item_lov      uil, " + "rpm_future_retail rfr2, " +

        "(SELECT decode(im.item_number_type, " + "'ITEM', "
        + "im.item, " + "im.item_parent) codigoPrincipal, "
        + "im.item_desc descripcionLarga, "
        + "im.short_desc descripcionCorta " + "FROM item_master im "
        + "WHERE im.item = ?1) tmp_barra_primaria " +

        "LEFT OUTER JOIN(item_loc il) "
        + "ON (tmp_barra_primaria.codigoPrincipal = il.item AND "
        + "il.loc = DECODE(?2,'todas',il.loc,?2)) " +
        "LEFT OUTER JOIN(rpm_future_retail rfr) "
        + "ON (tmp_barra_primaria.codigoPrincipal = rfr.item AND "
        + "rfr.location = DECODE(?2,'todas',rfr.location,?2) AND "
        + "TO_DATE(sysdate, 'DD/MM/YYYY') <= "
        + "TO_DATE(rfr.action_date, 'DD/MM/YYYY') AND "
        + "(to_date(sysdate) - to_date(rfr.action_date)) >= 0) "
        + "LEFT OUTER JOIN(rpm_promo_item_loc_expl rpile) "
        + "ON (tmp_barra_primaria.codigoPrincipal = rpile.item AND "
        + "rpile.location = DECODE(?2,'todas',rpile.location,?2) AND "
        + "TO_DATE(sysdate, 'DD/MM/YYYY') between "
        + "TO_DATE(rpile.detail_start_date, 'DD/MM/YYYY') AND "
        + "to_date(rpile.detail_end_date, 'DD/MM/YYYY')) " +

        "WHERE im.item = tmp_barra_primaria.codigoPrincipal "
        + "AND VI.VAT_TYPE IN ('R', 'B')"
        + "AND vi.item = im.item " + "AND s.store = DECODE(?2,'todas',s.store,?2) "
        + "AND s.vat_region = vi.vat_region "
        + "AND uil.item = im.item " + "AND uil.uda_id = 3 "
        + "AND rfr2.item = tmp_barra_primaria.codigoPrincipal "
        + "AND rfr2.location = DECODE(?2,'todas',rfr2.location,?2) " +

        "AND (to_date(SYSDATE) - to_date(rfr2.action_date)) >= 0 )";

It should be noted that it receives two parameters:

Query query = em.createNativeQuery(stringQuery);
query.setParameter(1, codigoBarra);
query.setParameter(2, sucursal);

... the bar code and the branch.

I hope you can guide me in knowing what I'm doing wrong.

sql java oracle jpa

asked by Jdeveloper 14.11.2016 в 17:21

source

1 answer

PHP href with conditional Convert an array to a .json file

score 2 · Answer 1

The simple answer is that the 2 queries are different.

The most obvious example is found in the% main WHERE . In the "Toad" version, you have the following condition:

 AND vi.active_date in (select max(active_date)
                         from vat_item vi1 
                         where vi.item = vi1.item
                         and vi.vat_type = vi1.vat_type
                         and vi1.create_date <= trunc(sysdate)
                         and vi.vat_region = vi1.vat_region)

This condition does not exist at all in the Java code.

Another, slightly less obvious example is that your DECODE uses are different. Taking an example in the "Toad" version:

DECODE(:2,'todas',il.loc)

and comparing with the Java version:

DECODE(?2,'todas',il.loc,?2)

As you can see, you pass an additional parameter in the Java version.

These are only 2 differences that I found at first glance, there may be others. Obviously, you can not expect the same results if your queries are not the same.