January 5, 2017 at 1:14 am
I have a sql code like below in my stored procedure:
INSERT INTO PRIOR_DIST (_ID, COSTS_DST, IS_ACTIVE)
SELECT
@_id,
COSTS_DST,
'Y'
FROM PRIOR_DIST
WHERE _ID = @id_prev;
currently It fetches previous record and insert as new record.
I'd like to modify the insert code . I want to update COSTS_DST received from select query by multiplying with a constant value and and then insert.
@const1=4.000
@const2=6.000
COSTS_DST = (COSTS_DST*@const1)/@const2 // upto ten decimal
I'd like to insert this updated value .
I'm not sure what changes I need to do in my code.
January 5, 2017 at 1:17 am
INSERT INTO PRIOR_DIST (_ID, COSTS_DST, IS_ACTIVE)
SELECT
@_id,
COSTS_DST*@const1/@const2,
'Y'
FROM PRIOR_DIST
WHERE _ID = @id_prev;
January 5, 2017 at 1:21 am
DesNorton (1/5/2017)
INSERT INTO PRIOR_DIST (_ID, COSTS_DST, IS_ACTIVE)
SELECT
@_id,
COSTS_DST*@const1/@const2,
'Y'
FROM PRIOR_DIST
WHERE _ID = @id_prev;
Thanks for the code. I have understood this part.
Is it possible to round the result upto 10 decimal place for the insert. Is there any way ?
January 5, 2017 at 2:06 am
spectra (1/5/2017)
DesNorton (1/5/2017)
Is it possible to round the result up to 10 decimal place for the insert. Is there any way ?
What is the datatype of COSTS_DST?
If it is DECIMAL(28,10), the insert will automatically round the value for you.
You can also look at the built-in ROUND() function.
January 5, 2017 at 4:55 am
Thank you.
I wish to add one condition in my code i.e a zero value checking .
like this ...
if(@const1!=0)
INSERT INTO PRIOR_DIST (_ID, COSTS_DST, IS_ACTIVE)
SELECT
@_id,
COSTS_DST*@const1/@const2,
'Y'
FROM PRIOR_DIST
WHERE _ID = @id_prev;
else
INSERT INTO PRIOR_DIST (_ID, COSTS_DST, IS_ACTIVE)
SELECT
@_id,
COSTS_DST,
'Y'
FROM PRIOR_DIST
WHERE _ID = @id_prev;
can we re-code it in a better way....little smaller ? preferably in single block
Note: @const2 will always be non-zero.
January 5, 2017 at 5:39 am
spectra (1/5/2017)
Thank you.I wish to add one condition in my code i.e a zero value checking .
like this ...
if(@const1!=0)
INSERT INTO PRIOR_DIST (_ID, COSTS_DST, IS_ACTIVE)
SELECT
@_id,
COSTS_DST*@const1/@const2,
'Y'
FROM PRIOR_DIST
WHERE _ID = @id_prev;
else
INSERT INTO PRIOR_DIST (_ID, COSTS_DST, IS_ACTIVE)
SELECT
@_id,
COSTS_DST,
'Y'
FROM PRIOR_DIST
WHERE _ID = @id_prev;
can we re-code it in a better way....little smaller ? preferably in single block
Note: @const2 will always be non-zero.
INSERT INTO PRIOR_DIST (_ID, COSTS_DST, IS_ACTIVE)
SELECT
@_id,
ROUND(CASE WHEN @const1!=0 THEN COSTS_DST*@const1/@const2 ELSE COSTS_DST END, 10),
'Y'
FROM PRIOR_DIST
WHERE _ID = @id_prev;
January 5, 2017 at 5:48 am
thats an impressive code !
You are the SQL God.
Thanks for the quick post.
January 5, 2017 at 5:54 am
spectra (1/5/2017)
thats an impressive code !You are the SQL God.
Thanks for the quick post.
I am a relative noob, with sooo much to learn.
Just glad I could be of help.
January 5, 2017 at 6:21 am
I would like to change this code little more.
Can we write a select query there to fetch value replacing @const1 ?
something like this ..
INSERT INTO PRIOR_DIST (_ID, COSTS_DST, IS_ACTIVE)
SELECT
@_id,
ROUND(CASE WHEN @const1!=0 THEN COSTS_DST*(select const1 from TableA where _ID = @id_prev) /@const2 ELSE COSTS_DST END, 10),
'Y'
FROM PRIOR_DIST
WHERE _ID = @id_prev;
Is it valid ? I'm not sure about the syntax . Could you please correct me ?
January 5, 2017 at 6:30 am
INSERT INTO PRIOR_DIST (_ID, COSTS_DST, IS_ACTIVE)
SELECT
@_id,
ROUND(CASE WHEN ta.const1 IS NULL OR ta.const1 != 0 THEN pd.COSTS_DST * ta.const1 / @const2
ELSE pd.COSTS_DST END, 10),
'Y'
FROM PRIOR_DIST AS pd
LEFT JOIN TableA AS ta ON pd._ID = ta._ID -- left join here, just in case the lookup does not exist.
WHERE pd._ID = @id_prev;
January 5, 2017 at 6:48 am
DesNorton (1/5/2017)
INSERT INTO PRIOR_DIST (_ID, COSTS_DST, IS_ACTIVE)
SELECT
@_id,
ROUND(CASE WHEN ta.const1 IS NULL OR ta.const1 != 0 THEN pd.COSTS_DST * ta.const1 / @const2
ELSE pd.COSTS_DST END, 10),
'Y'
FROM PRIOR_DIST AS pd
LEFT JOIN TableA AS ta ON pd._ID = ta._ID -- left join here, just in case the lookup does not exist.
WHERE pd._ID = @id_prev;
what would be value of ta.const1 in this code if lookup does not exist ?
January 5, 2017 at 7:00 am
spectra (1/5/2017)
DesNorton (1/5/2017)
INSERT INTO PRIOR_DIST (_ID, COSTS_DST, IS_ACTIVE)
SELECT
@_id,
ROUND(CASE WHEN ta.const1 IS NULL OR ta.const1 != 0 THEN pd.COSTS_DST * ta.const1 / @const2
ELSE pd.COSTS_DST END, 10),
'Y'
FROM PRIOR_DIST AS pd
LEFT JOIN TableA AS ta ON pd._ID = ta._ID -- left join here, just in case the lookup does not exist.
WHERE pd._ID = @id_prev;
what would be value of ta.const1 in this code if lookup does not exist ?
Oops. It would be NULL.
It should read ...
ROUND(CASE WHEN ta.const1 IS NULL OR ta.const1 = 0 THEN pd.COSTS_DST -- Do not do calcs
ELSE pd.COSTS_DST * ta.const1 / @const2 END, 10),
January 5, 2017 at 7:06 am
This looks nice. Thanks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply