How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask adamd-mod Your Own Question

adamd-mod
adamd-mod, Moderator
Category: Software
Satisfied Customers: 9
Experience:  Moderator
74353250
Type Your Software Question Here...
adamd-mod is online now

Sql programmer,i have some sql code that uses variables and

Customer Question

sql programmer,
i have some sql code that uses variables and a case statement and i want to put into a computed column but dont know how
Submitted: 8 months ago.
Category: Software
Expert:  Ingo U replied 8 months ago.
Hi, this sounds like you want a DML trigger. (I assume this is MS SQL, i.e. Transact-SQL?)This is basically a stored procedure you write to be run every time a table changes.Depending on your situation, it could also make sense to create a UDF that does your computation, and use that UDF directly wherever needed, instead of actually storing the computed value in a table column.If you provide more detail about your situation, I may be able to guide you better.
Expert:  Ingo U replied 8 months ago.
just following up, were you able to use my solution? Please feel free to post any additional questions, comments or concerns you may have regarding this issue.If you're not yet satisfied with the result, please let me know what is missing. I will do my best to make it right for you.Otherwise, please don't forget to rate my service - I don't get paid for my work until your rating is received.Regards,Ingo U
Customer: replied 8 months ago.

Hi, sorry i havnt been in contact sooner, i had issues replying with Just Answer they took 48 hours to fix it.

Anyway i had a look into how a DML trigger works and that figured out how to make it work.

In the same DML trigger i am trying to update column (PL1) with another calculation, however SQL doesnt like it and im not sure why? Could you please take a look.

Thank

Attachment: 2016-04-08_172625_sql_plupdate.xlsx

you

Expert:  Ingo U replied 8 months ago.
Well, this may be unrelated to any trigger-related issues...because just looking at your UPDATE statement, it doesn't make sense to update a single column (PL1)from a select statement that returns multiple values...plus, you need to have something that looks more likeUPDATE tableSET Col1 = i.Col1, Col2 = i.Col2FROM ( SELECT ID, Col1, Col2 FROM other_table) iWHERE i.ID = table.ID or, perhaps using an inner join to extract the right data for the right destination rows in your dbo.COA I'd develop,debug,test the update statement in a separate query window, i.e. not as part of the trigger.Once it's all working, modify your trigger to execute that code.
Customer: replied 8 months ago.

Ok sorry it doesn't make sense, this is why i am asking an expert because i don't know myself! You may need to bear with me..

Perhaps lets start again..

What i am ultimately trying to achieve in table 'COA' is to calculate a total sales volume for the year by account and then calculate a monthly value using a sales curve from the date table.

I have attached an example which i am hoping explains things more clearly.

Hoping you can provide some code to make it work.

If you need more info to assist please let me know.

Thank you

Attachment: 2016-04-09_103850_sql_plupdate_example.xlsx

Expert:  Ingo U replied 8 months ago.
OK, I think I'm starting to see the bigger picture and goal... still not quite sure how/why you're going about doing what you are with the CASE statement...Could you upload an actual export (or entire backup) of your database table(s) in question? Or, at least, a schema definition for the tables? In order to write you a SQL query I really would need to know what all the columns and relationships are.
Customer: replied 8 months ago.

Basically this is the only way i've managed to get it to work.

I've attached a simplified version focusing only on the Sales Volume, if this part can incorporate the sales curve by month then i can apply it elsewhere.

Overview: In the attached file

Tab 1 - Pivot table output

Tab 2 - Database table data (JA wouldn't allow me to upload the SQL database so i have include the data here)

1. 'COA' - holds account/product info

2. 'Date' - date plus associated date driven data for calcs eg. Sales Curve

3. 'Plann' - holds assumption data used in calculations.

Tab 3 - Trigger query

This is associated with the 'Plann' table - The purpose of the query is to auto update table 'COA' column 'PL' driven by assumptions in table 'Plann'. In my version of excel i have an addin that allows sql table data to be changed in an excel worksheet.

If you have a more efficient way of coding it would be very much appreciated.

If anything isn't clear please let me know.

Thank you.

Attachment: 2016-04-10_201525_pl_projection.xlsx

Expert:  Ingo U replied 7 months ago.
My apologies,I am out of ideas on this one at the moment.I am opting out and opening the question up to other professionals here who may be able to help.Regards,Ingo U
Expert:  adamd-mod replied 7 months ago.

Hello,

It seems the professional has left this conversation. This happens occasionally, and it's usually because the professional thinks that someone else might be a better match for your question. I've been working hard to find a new professional to assist you with your question, but sometimes finding the right professional can take a little longer than expected.

I wonder whether you're OK with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you.

Thanks for your patience,

Adam

Customer: replied 7 months ago.
Yes please continue to search.
Expert:  adamd-mod replied 7 months ago.

Hello,

Thanks for getting back to me.

We will continue to look for a Professional to assist you.

Thank you for your patience,

Adam

Expert:  adamd-mod replied 7 months ago.

Hello,

I apologize as we have not yet been able to find a Professional to assist you. Do you wish for me to continue to search for someone to assist you or would you like for us to close your question at this time?

Thank you for your patience,

Adam

Customer: replied 7 months ago.

Please persist for the time being.

thanks

Expert:  adamd-mod replied 7 months ago.

Hello,

Thanks for getting back to me.

We will continue to look for a Professional to assist you.

Thank you for your patience,

Adam

Expert:  adamd-mod replied 7 months ago.

Hello,

Please understand it is rare for us not to be able to find the right Professional to assist our customers. We can close this question and return your good faith deposit to the original funding source on request.

Please let me know how you wish to proceed and again I apologize for any inconvenience this may have caused.

I hope you will give JustAnswer a try again in the future,

Adam

Customer: replied 7 months ago.

Hi, the good faith deposit of £3 and the £28 has gone from my account, can you please reimburse both amounts and close the ticket.

thank you

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
< Previous | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
 
 
 

Meet The Experts:

 
 
 
  • Steve Herrod

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    160
    Familiar with a wide variety of software and experienced in user training/support
< Last | Next >
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    160
    Familiar with a wide variety of software and experienced in user training/support
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    114
    Certified Software expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    112
    Computer Software Support specialist for more that 10 years
  • http://ww2.justanswer.com/uploads/FS/fszcze/2012-6-18_181848_500test.64x64.jpg Frederick S's Avatar

    Frederick S

    Computer Specialist

    Satisfied Customers:

    91
    Computer technician; founder of a home PC repair company.
  • http://ww2.justanswer.com/uploads/WA/wa94sound/2012-6-6_202633_iStock000007010121Small.64x64.jpg Bernie's Avatar

    Bernie

    Software Support

    Satisfied Customers:

    52
    6+ years experience, Microsoft MCDST/CompTia A+ Certified
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Software Engineer

    Satisfied Customers:

    38
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/jnayes/2010-12-23_132454_japicbetter.jpg jnayes's Avatar

    jnayes

    Computer Support

    Satisfied Customers:

    37
    Network Administrator
 
 
 

Related Software Questions