red1.org

[riche]

Can you give me a SQL command to do the following:
For all Bpartner where search key looks like '07%' and Bpartner_location.freighcostrule=A
set Bpartner_location.freighcostrule=P

I was basically asked to change the freightcost rule for all progressus (searchkey starts with 07) locations that are prepaid&add (A) to prepaid(P).

Thanks

[trifon]

Hi Rcih,

On my instance i ddin't found any recrds that satisfy this conditions.
Please execute this SELECT statement and see if you have records that satisfy this condition:

SELECT *
FROM C_BPartner_Location l
WHERE AD_Client_ID = 1000000
AND l.C_BPartner_ID IN (
SELECT C_BPartner_ID
FROM C_BPartner bp
WHERE bp.AD_Client_ID = 1000000
AND bp.Value LIKE '07%'
)
AND l.FreightCostRule = 'A'

THIS is UPDATE SQL:

UPDATE C_Bpartner_Location l
SET FreightCostRule = 'P'
WHERE AD_Client_ID = 1000000
AND l.C_BPartner_ID IN (
SELECT C_BPartner_ID
FROM C_BPartner bp
WHERE bp.AD_Client_ID = 1000000
AND bp.Value LIKE '07%'
)
AND l.FreightCostRule = 'A'

Regards,
Trifon
_________________
Trifon Trifonov

[riche]

I need an SQL to list product search key, product description, list price, standard price for a specific pricelist.

Thanks,
Rich

[trifon]

Hi Rich,

This is required SQL:

SELECT p.Value, p.Description, pp.PriceList, pp.PriceStd, pv.Name
FROM M_Product p
LEFT OUTER JOIN M_ProductPrice pp ON (p.M_Product_ID = pp.M_Product_ID)
LEFT OUTER JOIN M_PriceList_Version pv ON (pp.M_PriceList_Version_ID = pv.M_PriceList_Version_ID)
WHERE AD_Client_ID = 1000000
AND pv.Name = 'Standard 2003'

Please set proper value of AD_Client_ID if it differs from 1000000 on your instance.

Regards,
Trifon
_________________
Trifon Trifonov

[riche]

I need a SQL to do the following:

go thru I_BPartner
in each record in I_BPartner (for all imported records)
set bplocation.name=i_bpartner.name (set the location name = bpartner.name)

I need this because I accidentally set bplocation.name = "zip city,state".

I think Trifon gave me this SQL once, but I can't find it.

Thanks,
Rich

[riche]

Trifon I tried the following SQL:

UPDATE C_BPartner_Location loc
SET loc.Name =
(SELECT bp.Name
FROM C_BPartner bp
INNER JOIN C_BPartner_Location loc2 ON (bp.C_BPartner_ID = loc2.C_BPartner_ID)
INNER JOIN I_BPartner ibp ON (loc2.C_BPartner_Location_ID = ibp.C_BPartner_Location_ID)
WHERE loc2.C_BPartner_Location_ID = ibp.C_BPartner_Location_ID
AND loc2.C_BPartner_ID = bp.C_BPartner_ID
)
WHERE loc.AD_Client_ID = 1000000
AND loc.C_BPartner_Location_ID IN
(
SELECT loc2.C_BPartner_Location_ID
FROM C_BPartner_Location loc2
INNER JOIN I_BPartner ibp ON (loc2.C_BPartner_Location_ID = ibp.C_BPartner_Location_ID)
)

I got the following error:
"single row subquery returns more than one row"

Any ideas?

-Rich

[red1]

i ran the above script and it went thru returning 0 rows thus its syntax is correct.

But your error message indicates that it doesnt know which of the rows returned to use as there is more than one answer. Thus check your source data whether it is redundant or need to be more exact.

Perhaps a description of sources data can give and idea what is needed.

[trifon]

Hi Rich,

Could try this one:

UPDATE C_BPartner_Location loc
SET loc.Name = (
SELECT ibp.Name
FROM I_BPartner ibp
WHERE loc.C_BPartner_Location_ID = ibp.C_BPartner_Location_ID
AND loc.AD_Client_ID = ibp.AD_Client_ID
AND ibp.C_BPartner_Location_ID IS NOT NULL
)
WHERE loc.AD_Client_ID = 1000000
AND loc.C_BPartner_Location_ID IN (
SELECT C_BPartner_Location_ID
FROM I_BPartner ibp
WHERE loc.AD_Client_ID = ibp.AD_Client_ID
AND ibp.C_BPartner_Location_ID IS NOT NULL
)

Regards,
Trifon
_________________
Trifon Trifonov

[riche]

I need a SQL command to set bp_location.address1=Name, address2=address1, address3=2, address4=address 3. I know that I actually have to run these SQL commands in reverse. Also, can you disable the callout that autopopulates the search key and changes the name if it is not unique (assuming that these are not required)?

Thanks,
Rich

[red1]

It shuld take the shape of the following (replace *** with your AD_Client ID)

Update C_Location set Address4=Address3 where AD_Client= ***;
Update C_Location set Address3=Address2 where AD_Client= ***;
Update C_Location set Address2=Address1 where AD_Client= ***;
Update C_Location set Address1=Name where AD_Client= ***;

U can run each one at a time just to check first the contents effect. After running all u and if checking is ok, u can then commit the DB.

[riche]

[red1]

1) For name its