By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,101 Members | 1,463 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,101 IT Pros & Developers. It's quick & easy.

Syntax Error

towsie
P: 28
Hi,

I would be very grateful if someone could have a look at the staement below and help me find the Syntax Error.
Expand|Select|Wrap|Line Numbers
  1. ("Select origin_of_sale as Origin, Category, Crude.F3 as Customer, sales_terms, autolift, load_point as lp, Crude.F11 as Vessel, Crude.F2 as CONTRACT_NO, VALUE_ONLY, invoice_unit, Ledger_unit, Contract_date, template_name, acct_to_group_conversion as conv, Crude.F5, Crude.F6 as quantity, Crude.F8 as tax_quantity, Crude.F1 as participator, Crude.F14 as invoice_no, Crude.F9 as sales_date, Crude.F10 as pay_date, Crude.F12 as header, Crude.F4 as shipping, Crude.F7 as price, Crude.F13 as export " & _
  2.  
  3. "From Crude inner join t_sales_template on Crude.F5 = t_sales_template.origin_of_sale " & _                                                   
  4.  
  5. "Where (If(Customer = 'Shell Uk Ltd' AND Participator = 'SHELL UK LIMITED' Then template_name = 'Crude - SUKO - ' & Crude.F5) " & _
  6. "Else " & _
  7. "(If(Customer = 'A/B Svenska Shell' OR 'SHELL U.K. EXPLORATION & PRODUCTION' OR 'Shell Canada Ltd' OR 'Shell Canada Products' OR 'Shell Chemicals (Uk) Ltd.' OR 'Shell Chemicals Europe BV' OR 'Shell Deutschland Oil GMBH' OR 'Shell Expro UK Sector 11 (EOEL)' OR 'Shell Expro UK Sector 11 (EOP)' OR 'Shell Gas Direct' OR 'Shell Gas Limited' OR 'Shell Nederland Raffinaderij B.V.' OR 'Shell Trading Canada' OR 'Shell Trading International Limited' OR 'Shell Trading US Company' OR 'Shell Western Supply and Trading Limited' OR 'Societe des Petroles Shell' OR (Customer = 'Shell UK Ltd' AND Participator NOT 'SHELL UK LIMITED') Then template_name = 'Crude - Group - ' & Crude.F5)) " & _
  8. "Else " & _
  9. "(template_name = 'Crude - Third Party - ' & Crude.F5);")
  10.  
Thanks

Towsie
Feb 19 '08 #1
Share this Question
Share on Google+
6 Replies


debasisdas
Expert 5K+
P: 8,127
What is that IF in the SQL syntax ?
Feb 19 '08 #2

QVeen72
Expert 100+
P: 1,445
Hi,

I dont think you can use If .. Then.. Else.. End IF in an SQL Statement like that. If you are using Access, you can use
IIF(EvaluateCondition, True_do_This, False_do_this)

Change your statement accordingly..

Regards
Veena
Feb 19 '08 #3

towsie
P: 28
Hi,

I have changes the statement to
Expand|Select|Wrap|Line Numbers
  1. ("Select origin_of_sale as Origin, Category, Crude.F3 as Customer, sales_terms, autolift, load_point as lp, Crude.F11 as Vessel, Crude.F2 as CONTRACT_NO, VALUE_ONLY, invoice_unit, Ledger_unit, Contract_date, template_name, acct_to_group_conversion as conv, Crude.F5, Crude.F6 as quantity, Crude.F8 as tax_quantity, Crude.F1 as participator, Crude.F14 as invoice_no, Crude.F9 as sales_date, Crude.F10 as pay_date, Crude.F12 as header, Crude.F4 as shipping, Crude.F7 as price, Crude.F13 as export " & _
  2.  
  3. "From Crude inner join t_sales_template on Crude.F5 = t_sales_template.origin_of_sale " & _
  4.  
  5.  "Where (IIf(Customer = 'Shell Uk Ltd' AND Participator = 'SHELL UK LIMITED'), (template_name = 'Crude - SUKO - ' & Crude.F5), (IIf(Customer = 'A/B Svenska Shell' OR 'SHELL U.K. EXPLORATION & PRODUCTION' OR 'Shell Canada Ltd' OR 'Shell Canada Products' OR 'Shell Chemicals (Uk) Ltd.' OR 'Shell Chemicals Europe BV' OR 'Shell Deutschland Oil GMBH' OR 'Shell Expro UK Sector 11 (EOEL)' OR 'Shell Expro UK Sector 11 (EOP)' OR 'Shell Gas Direct' OR 'Shell Gas Limited' OR 'Shell Nederland Raffinaderij B.V.' OR 'Shell Trading Canada' OR 'Shell Trading International Limited' OR 'Shell Trading US Company' OR 'Shell Western Supply and Trading Limited' OR 'Societe des Petroles Shell' OR (Customer = 'Shell UK Ltd' AND Participator NOT 'SHELL UK LIMITED'))), (template_name = 'Crude - Group - ' & Crude.F5), (template_name = 'Crude - Third Party - ' & Crude.F5);")
  6.  
There is still a syntax error.

Thanks

Towsie
Feb 19 '08 #4

debasisdas
Expert 5K+
P: 8,127
Try to use CASE instead of IF

That is supported in SQL.
Feb 19 '08 #5

debasisdas
Expert 5K+
P: 8,127
It is very difficult to suggest anything without your table structure.
Feb 19 '08 #6

QVeen72
Expert 100+
P: 1,445
Hi,

Look at OR :

Customer = 'A/B Svenska Shell' OR 'SHELL U.K. EXPLORATION'

It should be:

Customer = 'A/B Svenska Shell' OR Customer = 'SHELL U.K. EXPLORATION'

Such things are handled better in the Reports.. instead of writing such Complex SQL..

More over, IIF Condions has to be used for Field Names not in Where Conditions..


REgards
Veena
Feb 19 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.