Run sql query inside a stored procedure - Database Discussion Boards

Re: SQL for dynamic mappings

CHill6022-May-17 4:39

CHill60

22-May-17 4:39

Cursor is a definite "No!" Laugh | :laugh:

(See my article Processing Loops in SQL Server[^] )

XML is a definite possibility, but the code I've shown using PIVOT is quite compact. There is an example of generating dynamic sql for a pivot in the article.

Re: SQL for dynamic mappings

Danpeking22-May-17 5:46

Danpeking

22-May-17 5:46

Wow, looks a great article. Thanks, will take a look at this and reply once I have.

Re: SQL for dynamic mappings

Danpeking24-May-17 23:28

Danpeking

24-May-17 23:28

I achieved this in the end by dynamically creating strings to pass in to a SQL statement I was building up. I used MAX and Case statements to achieve this. I finished it an EXEC (@MyString). Thanks for the advice!

Message Closed

18-May-17 20:29

Member 13210035

18-May-17 20:29

Message Closed

Re: oracle fusion procurement

Richard MacCutchan18-May-17 21:49

Richard MacCutchan

18-May-17 21:49

Please do your own research: oracle fusion - Google Search[^].

Re: oracle fusion procurement

CHill6018-May-17 22:24

CHill60

18-May-17 22:24

It's a spam set up

Re: oracle fusion procurement

Richard MacCutchan18-May-17 22:35

Richard MacCutchan

18-May-17 22:35

~~A rubbish question I agree, but not sure why it's a spam setup.~~
Mea culpa, just seen it.

Run sql query inside a stored procedure

Member 1320472716-May-17 9:29

Member 13204727

16-May-17 9:29

Hi..
Please help me in running below sql query by creating and calling a stored procedure.

Query:

$incidentQuery = "SELECT * FROM pki_incidents pi
LEFT JOIN pki_tickets pt ON pt.ticket_no = pi.hosp_ticket
WHERE pt.ticket_no = '" . $_REQUEST['hosp_ticket'] . "'";

Re: Run sql query inside a stored procedure

ZurdoDev16-May-17 10:13

ZurdoDev

16-May-17 10:13

What is your question?

There are two kinds of people in the world: those who can extrapolate from incomplete data.

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: Run sql query inside a stored procedure

Kornfeld Eliyahu Peter16-May-17 21:21

Kornfeld Eliyahu Peter

16-May-17 21:21

How to: Create a Stored Procedure (SQL Server Management Studio)[^]
Create a Stored Procedure | Microsoft Docs[^]
Sql Server - How To Write a Stored Procedure in SQL Server[^]
And an other 3 million answers using Google...

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

Re: Run sql query inside a stored procedure

Richard Deeming17-May-17 1:25

Richard Deeming

17-May-17 1:25

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

Mostafazs14-May-17 20:35

Mostafazs

14-May-17 20:35

Hello
I can't find solution for this simple query.Want to get sum of column of VISIT_FEE that have colum MONTH_ 2 and YEAR_ 1396

My main table is like this:

ID VISIT_DATE  VISIT_TIME  VISIT_FEE   IS_PAY
1   13960124    10:00           300000          1
6   13960208    10:50           2500000         1
7   13960208    11:00           210000          1
8   13960209    10:20           300000          1

and now i use below query:

SELECT
VISIT_FEE,
    EXTRACT(YEAR FROM VISIT_DATE) AS YEAR_,
    EXTRACT(MONTH FROM VISIT_DATE) AS MONTH_,
EXTRACT(DAY FROM VISIT_DATE) AS DAY_

FROM
    MZS_VISIT_REQUEST
WHERE
    IS_PAY = 1

and result is :

VISIT_FEE  YEAR_   MONTH_  DAY_
300000         1396 1   1
2500000        1396 2   2
210000         1396 2   2
300000         1396 2   2

And now i want some of colum VISIT_FEE that have YEAR_ 1396 and MONTH_ 2 and 1
somethings like below table:

FEE    YEAR_   MONTH_  DAY_
300000         1396 1   1
3010000        1396 2   2

Thanks.

modified 15-May-17 23:40pm.

Re: Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

Maciej Los14-May-17 21:18

Maciej Los

14-May-17 21:18

Please replace:

SQL

EXTRACT(MONTH FROM VISIT_DATE) AS DAY_

with:

SQL

EXTRACT(DAY FROM VISIT_DATE) AS DAY_

and everything should be OK.

For further details, please see: EXTRACT (datetime)

Re: Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

Mostafazs15-May-17 17:40

Mostafazs

15-May-17 17:40

Thanks for replay.
but this is just my misspelling and nothings changed on result. my show result table table is correct.
I want to get sum of column VISIT_FEE

Re: Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

Maciej Los15-May-17 20:03

Maciej Los

15-May-17 20:03

All you need to do is to use [SUM()](https://www.techonthenet.com/oracle/functions/sum.php) - one of [aggragate functions](https://docs.oracle.com/database/121/SQLRF/functions003.htm#SQLRF20035):

SELECT SUM(VISIT_FEE) AS FEE,
    EXTRACT(YEAR FROM VISIT_DATE) AS YEAR_,
    EXTRACT(MONTH FROM VISIT_DATE) AS MONTH_,
    EXTRACT(DAY FROM VISIT_DATE) AS DAY_
FROM MZS_VISIT_REQUEST
WHERE IS_PAY = 1
GROUP BY EXTRACT(YEAR FROM VISIT_DATE),
    EXTRACT(MONTH FROM VISIT_DATE) ,
    EXTRACT(DAY FROM VISIT_DATE)

Good luck

Re: Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

Mostafazs16-May-17 20:04

Mostafazs

16-May-17 20:04

Thanks.

But it's not what i want.
I want sum of each month like:

FEE YEAR_ MONTH_
300000 1396 1
3010000 1396 2

Sum of Fee column if MONTH_ is equal. like above table.

Re: Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

anarayane15-May-17 18:30

anarayane

15-May-17 18:30

SELECT
sum(VISIT_FEE) AS VISIT_FEE,
    EXTRACT(YEAR FROM VISIT_DATE) AS YEAR_,
    EXTRACT(MONTH FROM VISIT_DATE) AS MONTH_,

 
FROM
    MZS_VISIT_REQUEST
WHERE
    IS_PAY = 1
GROUP by
EXTRACT(YEAR FROM VISIT_DATE) AS YEAR_,
    EXTRACT(MONTH FROM VISIT_DATE) AS MONTH_

Please try this one

Re: Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

Mostafazs20-May-17 20:46

Mostafazs

20-May-17 20:46

Thanks.
But when running i get below error:

Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS AYEAR_,<br />
    EXTRACT(MONTH FROM VISIT_DATE) AS BMONTH_' at line 10

When i delete last part(ORDER BY), query without any error with below result
FEE YEAR_ MONTH_
3310000 1396 1

is this any things we can do like -->

<br />
SELECT<br />
        SUM(VISIT_FEE) AS FEE,<br />
    EXTRACT(YEAR FROM VISIT_DATE) AS YEAR_,<br />
    EXTRACT(MONTH FROM VISIT_DATE) AS MONTH_<br />
FROM<br />
    MZS_VISIT_REQUEST<br />
WHERE<br />
    IS_PAY = 1 AND DISTINCT MONTH_<br />

???

Access SSRS reports from outside network

bunty_bpr11-May-17 5:08

bunty_bpr

11-May-17 5:08

Hi Team

I am new to SSRS and exploring it for my organizational reporting requirements. After configuring dev instance, I am able to login into Report Manager and Report Server via IE using windows os users. But I am unable to understand below items in SSRS
1. Why logout button is not given in Report Server and how user will logout after access report.
2. If business users wants to access reports from outside(not in network) over internet and if I configured instance on public ip, will that work and how they will logout from session.
3.Do I need to create each business user in windows or users configured in Active Directory will work to access reports from outside over internet.
4.Is there any other way to maintain business users in different repository to avoid OS user and active directory.

Please guide me here.

Thanks

BuntyR

Re: Access SSRS reports from outside network

Simon_Whale11-May-17 5:19

Simon_Whale

11-May-17 5:19

Have a read of this

How do I provide access to SSRS Reports to external users where these SSRS Reports have SSAS as the data source.[^]

There is a link in one of the threads that discusses how to configure SSRS to be used externally

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

Re: Access SSRS reports from outside network

Member 1491271312-Aug-20 1:40

Member 14912713

12-Aug-20 1:40

Hi have you found a solution for this issue?

How to read & write data in queue in sql server

Tridip Bhattacharjee3-May-17 22:51

Tridip Bhattacharjee

3-May-17 22:51

anyone can guide me that how to read & write data in queue in sql server.

do i need to create queue first like table ?
queue is persistent object in db like table ?

please post a small example for read & write data in queue in sql server.

tbhattacharjee

Re: How to read & write data in queue in sql server

Victor Nijegorodov4-May-17 0:47

Victor Nijegorodov

4-May-17 0:47

Did you try to google for the queue examples?
Like this one: Building High Performance Queue in Database for storing Orders, Notifications, Tasks
Or some others from Google

Re: How to read & write data in queue in sql server

CHill604-May-17 2:42

CHill60

4-May-17 2:42

The member is question seems to have a pathological aversion to using Google for themselves. Sigh | :sigh:

Problem with installing oracle database

VISWESWARAN19982-May-17 0:13

VISWESWARAN1998

2-May-17 0:13

Good Morning Sir,
I am willing to install oracle database on my computer. So I have downloaded the database from here Oracle Database Software Downloads | Oracle Technology Network | Oracle[^] I found 12c is the latest database and I have downloaded it. Mine is a Windows 10 64 bit Operating System based on x-64 processor so the database will work in my computer since Microsoft Windows x64 (64-bit) is only present.

I created the oracle account and started installing the database, there were few steps prompted to me before installing the database, I've chosen the default settings and moved on. But I got this error,

Failed to add VISWESWARAN\Visweswaran install user to %2% group

I have searched the internet for this error and referred this Configuring Users, Groups and Environments for Oracle Database[^] thread but I found nothing.

Sir, could you please help me
Thank you for your time sir.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.