SQL query...

xirokx · 24 Oct 2008 at 15:49

SELECT uniprod.orgmstee.org_lvl_number, uniprod.orgmstee.org_name_full, uniprod.prdmstee.prd_lvl_number, uniprod.prdmstee.prd_name_full, TRUNC(SYSDATE-1), uniprod.invbalee.on_hand_qty
FROM uniprod.invbalee invbalee, uniprod.orgmstee orgmstee, uniprod.prdmstee prdmstee
WHERE invbalee.ORG_LVL_CHILD = orgmstee.ORG_LVL_CHILD AND invbalee.PRD_LVL_CHILD = prdmstee.PRD_LVL_CHILD AND ((prdmstee.PRD_LVL_NUMBER=204133))

Hi I have the above code in SQL which seeks data when I run a report trouble is I can only search 1 product at a time I want to lookup more, so 204133 is the product what can I type like a back slash or comma so that I can enter more then 1 product..

Please help

Greenlizard0 · 24 Oct 2008 at 15:53

I don't quite understand your question, but what about AND?

Andre · 24 Oct 2008 at 15:58

Replace

Code:

AND ((prdmstee.PRD_LVL_NUMBER[COLOR=Red]=204133[/COLOR]))

with

Code:

AND ((prdmstee.PRD_LVL_NUMBER[COLOR=Red] IN (204133,204144,204145) [/COLOR])

if you are constructing the code on the fly - slightly more involved if you are using stored procedures

shine · 24 Oct 2008 at 15:59

Use the in command:

Code:

prdmstee.PRD_LVL_NUMBER in (204133,204134)

xirokx · 24 Oct 2008 at 16:20

thanks for your reply..

However I keep receiving "could not add the table uniprod.invbalee" error message...

Please help

thanks

Andre · 24 Oct 2008 at 19:11

Here we go:

Code:

SELECT orgmstee.org_lvl_number, 
    orgmstee.org_name_full, 
    prdmstee.prd_lvl_number, 
    prdmstee.prd_name_full, 
    TRUNC(SYSDATE-1), 
    invbalee.on_hand_qty
FROM uniprod.invbalee AS invbalee
INNER JOIN uniprod.orgmstee AS orgmstee ON invbalee.ORG_LVL_CHILD = orgmstee.ORG_LVL_CHILD
INNER JOIN uniprod.prdmstee AS prdmstee ON invbalee.PRD_LVL_CHILD = prdmstee.PRD_LVL_CHILD 
WHERE prdmstee.PRD_LVL_NUMBER IN (?x, ?y, ?z, ?a)

(re-written to ANSI conformance so I can see what you are trying to do with the SELECT etc.)

Your problem was using the fully qualified names in the SELECT section (uniprod.invbalee) when you have given them an alias (invbalee)
Once you have given them an alias, you are unable to use the fully qualified name

xirokx · 24 Oct 2008 at 21:55

wow

thanks for that

I will try it on Monday at work..

Do you actually know what that code means? I hope it works that will potentially save me an hour a day..

I will let you know how I get on

Cheers..

Andre · 25 Oct 2008 at 05:04

xirokx said:
thanks for that

You're Welcome

xirokx said:
Do you actually know what that code means?

Of Course! - it is a bit clearer than the one you initially gave, as it explicitly states how everything is joined together (uses INNER JOIN which will give you only those in all three tables - let me know if the relationship is different)
It is always worth writing in ANSI-SQL as it makes life easier to see where mistakes are

xirokx said:
I will let you know how I get on

Cheers..

Good Luck

André

xirokx · 25 Oct 2008 at 08:42

I know my initial post was pants it was because it was sent from work and I rarely have anytime to mess around online etc..

Basically when we want to pull a report we have different "excels" we go into which I think are linked to different applications..

In this particular case, this report tells us how many stock there is in stores , so for example - store 1 = 200units, store 2 = 400units so on and so forth were 205433 is the item code...

However since I started doing this job a few ago if we ever wanted to find out the store stock holding for more then 1 item code I was told we couldnt, I was always of the thinking "thats nonsense - its probably the code"..

I can't wait to go and try the code you have given to see if that will work....Also the way we run the report is a bit strange, so we open excel , right click on a cell and then edit query , here a SQL screen appears, we press the SQL button from the toolbar and then change the product number...If your code does work, then where would I paste it in so that everytime I run the report its there instead of the code which sits there at the moment thus saving me more time to paste in your code??

Thanks for all your help

Andre · 25 Oct 2008 at 17:27

Hmm - not sure without actually seeing, but it should still work... sounds like the excel spreadsheets are linked to a back-end database, thus allowing you to do SQL queries (not sure as not great at Excel - can do the basics, but the more complex stuff I normally move to a database)

Was trying to think of a way you could provide a list of product ids from cells - could try replacing the product code list (x,y,z) with $a1:$a30 where a1:a30 is the list of products you need.

All sounds a bit of a fudge (apologies if you did it) by someone who started with simple spreadsheets and has slowly made them into a complex database.
Other way would be to define a pivot table with a filter on it, so you can select the product codes you want to show???maybe

fez · 25 Oct 2008 at 22:49

Deffo learn to alias tables, it makes life so much easier when you are using lots of fields from a table. Also makes reading the SQL much easier in my oppinion; much cleaner.

SimonCHere · 27 Oct 2008 at 09:27

xirokx said:
I can't wait to go and try the code you have given to see if that will work....Also the way we run the report is a bit strange, so we open excel , right click on a cell and then edit query , here a SQL screen appears, we press the SQL button from the toolbar and then change the product number...If your code does work, then where would I paste it in so that everytime I run the report its there instead of the code which sits there at the moment thus saving me more time to paste in your code??

Thanks for all your help

Assuming that when you currently go into the SQL screen in excel, it still shows the last product code you searched for, ie. it saves the query, then pasting the whole of the above code into the SQL window should save it for next time.
Then all you have to do it replace the product codes in the in() statement for the ones you want to display.

Remeber to comma separate the codes. in(1,2,3)

Simon

xirokx · 27 Oct 2008 at 18:46

Hey Simon et al,

Am gutted, tried it this morning by pasting the above code and still failed

error msg = "could not add the table uniprod.invbalee"

is there anyway I am going to resolve this, remember I have no SQL experience or knowledge and am not the creator of this sheet..

Assuming that when you currently go into the SQL screen in excel, it still shows the last product code you searched for, ie. it saves the query,

- this is precisely what I do on each occassion..but 1 code at time and its fine, there has to be a way of adding multiple codes?

thanks

SimonCHere · 28 Oct 2008 at 09:10

xirokx said:
- this is precisely what I do on each occassion..but 1 code at time and its fine, there has to be a way of adding multiple codes?

thanks

There is and it has been posted in this thread.
It's the error message:
error msg = "could not add the table uniprod.invbalee"
that concerns me.

It's saying either the table doesn't exist or you don't have access rights to it.
Now because the first query that you posted works, you should have the access. :confused:

Try copying this into the SQL pane.

Code:

SELECT uniprod.orgmstee.org_lvl_number, uniprod.orgmstee.org_name_full, uniprod.prdmstee.prd_lvl_number, uniprod.prdmstee.prd_name_full, TRUNC(SYSDATE-1), uniprod.invbalee.on_hand_qty
FROM uniprod.invbalee invbalee, uniprod.orgmstee orgmstee, uniprod.prdmstee prdmstee
WHERE invbalee.ORG_LVL_CHILD = orgmstee.ORG_LVL_CHILD AND invbalee.PRD_LVL_CHILD = prdmstee.PRD_LVL_CHILD AND ((prdmstee.PRD_LVL_NUMBER IN (204133,204144,204145)))

xirokx · 28 Oct 2008 at 18:54

ok will try it first thing tomorrow and report back..

its so frustating especially as I do not know any SQL but its a privilege to have your guidance and support...

Its even more frustating because these things are passed down in the company and since we have had many changes nobody knows who the original creator was but more importantly how to change this.....but I would like to as it would save me so much time in the day.

thanks for your continued support and assistance..

xirokx · 29 Oct 2008 at 11:26

wow it worked.

you rock

xirokx · 21 Nov 2008 at 20:48

hey guys, particularly simon...

I have a new requirement..

Simon's code working perfectly however before when pressed you clicked on the SQL button in excel SQL panel popped up with the code inside it but before that we also use to get a prompt in which you could type the item codes instead and excel would display the result....can anyone see any error in simons code to stop excel or sql from doing this?

please help

thanks

SimonCHere · 23 Nov 2008 at 10:59

Code:

SELECT uniprod.orgmstee.org_lvl_number, uniprod.orgmstee.org_name_full, uniprod.prdmstee.prd_lvl_number, uniprod.prdmstee.prd_name_full, TRUNC(SYSDATE-1), uniprod.invbalee.on_hand_qty
FROM uniprod.invbalee invbalee, uniprod.orgmstee orgmstee, uniprod.prdmstee prdmstee
WHERE invbalee.ORG_LVL_CHILD = orgmstee.ORG_LVL_CHILD AND invbalee.PRD_LVL_CHILD = prdmstee.PRD_LVL_CHILD AND ((prdmstee.PRD_LVL_NUMBER IN (?x, ?y, ?z, ?a)))

as Andre mentioned earlier.

xirokx · 23 Nov 2008 at 14:45

hey simon,

is this actually copied into the code? just wanted to clarify..

(?x, ?y, ?z, ?a)))

and by doing this will it bring up a pop up box in which I can enter the item codes I want SQL to return an answer for?

or do I need to replace ?x ?y ?z ?a with actual item code numbers in the SQL panel..

please clarify

cheers

SimonCHere · 23 Nov 2008 at 19:38

The ? are placeholders in SQL. It will prompt you for values to go in that field.

So when you run that code you should get 4 prompts x, y, z, a asking for items codes.

Not sure what happens with null param values but try it and see.

Simon