Complex WHERE clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Complex WHERE clause

Author

Topic

Yakkity
Starting Member

5 Posts

Posted - 2011-04-26 : 10:40:39

Dear readers,

I'm having difficulties in creating a query for the following requirement:

- We have 4 tables: ARTICLE, ACCOUNT, ORDER and DELIVERY
- All 4 tables have their Primary keys
- The table DELIVERY has 3 foreign keys to the other 3 tables, but any of these can also be null. To ARTICLE this is ArticleNumber. To ACCOUNT this is AccountNumber and to Order this is OrderNumber.

Now I have created these tables, with their required keys, but the next part is inserting data into them which is coming from our ERP which is using a Pervasive SQL.

I'm doing these inserts with the help of a linked server, but the foreign keys are bugging me. Apparantly there is data in the ERP in the DELIVERY table which has a foreign key value ( articlenumber ) to the ARTICLE table which does not actually exist anymore in the ARTICLE table in the ERP. I know this is something that shouldn't happen, but I'm telling you ... this ERP is full of it and the data I'm exporting now will be for a new ERP.

So in short:
The record should be inserted when:
ArticleNumber = NULL OR ArticleNumber exists in ARTICLE table
AND
AccountNumber = NULL OR AccountNumber exists in ACCOUNT table
AND
OrderNumber = NULL or OrderNumber exists in ORDER table

At the moment I have the following:

INSERT INTO OT_BRILJANT_MERGE.dbo.BRIL_AFLEVERD
SELECT AFLEVERNR AS AfleverNR, AFLEVERBK AS AfleverBK, LIJNNR AS LineNumber, ''BCOM Vilvoorde'' AS
Briljant_Dossier, VERKREK AS AccountNumber, ORDERNR AS OrderNR, OrderBK AS OrderBK, ARTIKEL As
ArticleNumber, AANTAL As Aantal, KOSTPRIJS As Kostprijs, STDPRIJS As
StdPrijs, EENHPR AS EenhPrijs, KORTING1 AS Korting1, KORTING2 AS Korting2, TOTAAL AS Totaal,
MAGAZIJN AS Magazijn
FROM OPENQUERY (' + @MyLinkedServer + ', ''Select * from AFLEVERD'') ls
WHERE
( IF ls.ORDERBK LIKE '''' THEN 1
ELSE
EXISTS
(SELECT 1 FROM OT_BRILJANT_MERGE.dbo.BRIL_ORDER WHERE OrderNR = ls.ORDERNR
AND Briljant_Dossier = ''BCOM Vilvoorde''
AND OrderBK = ls.ORDERBK
)
END IF
)
AND
( IF ls.VERKREK LIKE '''' THEN 1
ELSE
EXISTS
(SELECT 1 FROM OT_BRILJANT_MERGE.dbo.BRIL_ACCOUNT WHERE AccountNumber = ls.VERKREK
)
END IF
)
AND
( IF ls.ARTIKEL LIKE '''' THEN 1
ELSE
EXISTS
(SELECT 1 FROM OT_BRILJANT_MERGE.dbo.ARTICLE WHERE ArticleNumber = ls.ARTIKEL
AND Briljant_Dossier = ''BCOM Vilvoorde''
)
END IF
)

and i'm getting the error:
Incorrect syntax near the keyword 'IF'.

I know i can tweak it a bit more, but I still don't think i'll be able to succesfully create the query and just keep getting the next error.

Any help is welcome, cause I'm kinda stuck on this.

Thanks in advance,
Yannick.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-04-26 : 21:03:53

It's a messy query, but your where clause should probably be written like so.

WHERE
(ls.ORDERBK = ''''
or
EXISTS
(SELECT 1 FROM OT_BRILJANT_MERGE.dbo.BRIL_ORDER WHERE OrderNR = ls.ORDERNR
AND Briljant_Dossier = ''BCOM Vilvoorde''
AND OrderBK = ls.ORDERBK )
)
...

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Yakkity
Starting Member

5 Posts

Posted - 2011-04-27 : 03:47:43

At the moment I have the following, but I still get the error:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_BRIL_AFLEVERD_ORDER". The conflict occurred in database "OT_BRILJANT_MERGE", table "dbo.BRIL_ORDER".

My code (my sql query is created as a string here so don't mind the extra quotes):

WHEN 'AFLEVERD' THEN
'INSERT INTO OT_BRILJANT_MERGE.dbo.BRIL_AFLEVERD
SELECT AFLEVERNR AS AfleverNR, AFLEVERBK AS AfleverBK, LIJNNR AS LineNumber, ''BCOM Vilvoorde'' AS
Briljant_Dossier, VERKREK AS AccountNumber, ORDERNR AS OrderNR, OrderBK AS OrderBK, ARTIKEL As
ArticleNumber, AANTAL As Aantal, KOSTPRIJS As Kostprijs, STDPRIJS As
StdPrijs, EENHPR AS EenhPrijs, KORTING1 AS Korting1, KORTING2 AS Korting2, TOTAAL AS Totaal,
MAGAZIJN AS Magazijn
FROM OPENQUERY (' + @MyLinkedServer + ', ''Select * from AFLEVERD'') ls
WHERE
( LTRIM(RTRIM(ls.ORDERBK)) LIKE ''''
OR
EXISTS
(SELECT 1 FROM OT_BRILJANT_MERGE.dbo.BRIL_ORDER WHERE OrderNR = ls.ORDERNR
AND Briljant_Dossier LIKE ''BCOM Vilvoorde''
AND OrderBK = ls.ORDERBK
)
)
AND
( LTRIM(RTRIM(ls.VERKREK)) LIKE ''''
OR
EXISTS
(SELECT 1 FROM OT_BRILJANT_MERGE.dbo.BRIL_ACCOUNT WHERE AccountNumber = ls.VERKREK
)
)
AND
( LTRIM(RTRIM(ls.ARTIKEL)) LIKE ''''
OR
EXISTS
(SELECT 1 FROM OT_BRILJANT_MERGE.dbo.ARTICLE WHERE ArticleNumber = ls.ARTIKEL
AND Briljant_Dossier LIKE ''BCOM Vilvoorde''
)
)
'

The PK of table ORDER:
ALTER TABLE BRIL_ORDER ADD CONSTRAINT PK_BRIL_ORDER PRIMARY KEY (OrderNR, OrderBK, Briljant_Dossier)

The FK of table AFLEVERD together with the other 2 FK's:
ALTER TABLE BRIL_AFLEVERD ADD CONSTRAINT FK_BRIL_AFLEVERD_ORDER FOREIGN KEY
(OrderNR, OrderBK, Briljant_Dossier)
REFERENCES BRIL_ORDER (OrderNR, OrderBK, Briljant_Dossier)

ALTER TABLE BRIL_AFLEVERD ADD CONSTRAINT FK_BRIL_AFLEVERD_ARTICLE FOREIGN KEY
(ArticleNumber, Briljant_Dossier)
REFERENCES ARTICLE (ArticleNumber, Briljant_Dossier)

ALTER TABLE BRIL_AFLEVERD ADD CONSTRAINT FK_BRIL_AFLEVERD_ACCOUNT FOREIGN KEY
(AccountNumber)
REFERENCES BRIL_ACCOUNT (AccountNumber)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources