Outer join in ms sql

Previous Member

Hi

I´m trying to outer join tables(left join) in a datareader. I´m an Oracle person so I´m used to doing it in the WHERE clause and that is not an issue in BOARD, but I´m working with an MS SQL database and I don´t seem to be able to change the INNER JOIN to a LEFT JOIN. 

 

Does anyone know how to do that?

 

Kind regards,

Throstur Thor

Previous Member

Could you post the statement? Oracle syntax will be fine.

Previous Member

Hi Ray,

 

What I want to do is change the INNER in this statement to a LEFT

 

 SELECT
...
FROM dbo.[Vidd-ClaimTree] INNER JOIN dbo.RemainingAmountFactoringDueDate
ON dbo.[Vidd-ClaimTree].Claim = dbo.RemainingAmountFactoringDueDate.Claim ;

 

like this

 

SELECT
...
FROM dbo.[Vidd-ClaimTree] LEFT JOIN dbo.RemainingAmountFactoringDueDate
ON dbo.[Vidd-ClaimTree].Claim = dbo.RemainingAmountFactoringDueDate.Claim ;

 

 

The full statement is here

 

SELECT
dbo.[Vidd-ClaimTree].Product
,dbo.[Vidd-ClaimTree].Product
, dbo.[Vidd-ClaimTree].Status
, dbo.[Vidd-ClaimTree].Status
, Cast(dbo.[Vidd-ClaimTree].Claim as VARCHAR)
, Cast(dbo.[Vidd-ClaimTree].Claim as VARCHAR)
, Cast(dbo.[Vidd-ClaimTree].Application as VARCHAR)
, Cast(dbo.[Vidd-ClaimTree].Application as VARCHAR)
, left(Cast(
CASE
WHEN ISNULL(cast(GETDATE()-dbo.remainingAmountFactoringDueDate.FactoringDueDate as int)-1,-9999) Between -9998 AND 0 THEN 0
WHEN ISNULL(cast(GETDATE()-dbo.remainingAmountFactoringDueDate.FactoringDueDate as int)-1,-9999) > 180 THEN 181
ELSE ISNULL(cast(GETDATE()-dbo.remainingAmountFactoringDueDate.FactoringDueDate as int)-1,-9999) end as VARCHAR), 10)
, Left(Cast(
CASE
WHEN ISNULL(cast(GETDATE()-dbo.remainingAmountFactoringDueDate.FactoringDueDate as int)-1,-9999) Between -9998 AND 0 THEN 0
WHEN ISNULL(cast(GETDATE()-dbo.remainingAmountFactoringDueDate.FactoringDueDate as int)-1,-9999) > 180 THEN 181
ELSE ISNULL(cast(GETDATE()-dbo.remainingAmountFactoringDueDate.FactoringDueDate as int)-1,-9999) end as VARCHAR), 10)
FROM dbo.[Vidd-ClaimTree] INNER JOIN dbo.RemainingAmountFactoringDueDate
ON dbo.[Vidd-ClaimTree].Claim = dbo.RemainingAmountFactoringDueDate.Claim ;

 

 

Kind regards,

Throstur Thor

Previous Member

If I understand correctly, the statement is generated in in Auto-Mode. Syntaxwise it should be enough to change the INNER to LEFT.

Have you tried switching from auto-mode to manual mode, copying the query and then making the modifications needed?

Previous Member

Thank you,

that is what I assumed but I might be doing it wrong. I can insert the manual query like you have done and I can browse the data and it´s showing me the correct output but when I try to save the data reader I get the following error:

 

You have a manual query please update it to reflect the new changes made here:

 

The only changes I´ve done are the changes I want to make to the manual query so I´m not sure what I should do?

 

 

Cheers,

Throstur Thor

Previous Member

AFAIK it is just an information, not an error. To my understanding, it should be enough to click ok and run the SQL reader.

Previous Member

Thank you very much Ray

 

I had done it correctly originally but I thought the message was an error so I was trying to find another way (other than manual) to do it. 

 

Kind regards,

Throstur Thor