CODEWITHSUNDEEP
sqljasper-reports
user2315810
user2315810

Reputation: 1

SQL, return a row multiple times from single table based on variable

I'm creating some box labels using iReport and need to multiply out the data for each box label.

I have 2 parameters, @id for the record id and @typ for the package type required

SELECT
     dr_id,
     dr_to_customer,
     dr_company_name,
     dr_address_0,
     dr_address_1,
     dr_address_2,
     dr_address_3,
     dr_postcode,
     dr_contact,
     dr_per_packs,
     dr_per_boxes,
     dr_per_pallets
FROM delreq
WHERE dr_id = @id AND ??timesTOrepeat?? = @typ (a string)

I need to return each row x times based on the qty's in either packs, boxes or pallets.

So, I need to first select the qty based on @typ then work out how to multiply out the rows.

Help.

Upvotes: 0

Views: 313

Answers (1)

Lokesh
Lokesh

Reputation: 7940

You can try to achieve this using outer join. Try this:

SELECT
     dr_id,
     dr_to_customer,
     dr_company_name,
     dr_address_0,
     dr_address_1,
     dr_address_2,
     dr_address_3,
     dr_postcode,
     dr_contact,
     dr_per_packs,
     dr_per_boxes,
     dr_per_pallets
FROM delreq a, (select -1 id from all_objects where rownum < @typ) b
WHERE dr_id = @id AND a.dr_id(+) = b.id;

I am assuming here that dr_id will not have value -1 also assuming db is oracle [not sure abut other DB.

Upvotes: 1

Related Questions