question

MitchellJennifer-5359 avatar image
0 Votes"
MitchellJennifer-5359 asked CarrinWu-MSFT edited

Modifying SQL query

I have no experience with SQL however I have a pre-exisiting query and I want to add a filter that states in-service as Y and dates between 21-Aug-21 through -11-Sep-21

See below query and screenshot


,t5 AS
(Select/+parallel(8)/ ACCT_ID,SA_ID, char_type_cd Deposit_Char,char_val Deposit_Char_Value, Quoted_Deposit_Amount,Deposit_on_Hand,START_DT Quote_Date,
(case when Deposit_on_Hand is null then Quoted_Deposit_Amount else Quoted_Deposit_Amount-Deposit_on_Hand end) Deposit_Amt_Unpaid
From (
SELECT /+parallel(8)/
SA.ACCT_ID,SA.SA_ID,sc.char_type_cd,sc.char_val, sa.tot_to_bill_amt Quoted_Deposit_Amount,sa.START_DT,
row_number() over(partition by SA.ACCT_ID order by sa.START_DT desc) Deposit_rnk,
(sum(ft.tot_amt)*(-1)) Deposit_on_Hand
from cisadm.ci_sa sa
left join cisadm.ci_ft ft on (sa.sa_id = ft.sa_id)
left join cisadm.ci_SA_Char sc on (sa.sa_id = sc.sa_id)
where SA.SA_TYPE_CD IN ('O-DEPRES', 'O-DEPCOM', 'O-EXCRDP')
AND SA.SA_STATUS_FLG IN ('20')
group by SA.ACCT_ID,SA.SA_ID,sc.char_type_cd,sc.char_val, sa.tot_to_bill_amt,sa.START_DT)
Where Deposit_Rnk=1
)

134997-image.png


sql-server-generalsql-server-transact-sqlsql-server-reporting-servicessql-server-analysis-services
image.png (25.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ErlandSommarskog commented

SELECT /+parallel(8)/

I know this query hint "parallel" from Oracle https://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#CHDJIGDG
And the screenshot is SqlPlus, also Oracle.
Right? Because this a forum for Microsoft SQL Server

Anyway, please post table design as DDL, some sample data as DML statement and the expected result.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, both oracle but unfortunately i don't know how to provide what your asking for

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog MitchellJennifer-5359 ·

DDL = CREATE TABLE statements for the tables.
DML = INSERT statements with sample data.

It's difficult to help you, if we don't understand your tables.

Anyway, you should obviously ask in an Oracle forum. We may give you a solution that works on SQL Server, but which does not run on Oracle.

0 Votes 0 ·