Stored Procedure Help

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

Hi All,

Stored procedure help:

SQL

SELECT l._application_id_c
   , p.ID, l.datetime_c
FROM dbo.leads l
    LEFT OUTER JOIN dbo.Applications p ON l.application_id_c = p.ApplicationID
WHERE (datetime_c IS NOT NULL)..

This is my stored proc. I want to do this if Datetime is null then i would pick the datetime from another table Applications ...I tried this ...I know i have done blunder..Please bear it and correct me.

SQL

WHERE (datetime_c IS NOT NULL)OR([datetime_c] IS NULL Then  [datetime_c]=p.[ApplicationDate])

Posted 5-Jun-13 2:34am

vidkaat

Updated 5-Jun-13 2:37am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2013-06-05T02:40:00

Solution 1

You can use the COALESCE expression MSDN Coalesce[^]

eg

SQL

SELECT l._application_id_c, p.ID, COALESCE(l.datetime_c, p.ApplicationDate) as datetime_c
FROM dbo.leads l
    LEFT OUTER JOIN dbo.Applications p ON l.application_id_c = p.ApplicationID

[Edit - prompted by a comment from Prasad Khandekar I've given the full sql statement

Posted 5-Jun-13 2:40am

CHill60

Updated 5-Jun-13 3:48am

v2

Comments

Maciej Los 5-Jun-13 9:11am

+5

Prasad Khandekar 5-Jun-13 9:46am

Hello Vidkaat,

In addition to above answer you may need to remove the datetime condition from your where clause.

CHill60 5-Jun-13 9:49am

Good point - I've updated my solution. Thank you

vidkaat 5-Jun-13 10:12am

It doesn't work...Can you suggest me something

vidkaat 5-Jun-13 10:17am

I think you have misunderstood my question...I check if the datetime is null then i need to pick the value from applications table. When i tried the above it doesnot work

CHill60 5-Jun-13 10:29am

The code I supplied will look on table leads first - if the column datetime_c is null on that table it will then go to the Applications table and get the ApplicationDate column from there. When you say "It doesn't work..." what actually happens??

vidkaat 5-Jun-13 10:51am

Sir i when i changed my proc to what you said it is not picking that column from the application table and displaying them in my result

CHill60 5-Jun-13 11:00am

In which case the problem must either be with LEFT OUTER JOIN dbo.Applications p ON l.application_id_c = p.ApplicationID or the Application date for that Id is also null.
Try running the following sql

SELECT l._application_id_c, p.ID, l.datetime_c, p.* FROM dbo.leads l
    LEFT OUTER JOIN dbo.Applications p ON l.application_id_c = p.ApplicationID

and examine the results. If all of the p.* fields are NULL then your join isn't working

vidkaat 5-Jun-13 11:12am

Ya all the fields are NULL.

CHill60 5-Jun-13 11:16am

Then the way you are linking the leads table and the Applications table is incorrect. I can't help you further without knowing the table schemas or seeing some sample data

vidkaat 5-Jun-13 11:26am

In both the tables i have application_id column as common. I would select the datetime based on the application id only.Try if u could suggest me something. I really appreciate ur time.

CHill60 5-Jun-13 11:30am

Make sure in your join that you have used the correct column (and take care over case sensitivity). Then examine both tables by eye and try to find a row where the application ids match up. How are the columns defined on the database table?

vidkaat 5-Jun-13 12:03pm

Yes it does. SELECT application_id_c
, p.ID, COALESCE(l.[datetime_c],p.[ApplicationDate]) AS datetime_c
FROM dbo.leads l
INNER JOIN dbo.Applications p ON application_id_c = p.ApplicationID
Works fine for me but if i use where clause it fails i will have to use where clause because i do left outer join of other tables.

CHill60 6-Jun-13 4:23am

If you are doing left outer join on other tables then use the ON clause not the WHERE. The where clause in your original post will certainly stop the code from working

vidkaat 5-Jun-13 11:16am

i CHECKED AND ALL THE FIELDS ARE NULL..Do i need to do a different join? Please help