Home » SQL & PL/SQL » SQL & PL/SQL » how to use select field value in where clause (merged)
how to use select field value in where clause (merged) [message #684019] Wed, 24 March 2021 10:35 Go to next message
Sekhar6617
Messages: 22
Registered: March 2021
Junior Member
Hi Team,

How to use the select field from another table into main sql where clause. Do we need to repeat the same sub-query wrote in select in where clause as well or do we have any logic to get the select value in where clause. See below sql, in that how to get the startdate in case statement of where clause.



SELECT a.invoiceid, 
       a.invoicenum, 
       a.invoicedate, 
       ( 
              SELECT Max(b.start_date) 
              FROM   invoice_ln_tbl b 
              WHERE  a.invoiceid = b.invoiceid) AS startdate, 
       c.taxrate 
FROM   invoice_tbl a, 
       invoicetax c 
WHERE  a.invoiceid = c.invoiceid 
AND 
       CASE 
              WHEN a.invoicedate IS NULL THEN a.invoicedate <= startdate
              WHEN a.invoicedate IS NOT NULL THEN a.invoicedate <= sysdate 
       END
how to use select field value in where clause [message #684020 is a reply to message #684019] Wed, 24 March 2021 10:45 Go to previous messageGo to next message
Sekhar6617
Messages: 22
Registered: March 2021
Junior Member
Hi Team,

I wrote the below sql as per my requirement but confusing that how to use the ‘startdate’ in main sql where clause. Request you please help me with the correct sql.



SELECT a.invoiceid, 
       a.invoicenum, 
       a.invoicedate, 
       ( 
              SELECT Max(b.start_date) 
              FROM   invoice_ln_tbl b 
              WHERE  a.invoiceid = b.invoiceid) AS startdate, 
       c.taxrate 
FROM   invoice_tbl a, 
       invoicetax c 
WHERE  a.invoiceid = c.invoiceid 
AND 
       CASE 
              WHEN a.invoicedate IS NULL THEN a.invoicedate <= startdate 
              WHEN a.invoicedate IS NOT NULL THEN a.invoicedate <= sysdate 
       END
Thank you.

Regards
Sekhar
Re: how to use select field value in where clause (merged) [message #684022 is a reply to message #684019] Wed, 24 March 2021 11:49 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Put the subquery in the FROM clause and NEVER use it in the SELECT clause until you'll be an expert in SQL.

Previous Topic: Generate SQL via SQL
Next Topic: 12.1.0.1
Goto Forum:
  


Current Time: Thu Mar 28 06:57:33 CDT 2024