One input multiple queries

Question

I'm sure this is such a simple answer but I'm new to this and haven't been able to either ask the question of Google or anything in the right way.

I have a query, utilising the same input multiple times, and what I need to know is how can I get the input only once to be used several times?

ie the last line of the below is used at least five times and my current format asks it five times

Partial fix in place (Thank you Kaushik Nayak and your answer) now but it doesn't work correctly. I think the several join are where I have the issue, the initial select for the value of v.dt appears fine

FROM Bmtrread Rd 
INNER JOIN CUSTIMA.BSVCHRGE
ON Rd.CUST_REF = CUSTIMA.BSVCHRGE.U##CUST_REF
WHERE EXISTS
(SELECT *
FROM Bsvchrge Ch t cross join v
WHERE Rd.CUST_REF     = Ch.U##CUST_REF
AND Ch.U##TARIFF_CODE = 'TB12'
AND Ch.CHG_PERCENT    = 1
AND Rd.CURREAD_DT    >= v.dt

Is this statement run in SQL*Plus?

Jon Heller
– Jon Heller

2019-03-06 04:54:31 +00:00
Commented Mar 6, 2019 at 4:54 — Jon Heller
– Jon Heller, Commented Mar 6, 2019 at 4:54
No. Running it in Oracle SQL Developer

Vukoneti
– Vukoneti

2019-03-06 04:57:56 +00:00
Commented Mar 6, 2019 at 4:57 — Vukoneti
– Vukoneti, Commented Mar 6, 2019 at 4:57
Does this question solve your problem?

Jon Heller
– Jon Heller

2019-03-06 05:06:01 +00:00
Commented Mar 6, 2019 at 5:06 — Jon Heller
– Jon Heller, Commented Mar 6, 2019 at 5:06

Kaushik Nayak · Accepted Answer · 2019-03-06 06:13:16Z

0

You can use a single with clause to get the desired value and then use the column everywhere in the select or where clause

with v(dt)
AS
(
select to_date('&Start_Read_Date_DD_MM_YYYY','DD/MM/YYYY') from dual
)
select ..  --your select statement
..
FROM Bmtrread Rd 
INNER JOIN CUSTIMA.BSVCHRGE
ON Rd.CUST_REF = CUSTIMA.BSVCHRGE.U##CUST_REF
cross join v --add here
WHERE EXISTS
(SELECT *
FROM Bsvchrge Ch 
WHERE Rd.CUST_REF     = Ch.U##CUST_REF
AND Ch.U##TARIFF_CODE = 'TB12'
AND Ch.CHG_PERCENT    = 1
AND Rd.CURREAD_DT    >= v.dt --replace it with v.dt in all places.
)

edited Mar 6, 2019 at 6:13

answered Mar 6, 2019 at 5:01

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Vukoneti Over a year ago

I take it the "sometable" should be the table I am querying and the "t cross join v" adds the ability to use the parameter? (or am I confused lol)

Kaushik Nayak Over a year ago

@Vukoneti : yes t is just an alias I used for your table, that's all. It could be rd or ch or whatever you wish . See edits

Vukoneti Over a year ago

I think I'm more confused as there are existing joins, and I'm not even sure how to properly post the code here :)

Kaushik Nayak Over a year ago

@Vukoneti : There's no reason for confusion. simply put the cross join v after all your join conditions. Or else edit your question and add your complete query, at least starting from from clause

Kaushik Nayak Over a year ago

@Vukoneti : Please check my edits. You should include cross join in the main from so that it could be utilised within an external where clause as well as inside the exists( .. )

|

Collectives™ on Stack Overflow

One input multiple queries

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related