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 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy