Doubt about WHERE clause

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 06 Jan 2009 4:05 pm

These days I was looking for information to facilitate my life, I have a select like below:
Code: Select all
SELECT
    *
FROM
    fornec
WHERE
    cd_fornec = 2312
AND dat_cadast = '12/12/08'
AND cnpj = '000000'
Use this select constantly, sometimes search for vendor code sometimes by registration date, anyway, whenever I use only a search, for example, have to delete the code AND s remaining ´.

Question is there any way to keep the select and use only one or two search criteria and keep the other options?

Example: Select all \"cd_fornec\" and \"cnpj\" for suppliers registered in 12/12/08.

Code: Select all
SELECT
    *
FROM
    fornec
WHERE
    cd_fornec = X
AND dat_cadast = '12/12/08'
AND cnpj = X
IE maintaining the search criteria \"cd_fornec\" and \"cnpj\".

I tried to use variables but found no way. I know I can use LIKE most like to something more practical.
marquesjr
Location: Fortaleza - CE

Poston Tue, 06 Jan 2009 4:42 pm

You can use variables.

Something like this:
Code: Select all
SELECT
    *
FROM
    fornec
WHERE
    (vfornec is null OR cd_fornec = vfornec)
AND (vcadast is null OR dat_cadast = to_date(vcadast,'dd/mm/yyyy')
AND (vcnpj   is null OR cnpj = vcnpj)
Thus, if the variables vfornec, vcadast and vcnpj are NULL, SQL will simply DISREGARD them.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 06 Jan 2009 5:22 pm

Show:-
marquesjr
Location: Fortaleza - CE


  • See also
    Replies
    Views
    Last Post


      Return to SQL

      Who is online

      Users browsing this forum: No registered users and 9 guests